Export the Dynamics GP Payables or Receivables Historical Aged Trial Balance to Excel

This is a modified Dynamics GP report (see download) I’ve provided to my clients for years that can be exported to a text file and imported into excel that includes the customer ID, name, and the aging columns. Through GP security, you can only have one modified report for each user and company if you’re running a shared modified reports dictionary for each workstation. What this means is if you already have an existing modified historical aged trial balance detail report you may have to provide a separate login or shortcut to this report to have access to both. Here’s a screenshot of the report in excel.

You have to add totals and grand total line, be sure to exclude the “Original Amount” column.

Payables:

Download report

 

Receivables:

Download report

 

After you download the package(s) from above by right-clicking and choose ‘save as’, you can import it in version 10, 2010, 2013 by going to Microsoft Dynamics GP>>tools>>customize>>customization maintenance and choose import. Before doing so, be sure to back up your modified reports to ensure nothing is overwritten.

If you need the summary trial balance export packages just create a comment below and I will email them to you.

This entry was posted in Dynamics GP. Bookmark the permalink.

98 Responses to Export the Dynamics GP Payables or Receivables Historical Aged Trial Balance to Excel

  1. Eric says:

    Hi Steve,

    Thank you so much for putting this together and sharing your work with all of us! I know I can speak for everyone here, this has made life so much easier when dealing with these reports.

    Can you please send me the package for the customized payables detail report?

    Thanks again Steve!

  2. Mel says:

    Hi Steve,

    Can you please let me have the PM Summary and detail reports?

  3. Phil Justice says:

    Hello Steve,

    Could you please send me the Detail and Summary AR Historical Aged TB’s. I am having a little difficulty trying to download the detail from the site.

    Thank You

  4. Keith says:

    Can you please send a copy of the payables report and the summary report. This will save me the time to do the same all accountants want to send everything to Excel.

    Thank you

  5. Robert says:

    Can you please provide me with the AP trail balance and Summary please?

    Thanks

  6. John says:

    Hi Steve,
    I know you posted this a few years ago but that AP HATB package file would save me some time.

    Great work, thank you!

  7. Lisa Sorensen says:

    Steve,

    Can I get a copy of the Payables and Summary TB export package as well. This is great to have. Thank you for being generous and sharing it.

  8. Steve says:

    Unfortunately I don’t have the multicurrency version.

  9. Alicia says:

    Hi Steve,

    Can I get a copy of the Historical AP TB Detail and Summary export package as well.

    Thanks sharing!!

  10. Albertha says:

    We’ve customized the MC Payables Historical Aged TB (Detailed). For some reason, after the customization, I’m unable to save to file as text, Comma- or Tab-delimited. Any ideas?

  11. Steve says:

    Try making sure all the fields in each column are the same width, or try going back to the original to see if you have an environment issue, I’m not sure otherwise.

  12. Julio says:

    Hi, I can’t download the RMHistoricalAgedTrialBalanceDetail.package Can you help me please.

  13. Lindsay says:

    Hi there,

    can you please send me the package for AR and AP?

    thank you,
    Lindsay

  14. Clark says:

    Hi Steve,

    Can you send me the packages for the summary of both AR and AP?

    Thank you,
    Clark

  15. Brenda says:

    Can I please get the package file for the AR and AP reports, it will not download
    thanks
    Brenda

  16. Travis says:

    Hello – Could you please send me the packages for summary trial balance? Thanks in advance. Good work!

  17. Carly Dartez says:

    Can you send me the summary trial balance export packages

    Many thanks!

  18. Mark Wiley says:

    Thanks Steve. I’d like the summary versions, if possible. Thanks for sharing.

  19. Nikki Toole-Hebert says:

    This looks great could you send me the AR and AP detail Trial Balance Reports.
    Thank you for this.

  20. Kerri Reinholtz says:

    This is great – can you send me the package for the Receivables Historical Aged Trial Balance Reports? I am having trouble downloading/saving it.

    Thank you!

  21. Kim Harold says:

    Steve,
    Can you send me the TB detail and summary report packages as well?

    Thanks! Kim

  22. D. Lawson says:

    Hi Steve,

    Can you please send the PM Historical aged Trial Balance Summary? When i went in and stripped the report myself and try to run it i get the “ILLEGAL SYNTAX” error. I did not add any new fields. Only removed fields and move fields between headers. Can those two actions create bad syntax?

    Thanks!
    David

  23. Steve says:

    Yes, deleting fields can sometimes do this if the report relies on them. I recommend reimporting the package then making the fields you don’t want invisible.

  24. Cale says:

    Hi Steve,

    Thanks for posting! Would you please send me the summary trial balance export packages for both? Much appreciated!

    Thanks!

  25. Tina says:

    Hi,

    May I ask for a copy of the summary reports please?

    Thanks,
    Tina

  26. Tania Mofflin says:

    Can I please get a copy of the summary versions, that would be great.

    Thankyou

    tania.mofflin@stjohnambulance.com.au

  27. Charles Allen says:

    Steve, would you send me the summary Receivables report?

  28. Charles Allen says:

    Steve,

    Great job on making these reports clean and exportable to Excel. Thank you for your work!

  29. Salida Kaing says:

    Hi Steve,

    Can you please send me the Summary AR Historical Aged TB’s?

    Thank you!!

  30. Salida Kaing says:

    Steve,

    Can you send me the package for the Summary Receivables Historical Aged Trial Balance Report?

    Thanks!!

  31. David Forman says:

    Thank you!!!!!!!

  32. Mandy says:

    Steve,
    can you please send me the AP& AR summary historical aged trial balance report? My email is myan@thisisamg.com. Thanks a lot!

  33. Laine Guillot says:

    Hey Steve, can I get all 4 reports sent to me? Clicking the download just shows me the text and doesn’t download the file. I’d also like the summary reports as well.

    Thanks, Laine

  34. Linda says:

    Hi

    Can you provide instruction what to do next after importing the RM download file? How to generate the Aged Debtors Trial Balance in excel form?

  35. Steve says:

    This was from an earlier post, try this:
    Several things can go wrong, try following these instructions
    1. Give yourself security access to the report
    2. On the report destination, be sure to mark file and choose ‘Tab – Delimited’ on the file format
    3. Be sure you know where you’re saving it and the file extension is txt.
    4. Open up excel, then choose file>>open
    5. On the file types choose text, or in the file name you can choose *.txt and click the enter key to display the file types, then open the file
    6. This brings you to the text import wizard, choose delimited and choose next
    7. For the delimiters mark the tab field, choose next and finish
    8. From there you sort out the things you don’t need, clean up and enter subtotals

  36. Lesrene says:

    Good day,
    Could you please send me the summary trial balance export packages?

  37. Amy says:

    Hi,

    Can I get the export package AP Aged Trial Balance in Detail. Thank you for Historical one. Is it possible to get both summary one’s as well?

    Thanks!

  38. Rob says:

    Hi Steve,

    Could you please send me the summary trial balance reports for AR and AP? Thanks so much, Rob

  39. deanne says:

    Could you please send the packages to me? I’m interested in the sql code to export to Excel

  40. Steve says:

    This is not a SQL report. It’s a modified Dynamics GP report that exports to text which you can then import to excel. If you want the GP report just choose the download button. If you want a reporting services report that is SQL-based go to this post below. To use this you need to have reporting services installed and configured.
    http://reese-consulting.com/export-payables-detail-historical-trial-balance-to-excel-from-reporting-services/
    Steve

  41. megan says:

    can you please provide summary

  42. Rebecca Walker says:

    Hi Steve,

    I have tried to import the file as explained, but am getting an error stating “unable to open customizations dictionary”. Do you have any thoughts on how to correct this as I need to pull the historical TB for AR and AP into excel and your package seems to be the most efficient I have found.

  43. Steve says:

    You can’t have other users in the reports dictionary you are importing to. This means if you are sharing a single reports dictionary across all users everyone has to be out of GP during the import process, that’s usually why that error message comes occurs.

  44. Rebecca Walker says:

    Thanks. I was able to import after your assistance, however, my report doesn’t show as yours does. I get a messy report, and assume it is a formatting issue.

  45. Rebecca Walker says:

    How do I get this to display as you have above? I get a bunch of lines that do not format as your example above.

    As it stands, I have to go in and reorganize the entire excel spreadsheet and this will take many hours of time. My apology, but this has been the most difficult thing ever.

  46. Steve Reese says:

    Rebecca,
    Assuming you imported the package correctly and set security, you should not have to do any formatting – the data should look just like the screenshot in my post. My guess is you’re choosing the wrong format (delimiter) when exporting to GP or when importing from excel. Here are some things to try – when exporting from GP on the report destination window the file format should be tab or comma delimited, when importing to excel be sure to use the same delimiter. Open excel first, then choose the file to start the import wizard. I prefer tab-delimited.

    Steve

Leave a Reply

Your email address will not be published. Required fields are marked *