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.

image1

This produces a more columnar report.

image2

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

image3

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.

image4

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

image5

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.

Dynamics GP Coffee Break | Setting up ACH and EFT in Dynamics GP

This webinar will go through the basics of setting up ACH / EFT for accounts payable and accounts receivable. We'll walk you through the settings and the default setups.

May 21
9:00 am – 9:25 am

Register here

Webinar - Setting up ACH and EFT in GP

May. 21
9:00 am – 9:25 am

Register