Inventory Value Report Query

(0) Share

Report

Posted on by HAIRUL HAZRI

1,214

Hi. I have configure an Inventory Value Report with the following settings:

What I'm trying to do is to replicate the query results returned by this Inventory Value Report settings.

I was referring to buildTransactionsWithDynamicQuery() method in InventValueReportPopulateItem class and tried to trace then write the correct query for this settings. Here's my query:

select 
INVENTTABLE.ITEMID, INVENTDIM.INVENTBATCHID,INVENTVALUEREPORTVIEW.TRANSDATE,
INVENTVALUEREPORTVIEW.VOUCHER,INVENTVALUEREPORTVIEW.REFERENCE,INVENTVALUEREPORTVIEW.REFERENCECATEGORY,
INVENTVALUEREPORTVIEW.INVENTTRANSPOSTINGTYPE,INVENTVALUEREPORTVIEW.ISPOSTED,SUM(QTY) as Qty,SUM(AMOUNT) as Amount
from INVENTTABLE
join InventItemGroupItem
on INVENTTABLE.ITEMID = InventItemGroupItem.ITEMID
and INVENTTABLE.DATAAREAID = InventItemGroupItem.ITEMDATAAREAID
left outer join EcoResTrackingDimensionGroupItem 
on INVENTTABLE.ITEMID = EcoResTrackingDimensionGroupItem.ITEMID
and INVENTTABLE.DATAAREAID = EcoResTrackingDimensionGroupItem.ITEMDATAAREAID
join INVENTVALUEREPORTVIEW on INVENTTABLE.ITEMID = INVENTVALUEREPORTVIEW.ITEMID
join INVENTDIM on INVENTVALUEREPORTVIEW.INVENTDIMID = INVENTDIM.INVENTDIMID
where INVENTTABLE.ITEMID = 'YPWAR0001'
and INVENTVALUEREPORTVIEW.TRANSDATE between '2017-08-01 00:00:00.000' and '2017-08-31 00:00:00.000'
and INVENTVALUEREPORTVIEW.ISPOSTED = 1
group by INVENTTABLE.ITEMTYPE,INVENTTABLE.ITEMID,
EcoResTrackingDimensionGroupItem.TRACKINGDIMENSIONGROUP,
INVENTDIM.INVENTBATCHID,INVENTVALUEREPORTVIEW.TRANSDATE,
INVENTVALUEREPORTVIEW.VOUCHER,INVENTVALUEREPORTVIEW.REFERENCE,INVENTVALUEREPORTVIEW.REFERENCECATEGORY,
INVENTVALUEREPORTVIEW.INVENTTRANSPOSTINGTYPE,INVENTVALUEREPORTVIEW.ISPOSTED

It seems that I almost got it right but somehow there's extra record(s) which I'm not sure how to filter them out or (maybe) which group by field to add.

I am wondering if I'm doing this correctly? I do it like this because I need this query results to be used in a new report.

Thank You.

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

Hi Hairul,

What exactly are you trying to achieve and what is your exact question here? What is the business requirement?

Was this reply helpful? Yes No
HAIRUL HAZRI 1,214 on at

Like (0)

Report

Hi Andre,

the requirement is to replicate the query which returns the same results as the above Inventory Value Report settings.

Then I need to use that query in a new report then apply some additional conditions and of course in a whole new report design.

For example in the Inventory Value Report, printing the Inventory Value for item "YPWAR0001" from 1/8/2017 to 31/8/2017 will return 14 records.

I'm trying to build a query that returns these exact 14 records based on the Inventory Value Report settings.

I'm referring to buildTransactionsWithDynamicQuery() method in InventValueReportPopulateItem class to build the query but I can't get it right.

Since the Inventory Value Report is super complicated, feels like my approach here is a waste of time. It's taking me weeks already and I got almost nowhere.

So in your opinion, am I even doing this correctly? What would you do to fulfill this requirement?

Thank You.

Was this reply helpful? Yes No
Verified answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Hi,

I feel its better to extract the query used during report processing and make a comparison with it. You can use the below info statement in the method buildTransactionsWithDynamicQuery of class InventValueReportPopulateItem to get the query string. Hope this helps you.

info(strFmt("%1", qr.toString())); // Info message while (qr.next()) { }

Was this reply helpful? Yes No
HAIRUL HAZRI 1,214 on at

Like (0)

Report
Hi Chaitanya,

that's a great idea.

So I tried running the report with these parameters:

And here's what the info statement returned:

SELECT * FROM InventTable(InventTable_1) GROUP BY InventTable.ItemType, InventTable.ItemId, InventItemGroupItem.ItemGroupId, EcoResTrackingDimensionGroupItem.TrackingDimensionGroup, InventDim.inventBatchId, InventValueReportView.TransDate, InventValueReportView.Voucher, InventValueReportView.Reference, InventValueReportView.TransBeginTime, InventValueReportView.ReferenceCategory, InventValueReportView.InventTransPostingType, InventValueReportView.IsPosted WHERE ((ItemId = N'YPWAR0001')) JOIN * FROM InventItemGroupItem(InventItemGroupItem_1) ON InventTable.ItemId = InventItemGroupItem.ItemId AND InventTable.dataAreaId = InventItemGroupItem.ItemDataAreaId OUTER JOIN * FROM EcoResTrackingDimensionGroupItem(EcoResTrackingDimensionGroupItem_1) ON InventTable.ItemId = EcoResTrackingDimensionGroupItem.ItemId AND InventTable.dataAreaId = EcoResTrackingDimensionGroupItem.ItemDataAreaId JOIN SUM(Qty), SUM(Amount) FROM InventValueReportView(InventValueReportView_1) ON InventTable.ItemId = InventValueReportView.ItemId AND ((TransDate>={ts '2017-08-01 00:00:00.000'} AND TransDate<={ts '2154-12-31 00:00:00.000'})) JOIN * FROM InventDim(InventDim_1) ON InventValueReportView.InventDimId = InventDim.inventDimId

I have some questions.

First, why is it the To Date is '2154-12-31 00:00:00.000' instead of '2017-08-31 00:00:00.000' which was entered in the parameter? What happened here?

Then I translated this query into SQL server query to test it out :

SELECT InventTable.ItemType, InventTable.ItemId, InventItemGroupItem.ItemGroupId, EcoResTrackingDimensionGroupItem.TrackingDimensionGroup, InventDim.inventBatchId, InventValueReportView.TransDate, InventValueReportView.Voucher, InventValueReportView.Reference, InventValueReportView.TransBeginTime, InventValueReportView.ReferenceCategory, InventValueReportView.InventTransPostingType, InventValueReportView.IsPosted, SUM(Qty), SUM(Amount) FROM InventTable JOIN InventItemGroupItem ON InventTable.ItemId = InventItemGroupItem.ItemId AND InventTable.dataAreaId = InventItemGroupItem.ItemDataAreaId AND Inventtable.ItemId = N'YPWAR0001' LEFT OUTER JOIN EcoResTrackingDimensionGroupItem ON InventTable.ItemId = EcoResTrackingDimensionGroupItem.ItemId AND InventTable.dataAreaId = EcoResTrackingDimensionGroupItem.ItemDataAreaId JOIN InventValueReportView ON InventTable.ItemId = InventValueReportView.ItemId AND ((TransDate>={ts '2017-08-01 00:00:00.000'} AND TransDate<={ts '2017-08-31 00:00:00.000'})) AND ISPOSTED = 1 JOIN InventDim ON InventValueReportView.InventDimId = InventDim.inventDimId GROUP BY InventTable.ItemType, InventTable.ItemId, InventItemGroupItem.ItemGroupId, EcoResTrackingDimensionGroupItem.TrackingDimensionGroup, InventDim.inventBatchId, InventValueReportView.TransDate, InventValueReportView.Voucher, InventValueReportView.Reference, InventValueReportView.TransBeginTime, InventValueReportView.ReferenceCategory, InventValueReportView.InventTransPostingType, InventValueReportView.IsPosted

But it's not returning the same number of records. Sorry for asking it like this, but do you happen to know that could be missing here? I'm pretty sure I had copied everything.

Thank You.

Was this reply helpful? Yes No