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 | Management Reporter Webinar

During this webinar, we’ll explain how to create reports using Management Reporter in Dynamics GP. You’ll learn about using Rows, Columns, Trees, and setting Report Configurations.

June 21
9:00 am – 9:25 am PST

Register

Dynamics GP Management Reporter Webinar

Jun. 21
9:00 am – 9:25 am PST

Register