Create On Hand Report by Item Group

Finance and Operations/AX Developer Forum
1

Hi guys, im currently creating an On Hand Report Filter by ItemGroupId and seggregated by Warehouse

this should be the layout of the report

On Hand Inventory FROMDATE - TODATE

WAREHOUSE1

ITEMID ITEMNAME OPENING QTY OPENING AMOUNT CLOSE QTY CLOSE AMOUNT

Item1

Item3

SUBTOTAL

WAREHOUSE2

ITEMID ITEMNAME OPENING QTY OPENING AMOUNT CLOSE QTY CLOSE AMOUNT

Item2

Item4

SUBTOTAL

GRANDTOTAL

--------->

Here is my simple code, however it doesnt group the items per warehouse…any wrong code or misplaced or missing code…please help me… or you can suggest any other way on how i can accomplish this one…thanks in advance

public boolean fetch()

{

;

while select * from inventtable

where inventtable.ItemGroupId == itemgrid || itemgrid == “”

{

itemnum = inventtable.ItemId;

itemname = inventtable.ItemName;

while select * from inventtrans join inventdim

where inventtrans.ItemId == inventtable.ItemId

&& inventtrans.DateFinancial <= tdate

&& inventdim.inventDimId == inventtrans.inventDimId

&& inventtrans.StatusReceipt != StatusReceipt::Ordered

{

endbal += inventtrans.costValue();

qty += inventtrans.Qty;

costprice = inventsum.averageCostPrice();

if(inventtrans.DateFinancial <= fdate

&& inventtrans.StatusReceipt != StatusReceipt::Ordered)

{

begbal += inventtrans.costValue();

bqty +=inventtrans.Qty;

}

if( ilocid != inventdim.InventLocationId)

{

info(strfmt("%1-%2-%3",inventdim.inventDimId,inventtrans.inventDimId,inventdim.InventLocationId));

ilocid = inventdim.InventLocationId;

element.execute(3);//Warehouse

}

}

element.execute(1);//lines

begbal =0;

endbal =0;

qty =0;

costprice =0;

bqty = 0;

}

While Select count(ItemId) from InventTable

where inventtable.ItemGroupId == itemgrid

{

flag = strfmt("%1",inventtable.ItemId);

}

return true;

}

2

Hi,

oh, those lovely reports that fetch data directly from InventTrans… :slight_smile: I hope that inventory transaction amount for the live system won’t be counted in millions and millions. I haven’t got that much into details about the actual report you are trying to build, but could the InventSum table be of any use for the purpose?

Ok, but regarding your code… well, as you want to segregate data between (group on) warehouses than you definitely have to do some kind of sorting on the warehouse dimension for transactions. At the moment you are just selecting all items in some default order, than for each item you are going through the item’s transactions in some default order and that’s about it. So let’s say you have an item “A” and you go through its transactions. First you find a transaction with a warehouse “D” and you print a warehouse header, then the next transaction comes with a warehouse “C”, you print a header, but then comes yet another transaction with a warehouse “D” and you print another header for it…? Sorting in select statements is what you need. OR, you could cumulate and group all data in a temporary table (if a record for that item/warehouse is not in a temporary table yet, you create it, if it already is, then you just add the amount). When you have accumulated all transactions, you just print the contents of temporary table to a report.