INTELLIGENT TECHNOLOGIES, INC’S NEWS & TIPS BLOG

Simplify Your Reporting with Excel Reports in Dynamics GP

Beth Hodge

Beth Hodge, ERP Consultant

August 16, 2018

For years, users have had the ability to connect into Dynamics GP directly from Excel.  However, the only easy connection and therefore the only kind done by non-SQL techs was a direct connection to one GP data table, which very much limited the usefulness of this feature.

But, these days, the GP developers have created many more “user friendly” data views which link the information from several tables together, making it much easier to create reports without being a coding expert. You can find them by going to “Reporting Tools Setup” in GP Dynamics System Setup.

 

Depending on which version of GP you’re using, your window may look slightly different than mine shown below, but the result is the same. You can deploy the Excel reports from within Dynamics GP to a network path and these reports will have a “hot link” to your GP data.  This means that each time you open or refresh a report, it looks at the data and retrieves the information via an “ODC”, or “Office Data Connection.”  In other words, the Excel Report looks directly into the linked GP tables and retrieves results.

Here’s where you deploy the Excel reports in Dynamics GP (if you or your partner did not do this while installing or upgrading your system):

 

Screenshot of Simplify Your Reporting with Excel Reports in Dynamics GP setup

These Excel reports in Dynamics GP use your Windows credentials to retrieve data, so here are a few important things to remember:

  1. You must have the necessary security to run them, meaning you have permissions for the location on the network, as well as SQL permissions for the data.
  2. You will need to enable the external content from within Excel.

Once you’re setup, you will have a LARGE number of reports to check out. They are organized by GP company, then by module.  Here’s an example of the deployed reports.  Basically, the system deploys a report for each of the default SmartList favorites it creates upon installation:
 

screenshot showing the list of available Excel reports in Dynamics GP

While these reports are nice and many are very useful as-written, frequently clients need the information tweaked for their own specific needs.  Adding information to these Excel reports in Dynamics GP is usually a fairly quick task, as long as the desired additional data is already linked or easy to link in.  It’s a matter of modifying the ODC and possibly adding a new SQL view to your SQL installation.

 

Need help with this?

 

The Dynamics GP reporting experts at Intelligent Technologies, Inc. can help!  Whether you need Excel report modifications, assistance with report deployment or setting permissions, we’ve got you covered!

Contact us to get started using Excel reports in Dynamics GP.

Other Posts You May Enjoy

Acumatica Pro Tip: Recreate a Document Fast Using an Acumatica Template

Much of accounting is repeatable work that can be automated. Acumatica makes this easy with its powerful copy and paste feature. Use it to create a new template from your existing records or documents. Once a template is set up, you have full control over the items it includes and can reuse it anytime.

How Do I Contact Microsoft Tech Support?

Have you run into an issue with your Microsoft software? Not sure if you should ask your VAR for help or contact Microsoft directly? Our experts explain the when and how of contacting Microsoft tech support. Even if you’ve done it before, you should read this before opening your next support ticket.

Acumatica Pro Tip: How to Export Data to Excel

One of the best features of Acumatica is its user-friendly design. For anyone familiar with spreadsheets, its grid interface should feel like home. But sometimes, you still may prefer to manipulate Acumatica data in Excel. Luckily, getting data out of Acumatica and into Excel is a snap. Try it for yourself with our interactive tutorial.

Acumatica Pro Tip: How to Attach A File to a Record in Acumatica

One of the best parts about using a modern ERP like Acumatica is finally having a single place to store all your most important business information. Use the document attach feature to save photos, invoices, anything you like and then link them to a relevant record. Finally, you’ll spend less time searching and more time getting things done.

Sail Through Your Dynamics GP Year-End Close with Our New E-Guide

The holidays are stressful, closing your accounting books for the year shouldn’t be. Enjoy smooth sailing through the Dynamics GP year-end close process with our new e-guide. It brings together everything you need to know and wraps it up neatly in one convenient package. Download your copy now.