Posted on August 11, 2016|by: Ricardo Palhano| No Comments

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.

excel1

1. First we need to export our list to Excel via List>Export to Excel

excel2

2. Open the iqy file in excel.  This creates the necessary data connection in Excel which we will examine next.

excel3

3. Because this is an external data source we need to Enable the connection.

excel4

4. Now we can see all of the data that is pulling from the SharePoint list
excel5

5. We want to create a table from all this data, so select the INSERT tab on the ribbon and click on PivotTable

excel6
6. Now we can select Use an external data source and click on Choose Connection… so that the new PivotTable and Chart can pull directly form the SharePoint list.

excel7

7. SharePoint created a new connection called owssvr and we will select that for our connection.

excel8

8. At this point we can delete the owssvr worksheet SharePoint created if we don’t need to use it anymore.

excel9

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)

excel10

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.

excel11

11. Here we can see our new table layout showing labels and

excel12

12. Now we can add the PivotChart by selecting INSERT > PivotChart

excel13

13. Select a Clustered Column Chart

excel14

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.

excel15

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.

excel16

16. Now we can see a new filter dropdown on our table.

excel17

17. We see the same for the chart

excel18

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

excel19

19. Do the same for the chart and we can see that the funnel icon appears.

excel20

20. Now we have a filtered version of both the chart and table.  We can create additional ones for each view if we need.

excel21

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.

excel22
22. Go ahead and save the document as an Excel Workbook

excel23

23. We will upload the Excel to a document library

excel24
24. Now on the view you want to add the Excel chart and table to select Edit Page

excel25

25. This will open up the view page, which has web part zones available to add web parts to.

excel26

26. Click Add Web Part the select Business Data>Excel Web Access

excel27

27. Now on the dropdon on the top right of the webpart select Edit Web Part

excel28

28. Click on the points of ellipses to select the workbook we just uploaded

excel29

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

excel30
30. We can now adjust the appearance of the web part.  Rename the webpart, and set a height and width that will display all of the table without producing scroll bars.  Also set the Chrome Type to None

excel31

31. Add another for the chart and select the same options except for the Named Item

excel32

32. Again set the appearance options for the chart web part
33. You can now do the same for the other view(s).

excel33

Our views now have some great tables and charts providing additional insight that refresh with the view loading.

Filed in: Collaboration, Sharepoint, Technology, Tips & Tricks

Your email is kept private. Required fields are marked *

*
*