One of our distribution clients using Microsoft Dynamics GP would like to pull data from multiple databases, including Dynamics GP, and bring it into Microsoft Excel. This can be done using Excel Builder.

For this distribution client I am creating a SQL script that brings in data from different tables in Microsoft Dynamics GP. In this case we’re pulling data from multiple companies.  You can actually pull from multiple databases, which may or may not be Dynamics GP databases.  So that gives you flexibility to use outside systems to incorporate data.

The next step is to connect that data to Microsoft Excel. Once it’s in Excel, then you can go ahead and put the data into a pivot table. The data is live so the Pivot table can be refreshed anytime and you always have current information.

This process puts the Dynamics GP data into a program that our customers are very familiar with once it is in Excel I know they can do some really great things with it.

This particular distribution company uses SmartList Builder. They have created some SmartLists that incorporate additional tables that aren’t included in the standard SmartList options.  I can bring that new list into Excel and now it has all the data that wouldn’t normally be included in the standard SmartLists or Excel reports that come with Microsoft Dynamics GP.

Basically I use the SQL query that was created by that SmartList Builder and create a SQL View. And then once that SQL view is created we are able to create an Excel spreadsheet containing that data.  It is very straightforward but very powerful.

If you would like to use data from Dynamics GP and other databases, and work with it in Microsoft Excel, CAL Business Solutions can help. Contact us at sales@calszone.com or 860-485-0910 x4.

Get Microsoft Excel video tips

Get more Microsoft Dynamics GP tips

By John Miele, CAL Business Solutions, www.calszone.com