Dated per batch project costs from Dynamics AX 2009 using SSRS
Posted on 2013-06-17
I have written an SSRS report against an AX 2009 database to provide project sales revenue (accrued or invoiced) and costs, per project, per month, to keep an eye on project margins. The data comes primarily from PROJTRANSPOSTING, which suits well. It is important to note that adjustments to costs belong in the month of the adjustment, not the month of the item transaction being adjusted.
I have now been asked to provide a version of this report that drops down to batch level, where revenue can be allocated pro-rata based on batch quantity, but costs need to be those attributed specifically to the batch. The problem I'm finding is that, while PROJTRANSPOSTING has the costs and adjustments date-stamped, INVENTTRANS has (via INVENTDIM) the relevant batch / qty information but a consolidated set of costs over time for the related INVENTTRANSID / PROJADJUSTREFID.
I thought INVENTSETTLEMENT might be the solution, but have found discrepancies between dates on PROJTRANSPOSTING and INVENTSETTLEMENT, and no direct or indirect, specific relation that isn't many-to-many.
Can anyone point me in the direction of some nice little table that has just what I need, or is able to enlighten me on the appropriate relationship?
Thanks for reading,