Wednesday, May 1, 2013

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.

10 comments:

Russ Worth said...

Christina we have created an SSRS report that starts with GL and gives the breakdown amounts by project. If you would like to see the report I can send a sample. I have spent several months developing this report and have an interest in reselling it though the VAR channel. We also have an offshoot of this report that gives contract summary info matched to GL and bboken down by Labor, TP, Misc Log, EE ect.

Christina Phillips said...

Definitely, Russ! Feel free to contact me at cphillips@bkd.com :)

Malinda said...

Christina &/or Russ: I would love to speak to either/both of you regarding these reports. We have an URGENT need for what you have described here. If you are willing to contact me, my email address is malinda.lpc.mail@gmail.com. Thanks so much!!

Hendrik Lombard said...

I would also like to see your solution please contact me

Hendrik Lombard said...

Also interested!

DavidMO said...

I am also trying to develop some Project/GL reports and would like to see anything you all might have.

dmorinello@proenergyservices.com

Thanks!

Christina Phillips said...

Hi Hendrik! You can feel free to email me at cphillips@bkd.com.

Dawn Prigmore said...

Russ - I'd love to speak to you about the report(s) you've built! dprigmore@advancedintegrators.com

Dawn Prigmore said...

Christina et al: Would you take a minute to look at the following product suggestion? I'd LOVE to make reporting these details easier! https://connect.microsoft.com/dynamicssuggestions/feedback/details/1262276

Ohann Aucamp said...

Hi there

I realise this is an old thread but if someone could put me in touch with Russ or let me know where is report can be obtained that would be great!

Thanks
JP