When we implemented Dynamics GP, the accounting team selected the Payables Historical Aged Trial Balance (HATB) report as one to run at month end. They really like being able to run the report as of the last day of the prior month after all the payments have been recorded. 10 years of data later and the report is taking forever to run. When I tested recently, it took 9 minutes. If I run the "Steve Gray" version of PM HATB, it completes in 6 seconds. I looked into the stored procedures behind the report, pmHistoricalAgedTrialBalance and pmPrintHATBGetDocuments, and they use multiple nested loops and dynamic SQL, techniques known to be harmful to SQL performance. Please refactor how the report is calculated to use SQL coding best practices so that large data sets no longer decimate the performance..
Comments
Agreed - this would be a huge enhancement.
Category: Financials - Accounts Payable
Jodi Christiansen (administrator) on 5/20/2024 6:45:30 PM
Thank you for submitting this suggestion and for the votes it has received.
After thorough review, we regret to inform you that this suggestion has been declined for a future release of Dynamics GP.
The slowness issue typically arises due to the filters and restrictions set on the report.
However, for most customers, the report will run in a reasonable amount of time and provide accurate results.
While we won’t be addressing this specific feature in a future release, you might consider exploring Steve’s SQL option as an alternative.
Reports often perform better when directly querying the SQL database rather than going through the front-end UI.
Given the lifecycle and current state of Dynamics GP, this feature will not be implemented at this time.
Performance of PM Historical Aged Trial Balance (HATB) (dynamics.com)
Thanks
Terry Heley
Microsoft