Build a Dynamic Refreshable Excel 2013 Dashboard with Dynamics GP data

Part 4 of a 5 part series

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

clip_image002

I’ve already created the connection in my spreadsheet. If you need to know how to do this the instructions can be found here: http://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.

clip_image004

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.

clip_image006

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

clip_image008

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

clip_image010

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

clip_image012

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

clip_image014

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

clip_image016

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

clip_image018

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

clip_image020

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

clip_image022

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

clip_image024

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

clip_image026

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”.

clip_image028

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

clip_image029

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

clip_image031

If you would like help with this dashboard or the SQL view used for the dashboard, please contract me.

Until my next and last post in this series enjoy your dashboard!

Advertisements