Solved

Dated per batch project costs from Dynamics AX 2009 using SSRS

Posted on 2013-06-17
2
567 Views
Last Modified: 2013-06-20
Hi Experts,

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,

Mark
0
Comment
Question by:SeeHearMark
  • 2
2 Comments
 

Accepted Solution

by:
SeeHearMark earned 0 total points
ID: 39261055
OK - I think I have a solution, based on empirical evidence rather than hard facts, so it might only work due to how we are using AX!

Essentially, I use aggregated PROJTRANSPOSTING over the LEDGERTRANSDATE / INVENTTRANSID / PROJADJUSTREF where 'an original transaction', which seems to be LEDGERPOSTINGTYPE <= 50 or =52(???), outer joined to INVENTTRANS, where I use COSTAMOUNTPOSTED for each batch, referenced via INVENTDIM. This assumes that all the rows on PROJTRANSPOSTING for an INVENTTRANSID / PROJADJUSTREF pair will have the same LEDGERTRANSDATE, which they seem to do, and I'm not sure about the LEDGERTRANSTYPE split.

Then, I union this with something very similar for the other range of LEDGERTRANSTYPE (=51 or >= 53), outer joined to INVENTSETTLEMENT on VOUCHERID / INVENTTRANSID to pick up COSTAMOUNTADJUSTMENT, getting the batch again from INVENTTRANS > INVENTDIM using the TRANSRECID = RECID relation.

This seems to work for me, until someone does something in AX they haven't done so far, and breaks it. So the question is now, can anyone tell me what might break it - where any holes are in my assumptions - so I can be preapred?

Cheers,

Mark
0
 

Author Closing Comment

by:SeeHearMark
ID: 39264009
Looks like not, but I'll leave my solution on here in case it's useful to others, rather than deleting the question.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now