Creating a dashboard from your SharePoint lists using Excel ServicesFriday, December 22, 2017
If you use SharePoint in any capacity, chances are you're storing data in SharePoint lists somewhere. Of course, once you've got data inside lists, someone is going to want to report off of it.
In this article, we're going to take a look at how you can create dashboards inside of SharePoint that pull in data from SharePoint lists using Excel Services. Now there are several other technologies we could use, such as Reporting Services, PerformancePoint, PowerPivot, or Power View to create dashboards, but some of those technologies require enterprise licenses for SQL or the PowerBI functionality in Office 365.
In this example, I wanted to keep it simple and use technologies that people are more likely to have (although you do still need to have a SharePoint enterprise license in order to use Excel Services).
The chart we'll be building for our dashboard will be pulling in data from our SharePoint list via the "From OData Data Feed? option in Excel. To create a simple chart in Excel, follow these steps:
1. Open Excel 2013 and choose "Blank Workbook? as your template
2. Click on the "Data? tab on the ribbon at the top
3. In the "Get external data? grouping, click on the "From other Sources? button
4. Select "From OData Data Feed? from the list of options
5. In the textbox labeled "Link or File:? you'll need to put the URL to the listdata.svc for your site. This is done by simply taking the URL to your site and appending /_vti_bin/listdata.svc to it. So, for example, if the SharePoint site that my list is in has a URL of https://TestSite.SharePoint.com, the URL I'll need to enter is https://TestSite.sharepoint.com/_vti_bin/listdata.svc.
6. Click the "Next? button on the data connection wizard
7. If prompted to sign in, use credentials that have access to your list
8. Select the list you want to connect to from the available options on the data connection wizard
9. Click the "Next? button on the data connection wizard
10. Click the "Finish? button on the data connection wizard
11. Choose "PivotChart? on the import data screen, then click the "OK? button
If all has gone well, you should now be looking at a blank pivot chart in Excel. On the far right-hand side, you should see a window labeled "PivotChart Fields? that has a list of all the columns in the SharePoint list that you've connected to. To begin creating our chart, we simply need to start selecting the values that we want to display. In our example, we want to show the user the sales amount by category and month. Ultimately our chart looks similar to the following:
If you really wanted to get crazy, you could look at creating additional filters/slicers for your dashboard, or having your Excel charts render on a Web Part page using the Excel Web Access Web Part. Happy reporting/dashboarding!
From here all we need to do is save our workbook and upload it to SharePoint for users to view. It is important to keep in mind that the users viewing the workbook will need to have access to view the list that the workbook is pulling the data from if they want to refresh the chart.