Hi Team,
I have requirement where customer wants us to customize standard Variance Analysis statement report. In this report, user will select the from date, to date, Sorting and grouping as "Item", Column headings as "cost group type", Detail as "NO" and Level as "Total".
As of now when these parameters are selected and report is ran. It gives a cumulative line which shows sum of variance, qty. What we want is, it should run only for those items whose default order type is "Purchase Order". Also, instead f showing cumulative line, it should split the lines purchase order wise.
For example: lets say below is the data for an item in standard cost transaction form:
When the report is ran it shows below report:
So, it summing up the lines s Qty and Variances.
I debugged and found that this is the query which is generated in the report' class.
{Query object ea93d530: SELECT RecId, ItemId, dataAreaId, Product FROM InventTable(InventTable_1) WHERE ((ItemId = N'W725-10000735')) JOIN * FROM InventCostTransView(InventCostTransView_1) ON InventTable.ItemId = InventCostTransView.ItemId AND ((TransDate<={ts '2154-12-31 00:00:00.000'})) JOIN * FROM InventCostTransVariance(InventCostTransVariance_1) ORDER BY InventCostTransVariance.InventCostTransRefRecId ASC, InventCostTransVariance.VarianceType ASC ON InventCostTransView.InventCostTransRecId = InventCostTransVariance.InventCostTransRefRecId AND ((Level = 0)) OUTER JOIN * FROM BOMCostGroup(BOMCostGroup_1) ON InventCostTransVariance.CostGroupId = BOMCostGroup.CostGroupId}
I checked and found that this query gives 5 records shown in the standard cost transaction form above. However, I failed to understand at what point is it summing up the variance and qty and then showing cumulative line in report.
What I want is to split the lines as shown in standard cost transaction form and show them in report purchase order wise so user can see variance amount as purchase order wise only.
Please guide.
Thanks
