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
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.
12 comments:
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.
Definitely, Russ! Feel free to contact me at cphillips@bkd.com :)
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!!
I would also like to see your solution please contact me
Also interested!
I am also trying to develop some Project/GL reports and would like to see anything you all might have.
dmorinello@proenergyservices.com
Thanks!
Hi Hendrik! You can feel free to email me at cphillips@bkd.com.
Russ - I'd love to speak to you about the report(s) you've built! dprigmore@advancedintegrators.com
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
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
Just wanted to leave an update for anyone might have missed the new features! Thanks to all of the votes and the hard work of the MS GP PA team, PA and GL are now easily reconciled! Check out this post for more information on the release and new features: https://community.dynamics.com/gp/b/dynamicsgp/archive/2017/01/18/microsoft-dynamics-gp-2016-r2-project-accounting-trial-balance-report-distributions-added-for-each-transaction-to-reflect-on-report-line-distributions
Can anyone advise adding the equipment and payroll as mentioned?
Post a Comment