Relating GL Data to Project Accounting

In any discussion regarding reporting and project accounting, we always end up talking about the GL.  And the inevitable question comes up about reporting the GL by project.  Which, of course, there aren't a lot of standard options...

Within Dynamics GP, there is a standard Project Detailed Trial Balance report (Reports-Project-Detailed Trial Balance).  The report relies on temporary tables to correlate project information with the general ledger debit/credit detail.  So I sometimes hit roadblocks when modifying the report, and there is also the issue of distributing the report if you want to send it to non-GP users.

But there is also a Project Detailed Trial Balance report in SQL Reporting Services, it is a standard report with Microsoft Dynamics GP.  It works great, but relies on a stored procedure (which is very very very long and involved) to return the data.  So again, modifying the report can be a bit tricky.

So, in looking for a way to relate the information in a simpler way when looking for basic debit/expense side detail, I created the following SQL statement which can serve as a basis for a view.  This would allow you to use the existing Account Transactions view, and create a relationship based on...

• Originating Document Number = DocNumber
• Originating Transaction Source = AuditTrail
• Account Index = PACOGS_Idx

Now, keep in mind, this is a very basic approach when using Project Accounting in a basic way.  If you are using Cost Plus/Fixed Price projects, or have more involved accounting methods or posting, you may find that it needs further tweaking.

Also, this version only compiles project information for purchases and miscellaneous logs...but you could follow the same syntax to add the timesheet, equipment log, and employee expense transaction types as well.
select POP30300.POPRCTNM DocNumber, POP30300.TRXSORCE AuditTrail,
PA31102.PAPROJNUMBER ProjectNumber, PA31102.PACogs_Idx COGSAccount, PA31102.PAContra_Account_IDX ContraAccount,
PA31102.PACOSTCATID CostCategory, PA31102.ITEMNMBR ItemNumber
from POP30300 inner join PA31102 on POP30300.POPRCTNM=PA31102.PAVIDN
union
select PA30300.PAMISCLDOCNO DocNumber, PA30300.TRXSORCE AuditTrail,
PA30301.PAPROJNUMBER ProjectNumber, PA30301.PACogs_Idx COGSAccount, PA30301.PAContra_Account_IDX ContraAccount,
PA30301.PACOSTCATID CostCategory, PA30300.PSMISCID ItemNumber
from PA30300 inner join PA30301 on PA30300.PAMISCLDOCNO=PA30301.PAMISCLDOCNO

Of course, let me know if you find any issues with it.  I just was trying to distill down to a more basic approach.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.