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.I’ve already created the connection in my spreadsheet.
2. Highlight all the column data and then click on “INSERT” tab to add your first pivot table.
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.
4. Click on the filter icon of the Row Labels and select “Top 10” of the Value Filters section.
5. Change the Column descriptions to “Top 10 Customers” and “Total Sales.”
6. Select the Column filter icon again but this select “More Sort Options.” From the window that pops up, select “Descending” and“Total Sales.”
7. The end result of your first pivot table should look like the below screen shot.
8. Navigate back to Sheet 1 or the live SQL data and insert another pivot table into the same Sales Dashboard tab.
9. This time select the “Item Description” and “Quantity.”
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.
12. The end result should look like the screen shot below.
13. Now navigate back to the live SQL data and another pivot table to the dashboard for “Salesperson” and “Total Document Amount.”
14. Navigate to the insert tab and select the “Timeline” option from the ribbon. From the window that pops up select “Document_Date.”
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.”
16. Select all connections from the window that pops up and then click OK.
17. The end result is a Dynamic refreshable Sales Dashboard for your end users as shown below.
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!
