Simplify Your Reporting with Excel Reports in Dynamics GP

Dynamics GP | technical how tos
Written by Beth Hodge
Published on August 16, 2018
w
0 Comments
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.

Latest Articles

Related Articles

Acumatica Pro Tip: Eight Simple Steps for Acumatica Password Reset

Not being able to remember your password when you need to log into an important system like your bank account or your ERP system can be a very frustrating experience. Luckily, most online services have a streamlined the process for retrieving or resetting a lost password. Acumatica is no different. The Acumatica password reset process is easy.

Acumatica Pro Tip: Acumatica ERP Mobile App Won’t Load on Android

Having an app that won’t load, but instead simply shows a white screen can be a very frustrating experience. This often happens when the user reinstalls the mobile application in Android without fully clearing the data and cache. Follow these step by step instructions to get things working normally again.

Four Can’t Miss New Features Available in Dynamics GP 2019

As you might have heard, Microsoft released a new version of GP at the start of this month. Dynamics GP 2019, or just Dynamics GP as some now call it, includes a lot of great new features users like you requested. Today, I’ll show you four of our favorites.

On-Demand Dynamics GP Report Pack Webinar Now Available

Explore two of the most popular modules in the Dynamics GP report pack, the financial and sales order modules. No matter what business you’re in, having up-to-date financial and sales information at your fingertips is priceless. No more wasting time creating reports only for them to be obsolete as soon as you press print.

Webinar: Revolutionize Your Dynamics GP Reporting with the Power BI Report Pack

You know there’s a wealth of data hidden inside your dynamics GP system. The problem is, turning it into useful information can be difficult and time-consuming. But not when you have the Power BI Report Pack. See why no dynamics GP user should be without this tool during a free webinar on September 24 at 10 AM.

About the Author

As one of Intelligent Technologies, Inc’s Microsoft Dynamics Great Plains (GP) experts. I have over 20 years’ experience with this and other similar accounting packages, both as a consultant and an end user. It’s my job to oversee Dynamics GP implementation, configuration and training for new clients, as well as to assist current clients with their GP related questions or issues. And, I'd love the opportunity to put my Dynamics GP skills to work for your business. To get started, call (336)315-3935.

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *