axapta2009: sum inventtrans on specific date, filter by dimension

I need to calculate sum of InvnetTrans'es that have specific physicial 开发者_JAVA技巧dimensions[inventTransPosting.Dimension] on specific date. I.e. I need analogue of

select sum(CostAmountPosted) //other sums
     from InventTrans 
     where  InventTrans.transDate  < 3\3\2010
exists join InventTransPosted 
    where InventTransPosted.dimension[1] == 'XXX'
       && InventTransPosted.inventTransId == inventTrans.inventTransId
       && //other relations

It is possible to calculate InvenTrans sum with InventSum* classes filtering InvenTranses by InventDim. Is it possible somehow to filter by non inventdim dimensions? Or it is neccessary to change InventSum* classes? Right now I don't see that inventSum* classes can filter by Dimensions. Maybe I missed some classes?

Restructure your select:

select sum(CostAmountPosted) //other sums
     from InventTrans 
exists join InventTransPosting
    where InventTransPosting.dimension[1] == 'XXX'
       && InventTransPosting.transDate  < 3\3\2010
       && InventTransPosting.inventTransId == inventTrans.inventTransId
       && InventTransPosting.itemId == inventTrans.itemId
       && //other relations

Make relevant index on InventTransPosting with Dimension[1] as first index field and TransDate as second.

Take a look on the InventOnHand class.

It sums the invent on-hand values based on criteria like item id and inventory dimensions (but not financial dimensions).

There are several constructor methods to InventOnhand name new like newInventBatch.

You could store - redundantly - amountPosted on InventTransPosted table, then make a relevant index.

The filling of the amountPosted on existing transaction may be problematic.

Is it worth it? Or could you use a BI tool to do your report?