Slicing Dynamics GP 2010 Data in Microsoft Excel Dashboards
If your a GP user, here’s a little creative use of a few tools that you possible already have.
It’s a demo of a simple interactive Excel Dashboard using one of the data connection files that ships with GP2010RTMCoreMR.(Ask your Partner About this).
What we have is an excel file which uses a pivot table to display the Price, Margin and GP% (Gross Profit Percentage) of Sales Documents. For this Pivot I choose to set the rows as “Item Classes”, and I used the Excel “Slicer Tool” to select the customers.
I am able to select different customers in the slicer tool and review the numbers that appertain to that customer in reference to the Item Class Rows and Price,Margin and GP% Columns. In this next screenshot, this particular customer has only order products in one item class.
The Pivot will also show you when there are no records as in the example below.
This all took about 10 mins. to set up, execute and be fully functioning in my demo VPC machine. If you want to know more drop me a line.
Excel Dashboarding in GP 2010
A few days ago I met a prospective client, who was very exact in defining his requirements 
During our first meeting, one the requirements that was consistently mentioned many times was:
“We need Dashboard capabilities for quick view on cash, payables, and receivables”
Well Dynamics GP 2010 has capabilities that can fulfill this requirement.
We can see that there a 7 boxs of data. 4 boxs contain excel based charts and 3 boxs contain colum data from GP.
This example use the default “dummy data” that comes with all installations of GP and is been used here as an example. (It would be unethical of us if we were to show you a “real live” dashboard of another customer.
The example above uses “built in out of the box” data connections to the GP Data that resides inside SQL.
What that means is if you already use excel in your organization the you can extend the use of excel to include “Live” data out of GP 2010.
In a later post, we’ll explore some other dashboard options that are available with GP 2010.
Here’s a related Video that shows how to publish Refreshable Excel Reports in Dynamics GP.
Excel Tool- Chart Advisor
Chart Advisor
For those of you that demand a better chart in excel, have a look at this freebie by clicking on the picture.
