Dynamics GP AP Historical Trial Balance Recreation in Crystal Report

From time to time you can hear Microsoft Dynamics GP users complains about Report Writer reports inflexibility, especially when you are trying to export something like GL Trial Balance or AP Historical Trial Balance report. Report Writer is Great Plains Dexterity coded module and its text file inflexibility is understandable (as Dex is a shell, in turn programmed in C language). Well, if you are not happy with ReportWriter output, consider creating Trial Balance Report in Crystal Reports Designer (or even in Microsoft SQL Server Reporting Services or SSRS). we would like to share AP Historical Aged Trial Balance Report creation steps in Crystal Reports:

1. Crystal Report base. As AP Historical Aged Trial Balance report is very complex, and the only way (probably you may try to challenge this statement, but when you try it in Crystal Report data connection wizard, we may decide to drop your objections) to create it is to make report base SQL Stored Procedure, where you will be able to create temporary tables and final select statement (as Crystal Report data set) at the end of the procedure. Crystal Report is easily exportable to MS Excel or other formats (for possible EDI processing)

2. Tables for AP Historical Trial Balance Report. Please, consider PM00200 (Vendor Master file for vendor name, and additional info), PM20000 (PM Open Transactions - AP Vendor Invoices, Payments, Credit memos typically go here, and they stay there until they are applied to each other and automatically moved to historical tables), PM30200 (when AP Invoices/vouchers are fully applied by Payments or Credit memos - they are moved to this table automatically together with Payments to Vendors), PM30300 (this table has all the matches of Vendor Invoices and Debit Memos and Payments and Credit Memos to vendors, applied to these payments - both in Open and historical Status)

3. Additional Hints. If you are recreating current AP Aged Trial Balance (aged on the current date), you are probably good to begin your SQL Stored Procedure coding and Crystal Report design. If your goal is Historical Aged AP Trial Balance - you are not done yet. Here you have to recreate the history on the specific date. Recommended way to achieve this goal is the following:

select APFRDCNM, DOCTYPE, sum(APPLDAMT) as AmountApplied from PM30300
where ApplyToGLPostDate <=@agingDate and APTODCDT<=@agingDate
group by APFRDCNM, DOCTYPE

This first statement should allow us to filter out Historical Payments, not yet applied to AP Vendor invoices on the Aging Date

Second Statement is this:

select APTODCNM, APTODCTY, sum(APPLDAMT) as AmountApplied from PM30300
where DOCDATE <=@agingDate and APTODCDT<=@agingDate
group by APTODCNM, APTODCTY

And that one should help us to deal with Not yet fully applied Vendor Invoices

4. Crystal Versus Microsoft SQL Server Reporting Services. In our opinion both tools should do the job. SSRS is more geared toward web interface, while Crystal Reports is more advanced as traditional Corporate ERP reporting tool. Both could be based on Microsoft SQL Server View or Stored Procedure. CR or SSRS have even long term advantage as here you could combine heterogeneous Corporate ERP platforms, such as Dynamics GP, SAP Business One, Oracle EBusiness Suite, Microsoft CRM Orders and Quotes. Additional paragraphs are sort of fresh air in sharing Corporate ERP opinions

5. How your custom Crystal AP Historical Aged Trial Balance report should be validated? Here the answer is simple - print the same report from Dynamics GP workstation and compare

6. We based our AP Historical Trial Based Crystal Report on original Microsoft Dynamics GP SQL stored procedures with some improvements. We are Microsoft Dynamics GP Consulting firm and we are happy to implement AP or GL Historical Trial Balance for your in Crystal Reports or in SSRS

7. If you or your multinational company branch or subsidiary is located internationally in Brazil, China, Russia, Japan, Korea, Continental Europe, where Microsoft Dynamics GP version 11.0 and 10.0 is not localized, we recommend you to consider either Microsoft Dynamics AX Axapta (if your Corporate ERP budget is flexible), otherwise please consider SAP B1 as localized Corporate ERP, MRP and Accounting application for your country. In Russia you also have strong competition from Ax and 1S Bukhgalteria - if you need consolidated balance for multinational corporation where Russian subsidiary is run under 1S Bukhgalteria - feel free to call us for consolidated reporting options (FRx)

8. Please, feel free to call us: 1-866-528-0577, [email protected]


Andrew Karasev, Alba Spectrum http://www.albaspectrum.com [email protected] 1-866-528-0577 - Microsoft Business Solutions Great Plains MS CRM Partner, Info Portal http://www.pegasplanet.com - serving corporate customers in the following industries: Aerospace & Defense, Medical & Healthcare, Distribution & Logistics, Hospitality, Banking & Finance, Wholesale & Retail, Chemicals, Oil & Gas, Placement & Recruiting, Advertising & Publishing, Textile, Pharmaceutical, Not-Profit, Beverages, Conglomerates, Apparels, Durables, Manufacturing and having locations in multiple states and internationally. We are serving USA Nationwide: CA, IL, NY, FL, AZ, CO, TX, WI, WA, MI, MA, MO, LA, NM, MN