Build a Refreshable Excel 2013 Dashboard with Dynamics GP Data

Visit Website View Our Posts

Part 4 of a 5 part series

By Barry Crowell, Senior Business Consultant at KTL Solutions, Inc.

In previous posts, we have talked about the benefits of business intelligence, created a report with Jet Express for Dynamics GP and analyzed Dynamics GP sales data using market basket analysis to help increase sales.  Today we will be connecting our Excel 2013 spreadsheet to a SQL view to create a dynamic refreshable sales dashboard.  So let’s jump right in and get started building our Sales Dashboard.

 

1. Open Excel 2013 and create a connection to our SQL view.

 

resized 1

 

I’ve already created the connection in my spreadsheet.  If you need to know how to do this the instructions can be found here: https://office.microsoft.com/en-us/excel-help/connect-a-sql-server-database-to-your-workbook-HA103791059.aspx

 

2. Highlight all the column data and then click on “INSERT” tab to add your first pivot table.

 

resized 2

 

 

3. Drag “Customer Name” from the Choose fields’ area to the Rows section and drag “Total Document Amount” to the Values section making sure that sum is the totaling amount selected.

 

resized 3

 

 

4. Click on the filter icon of the Row Labels and select “Top 10” of the Value Filters section.

 

Pic4

 

 

5. Change the Column descriptions to “Top 10 Customers” and “Total Sales.”

 

Pic5

 

 

6. Select the Column filter icon again but this select “More Sort Options.”  From the window that pops up, select “Descending” and “Total Sales.”

 

resized 6

 

 

7. The end result of your first pivot table should look like the below screen shot.

 

Pic7

 

8. Navigate back to Sheet 1 or the live SQL data and insert another pivot table into the same sales dashboard tab.

 

resized 8

 

 

9. This time select the “Item Description” and “Quantity.”

 

resized 9

 

 

10. Repeat steps 4 through 7 from above.

11. Now let’s add a chart based on the first pivot table.  Click on a cell within the first pivot table then navigate to the insert tab.  Select the pie chart icon to insert the chart into the dashboard.

 

resized 11

 

12. The end result should look like the screen shot below.

 

resized 12

 

 

13. Now navigate back to the live SQL data and another pivot table to the dashboard for “Salesperson” and “Total Document Amount.”

 

resized 13

 

 

14. Navigate to the insert tab and select the "Timeline" option from the ribbon.  From the window that pops up select “Document_Date.”

 

resized 15

 

 

15. This inserts the “Timeline” functionality to the tab on one of the pivot table but we want it on all dynamically change all pivot table data when the user selects a time frame.  To do that, right click on the timeline and select “Report Connections.”

 

resized 15

 

 

16. Select all connections from the window that pops up and then click OK.

 

Pic16

 

 

17. The end result is a dynamic refreshable “Sales Dashboard” for your end users as shown below.

 

resized 17

 

 

If you would like help with this dashboard or the SQL view used for the dashboard, please contact sales@ktlsolutions.com. Until my next and last post in this series enjoy your dashboard!

by KTL Solutions

 

4 thoughts on “Build a Refreshable Excel 2013 Dashboard with Dynamics GP Data”

  1. Hey There. I discovered your blog the usage of msn. That is a
    really neatly written article. I will be sure to
    bookmark it aand return to learn extra of your useful information. Thanks for
    the post. I'll definitely return.

Leave a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Show Buttons
Hide Buttons