Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Logic for PM Historica...
Finance | Project Operations, Human Resources, ...
Suggested answer

Logic for PM Historical Aged Trial Balance Report

(0) ShareShare
ReportReport
Posted on by JGarcia123 466

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

Categories:
Microsoft Dynamics GP
  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at
    RE: Logic for PM Historical Aged Trial Balance Report

    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/

  • Lucas Reuss Profile Picture
    Lucas Reuss on at
    RE: Logic for PM Historical Aged Trial Balance Report

    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,

  • Joseph Markovich Profile Picture
    Joseph Markovich 3,914 on at
    RE: Logic for PM Historical Aged Trial Balance Report

    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.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,575 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,493 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans