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/
https://victoriayudin.com/2015/08/13/refreshable-excel-report-for-historical-gl-trial-balance-in-dynamics-gp/
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
https://reese-consulting.com/export-the-dynamics-gp-receivables-historical-aged-trial-balance-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
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Mansi Soni as our August 2025 Community…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Sohaib Cheema 665 User Group Leader
Martin Dráb 595 Most Valuable Professional
Yng Lih 558
