Dynamic SharePoint List View Dashboard with Excel Viewer
This post will cover how to create an Excel Dashboard inside of a SharePoint list or library view, using the Excel Viewer web part and an Excel sheet linked to your list or library. The Excel sheet will use pivot charts and tables to display the data we want from the list in a graphical manner. We will get Business Intelligence Visualizations without the need for complex SQL.
1. First we need to export our list to Excel via List>Export to Excel
2. Open the iqy file in excel. This creates the necessary data connection in Excel which we will examine next.
3. Because this is an external data source we need to Enable the connection.
5. We want to create a table from all this data, so select the INSERT tab on the ribbon and click on PivotTable
7. SharePoint created a new connection called owssvr and we will select that for our connection.
8. At this point we can delete the owssvr worksheet SharePoint created if we don’t need to use it anymore.
9. With the new PivotTable created, let’s go ahead and pick the fields we want to use for both this table and the PivotChart we will be adding. Select NextFY Value ($K), Op Status, and Total Value ($K)
10. We want to see totals for our value fields, so let’s move those into the Columns and make sure they are counts. We can leave the Op Status field in the Rows section since we are organizing our table by that.
11. Here we can see our new table layout showing labels and
12. Now we can add the PivotChart by selecting INSERT > PivotChart
13. Select a Clustered Column Chart
14. You can rename the chart by clicking on it and selecting the name in the dropdown at the top left of the Excel ribbon.
15. We want to use the same configuration as our table but for both, let’s go ahead and add the ability to filter by Business Unit. Do this by dragging Business Unit into the Filters section.
16. Now we can see a new filter dropdown on our table.
17. We see the same for the chart
18. At this point we can create new sheets or tables and graphs for the different views we want by selecting the filters we may need for that particular view. Click the filter dropdown and select Defense then click OK
19. Do the same for the chart and we can see that the funnel icon appears.
20. Now we have a filtered version of both the chart and table. We can create additional ones for each view if we need.
21. If we are going to be including multiple charts and tables in the excel for use in the Excel Viewer web part, we will want to rename them to something a bit more descriptive. This is done by selecting the chart or table you want then clicking on the Name text box in the top left of the ribbon.
23. We will upload the Excel to a document library
25. This will open up the view page, which has web part zones available to add web parts to.
26. Click Add Web Part the select Business Data>Excel Web Access
27. Now on the dropdon on the top right of the webpart select Edit Web Part
28. Click on the points of ellipses to select the workbook we just uploaded
29. Select the named item All Opportunities Table or whatever you happened to name that particular item, then uncheck the Autogenerate options and select non for the Type of Toolbar as well
31. Add another for the chart and select the same options except for the Named Item
32. Again set the appearance options for the chart web part
33. You can now do the same for the other view(s).
Our views now have some great tables and charts providing additional insight that refresh with the view loading.