Hi, I have this particular query: parent ds= InventSum, child ds=InventTable. Ordermode=Group By on both (since I need the sums in InventSum). Group by fields : ItemId in IS, ItemGroupId and ItemId in IT. When I run the report, the Axapta’s kernell forwards this interpreted query to SQL Server: SELECT SUM(A.POSTEDQTY),A.ITEMID,B.ITEMGROUPID,B.ITEMID FROM INVENTSUM A,INVENTTABLE B WHERE (A.DATAAREAID=‘dat’) AND ((B.DATAAREAID=‘dat’) AND (A.ITEMID=B.ITEMID)) GROUP BY A.ITEMID,B.ITEMGROUPID,B.ITEMID ORDER BY A.ITEMID,B.ITEMGROUPID,B.ITEMID OPTION(FAST 11) grop However, I want that the data gouping to be firstly by ItemGroupId in inventTable, and after that by ItemId in inventSum. I understand that the kernel is always placing the group by flds from the child DS ALWAYS after the grouping flds in parent. Is there a way to change this implicit order? Thank you, Ciprian
Hi Ciprian, I am not sure if you can implicitly change the order. Here is explicit solution [:)] InventSum is; InventTable it; WHILE SELECT ItemId, ItemGroupId FROM it GROUP BY ItemGroupId, ItemId JOIN SUM(PostedQty) FROM is WHERE it.ItemId == is.ItemId info(it.ItemId + ' ' + int2str(is.PostedQty)); Hope this help [:)]
Thank you Bojan. It’s nice and practical, but I can’t use that right now. I am forced to use a report query object with a predefined structure, so that the ranges, sortings, and other custom settings to be choosed by the user from the SysQueryForm at run-time. Shortly, the feature is about adding a ItemGroupId grouping field to the Inventory Management\Reports\Status\Physical Inventory\Physical inventory by inventory dimension. Regards, Ciprian
Hi Ciprian. Do you have to use existing report, or you can create your own?
It does not matter. The idea is to preserve the current functionality while adding a new group by option, as the primary grouping field (ItemGroupId), and of course, allow the user to set any option to this: Sub-headings and Group totals checks. Thanks, Ciprian
Hi, Ciprian. It really can’t be done. There is a solution I used, but it’s not pretty and breaks the rules of DBs. But here it goes: You can add the ItemGroupId field into the InventSum table. And fill it in at appropriate times. Than in you query you can simply add the group by on this field first. (I tried adding the ItemName into the InventOnHandItem report)