You’re offline. This is a read only version of the page.
1,599
We have a group that is trying to make a custom version of the AP Historical Aged Trial Balance. They are using GP 18.3, and trying to decipher the logic used in building the GP version of the report. So far the logic has been elusive in that standard report design uses a series of temporary tables to compile, manipulate and present data. That said has anyone out there been able to derive the necessary queries or logic to compile the historical AP aging? Please advise. Look forward to a response.
JG
Hi JG,
Indeed it's not as easy as it looks like.. particularly due to the fact that it uses stored procs in SQL.. However, if you have deployed the SSRS reports for GP, then the PM Historical ATB report is available in there and you could customize it's look & feel, probably easier to do than trying to customize the built-in GP report.
As alternative, you could try to use Excel reports (refreshable) to pull the data you're looking for. Below are 2 examples on how it could be done:
https://www.encorebusiness.com/blog/dynamics-gp-historical-trial-balance-reports-excel/
Ultimately, if you don't want to re-invent the wheel, someone did create a custom GP reports for AP & AR to get the data formatted in a way that it is easier to export to Excel
Hello JG and Thank you for your Post!
You are correct that the GP Version of the report utilizes temporary tables to present the data in the Report Writer version of the report. I'm not sure if I can actually explain how the information is presented in the report within a forum post, but the Report Writer version of the report relies heavily on the pmHistoricalAgedTrialBalance stored procedure, along with the other pmHATB stored procedures in the database, such as pmHATBAdjustForRevaluation, pmHATBCalculatePaymentTotals, pmHATBCalculateTotals, and pmHATBLoadDexTempTables. The report then also uses Dexterity code to present the data in the report after pulling the data into the temporary tables that it utilizes during processing.
You could look into how each of those are called and potentially leverage that information for use with your report.
Have you potentially considered modifying the original report instead of trying to create a report from scratch? Is there a particular business need for a fully custom report?
Regards,
On the RM side, there is a stored procedure that is called "seermHATBSRSWrapper." On the PM side, just looking through the stored procedures, there is one called seePMHATBWrapper. Maybe try that as the data source and see what it returns and compare it to the built-in PM HATB report?
Joe
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (