Deploy Historical Aged Trial Balance Reports to Excel in Dynamics GP

If you have ever struggled with reconciling your Payables or Receivables module in Microsoft Dynamics GP, or you just want to get your Aged Trial balance Reports into Microsoft Excel, then this blog post is for you.  I am going to show you two tips that you can use to make this a better process.

Formatting your trial balance report to make Excel reporting easier:

The first tip is to use Report Writer to modify the report and produce a stripped down version of the report.

You can see below that I have removed most of the header records from the report and really streamlined the report down to the basics.

This produces a more columnar report.

When you print this as a tab delimited file, it easily opens as an Excel spreadsheet.

Creating an Excel Refreshable report based on a SQL stored procedure.

The next tip requires a little more advanced knowledge of Dynamics GP and allows you to build an Excel refreshable report based off a view or stored procedure.

The following is an Excel report that refreshes each time it is opened or when I change the “as of” date.

It has been built by calling a stored procedure from Dynamics GP that populates the data for me.

The connection Command Text is built to use company database TWO and runs a stored procedure I created called Ap_hatb.  The stored procedure has a built in parameter that I have defined as cell B2 from my spreadsheet.

In either instance, we are able to pull the data from the Historical Aged Trial Balance report into and Excel spreadsheet with little effort.  If you have been looking for a tool that will enable you to review these reports in Excel, give us a call and we will be happy to help deliver the reports you have been looking for.