I really thought making SharePoint Lists into pretty graphs and charts in SharePoint would be far more straightforward than it’s turned out to be. Recently, I was given a project where I had to do just that and while I read through about 30 SharePoint BI books, none really gave a straight answer on which of the various SharePoint reporting technologies I should use. It seems I had a choice between:
- Reporting Services
- SharePoint’s Reporting Services add-on
- A near-useless SharePoint Chart Web Part
- Excel worksheets
- Excel Web Services
- PerformancePoint Dashboards w/ a SharePoint list data source, SQL Server Analysis Server data source, Excel Workbooks data source, and/or Excel Services as a data source
So overwhelming.
Initially, I decided PerformancePoint Dashboards and PowerPivot would best suit my needs. PowerPivot is an Excel Add-on + a SharePoint Add-on that basically surfaces Analysis Services for reporting and as awesome as it is, the farm I’m using doesn’t currently support it (setting up PowerPivot is a bear, I’ll admit.) The good news is that it will be, and when it is, it’ll be SQL Server 2012’s version that comes with Power View and PowerPivot. Whattt!
Ultimately, I decided that I’m going to accomplish my task using Excel Services and PerformancePoint Dashboard Designer. This solution addresses automatic refreshing so your reports will stay (mostly) up to date. For complete beginners, this is basically how it works: You use export a SharePoint list query as a data source in an Excel spreadsheet, use Insert->PivotChart to work with the data, publish those specific charts and tables back to SharePoint using Excel Services, access those published items using PerformancePoint Dashboard Designer (which you can’t download from the Internet — you download the ClickOnce file from your SharePoint page), then publish a dashboard and tada!
(End User) Pre-Configuration
- Obtain access to a SharePoint 2010 server running Excel Services and PerformancePoint Services
- Update: Automatic data refreshing doesn’t work with Excel Services when using SharePoint lists as a data source. For that, you’ll need a client side script or PowerPivot.
- Enable PerformancePoint Services Site Features and SharePoint Server Enterprise Site features on your site (Site Actions -> Site Settings -> Manage Site Features) if they aren’t already.
- Add some items to the Tasks list that comes built-in to Team Sites. In the example below, I have 108 items, but you won’t need that much; I’m just working with live data.
- Create a new Business Intelligence Site. Site Actions -> New Site -> Business Intelligence Center
Working in Excel
- In the SharePoint Task List you just populated, click List in the ribbon bar, and Export to Excel. You’ll be prompted to download a lil .iqy file to your desktop.
- Open a new Excel Worksheet
- Insert -> PivotTable
- When prompted, click Use an External Data Source
- Choose Connection -> Browse for more… -> Select the .idq file you just downloaded
- This is important: Even though this connection is to a SharePoint list and you’d think the updates would be instantaneous, you have to explicitly set the connection to refresh in order to keep your chart up-to-date.
Let’s do this by clicking “Properties” -> Refresh Control -> Enable background refresh, Refresh Every 60 minutes, Refresh data when opening file.Don’t click any of those. If you need data refreshes, use PowerPivot or this client-side script. - Click all those boxes, then OK. If you ever need to get to this screen again, you can find it at Data -> Existing Connections -> Right Click -> Edit Connection Properties.
- A new toolbox will appear on the right. Drag “Status” to “Row Labels” and the “E” looking thing. This will setup the data to make a chart that counts the number of Completed tasks, New Tasks, Tasks Assigned to Someone Else.. and so on.
- Yay, now click PivotChart (under PivotTools -> Options)
- Choose a chart. I’m just gonna go with the default by clicking OK. Now, your spreadsheet should look something like this, with a PivotTable and PivotChart:
- See how it says “Chart 1” in the upper left hand side? That’s the default Chart name. Let’ change that by clicking on the PivotChart then clicking PivotChart Tools -> Layout -> PivotChart Name:
- I’m going to name mine Task Status Chart
- K, now click in the PivotTable area, and under “PivotTable” at the top, click Options. On the left, you’ll see where you can change the PivotTable Name. Do that if you want.
Publish Your New Chart (Don’t just Save)
- Save the Excel file to SharePoint
- File -> Save and Send -> Save to SharePoint -> Set Publish Options
- Publish Options -> <b>Select the individual items</b> by clicking on “Entire Workbook” then selecting All Charts -> OK
- Go ahead and check the PivotTable (notice we didn’t change that PivotTable’s name so it’s still the default PivotTable1) options, too.
- Save your file to SharePoint by browsing for a location or saving it to a library that’s already been saved.
Show Published Excel Chart in a PerformancePoint Dashboard
- Browse to the Business Intelligence site you created in the Pre-reqs.
- Hover over Create Dashboards then click Start using PerformancePoint Services.
- You’ll then see a page that says “Run Dashboard Designer“. Click that.
- Your computer will install about a 16mb ClickOnce application and this is where you’ll always come to launch it.
- This interface is a little confusing. All you have to know right now is that you’ll be using two item things — Other Report and Dashboard.
- Let’s first get the Report item: Click on PerformancePoint Content on the left hand side to make the “Create” menu available. Then Create -> Other Reports -> Excel Services
- Use the selection tools to navigate down to your spreadsheet, and ultimately, your published items. Below, I’ll select Task Status Bar Chart.
- Name your Report in the Properties Tab. You can now click Save or move onto the next step.
- Now we’re going to create a dashboard. Click the Dashboard Icon in the upper left hand corner.
- Select your layout. In the example below, I went with two panes, just because.
- When the dashboard appears, you’ll see “Details” on the right hand side.
- Expand Reports -> PerformancePoint Content -> Drag the report to any pane.
- While we could just click the Office ball in the upper left hand corner and “Deploy“, we need to adjust the size of the chart real quick
- Click the down arrow on the report within the pane, and select Edit Item -> Size -> Set to Auto-size Width & Auto-size Height.
- Now it’s time to Deploy your Dashboard! Click the Office ball thing -> Deploy
- Drill down to select your Dashboards library within your BI site.
- The new dashboard will load automatically.
- Congrats, you’ve done it! Time to bask in the glory of your work.
You may run into administrative errors along the way (I know I did), but that is out of the scope of this blog post. Next up? Accomplishing the awesomer version of this using PowerPivot. Speaking of, Microsoft could really do a better job of marketing PowerPivot as an all-around great reporting tool and not just something amazing for large data sets. This tool is super useful even for smaller data sets. I’ll probably be blogging about that sometime soon. More people should know!
Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.
Hi Chrissy, this is an excellent post! I have been battling the same problem for the past day or so and have come to the same conclusion. I've followed your steps to a T, but run into an error when trying to open the Excel file in the Excel Web App. The critical error in the ULS is: "Excel Calculation Services process tried to access a null virtual address for which it does not have access to". Did you run into this issue by any chance?
Hey Ricardo,
Glad you found this helpful! I did not run into that issue on the two servers I performed this on. Send me your Excel Workbook and the URL of your Tasks list. I'll mimic your setup on my own machine, load your excel file and see what happens. [email protected]
Thanks Chrissy, I just sent you an email with the info.
Hey Ricardo, I received your email. I'll get to it as soon as I can! Probably sometime in the next couple days.
I understand now why this happens.. unfortunately, I found out, as you confirmed, that you cannot refresh a SharePoint List data source within Excel Services. I'll post an update shortly.
Client side workaround for "automatically" refreshing data hourly
Hi,
I am also facing the same issue, were you able to resolve this issue.
Thanks
Kalpana
I'm unable to replicate the error. I believe it's because both of you exported your .odc files and used the SSS ID. I was going to say you can create a new site then export it but it's likely that the issues you are having are at the farm level. What do the logs have to say?
I have to rework this blog post. There is no way to refresh the data on the server side when using SharePoint lists as a data source within Excel Services. I ended up writing a client side refresher on a timer. I'll post that soon.
here :)
Hi Chrissy,
thanks for this great post. This is a smart and easy implementation solution for reporting. I was wondering why you use performance point instead of displaying the excel file in SharePoint directly?
On my side, I am still trying to get Excel Service work properly (esp. for data refresh…). I woul be glad if you have any web resource on this subject.
Thanks again !
Hey Gui,
I'm really happy you found the post useful! It's a matter of preference, I suppose. I prefer the look and feel of web pages as opposed to Excel. I also chose PerformancePoint over Excel Web Parts because it's quick and easy once you get it.
Good luck getting the data to refresh.. I'm going to be playing with the SSS ID stuff later next week. So far, Kerberos works for me.
Kerberos, as it turns out, wasn't actually working for me.
read here for more info (the news is eh)
How to set data labels for chart report?
Hey Krishna,
Click on the PivotTable, then at the top, go to Options and all the way to the left, it says "PivotTable1." You can rename it there :)
Thanks so much! this is very helpful. I have a doubt, Do I need to install PowePivot for SharePoint server? or just the add-in for Excel.
Hi Chrissy,
Wow excellent guide, been looking for something like this for weeks!!
However, when trying to connect the Excel book I'm getting the error "ERROR CONTACTING THIS SHAREPOINT SITE", this shows when I enter the Sharepoint address.
Any ideas?
Many thanks
Jon
Good stuff! I'm into a similar issue. I've built a XLS report that used an ODC file to make a connection to a SQL DB. I'm using the Secure Store Service to manage the connection (in the browser) and everything works file EXCEPT for refresh. The file will only display the data from the most recent refresh in the full client (Excel). So my question is simple, although the answer probably isn't…
What's the RIGHT way to display SQL View data in the browser and be able to refresh it dyniamically or on-load? Obviously it's NOT Excel Services as the error I get and associate posts state that SQL 'external data ranges' are not supported. PerformancePoint Reports????
P.S. – My contact info below…
Good stuff! I'm running into a similar issue. I have an XLS report that is using an ODC file to connect to my SQL DB (Project Server Reporting DB) and it will not refresh in the browser when accessing it in the BI Center. I see that using 'an external data range' like a SQL query is an unsupported feature on SharePoinr 2010. So my question is…
What is the 'correct' method to use to give users a browser based report that refreshes data from SQL??? (FYI – No SSRS running)
Hi I am using Sql services analysis filter of sharepoint and using ssas cubes for this filter. My number of filters are around 15000 so it shows me an error ‘Maximum number of items exceeded’. I have also tried increasing threshold and cache size from CA but nothing seems to be working. Please help.
This is far too complicated than it needs to be. If you are using Nevron Chart (https://www.nevron.com/) for SharePoint, you simply connect to a SharePoint list or Excel spreadsheet and drag and drop two fields to create this chart. You do this directly in SharePoint (no need to publish anything).
For me Performance Point has a broken usability scenario.
Kurt,
While I agree that it could be easier, it’s native to SharePoint and requires no additional installs or purchases. Neveron looks nice enough but in our shared environment, we are not allowed to install any third-party applications & requests for changes take well over 18 months.
Hi Chrissy,
Well I guess in situations like yours, you just have no other option. However, when there are no limitations I believe it is worth it to have 3rd party control that will cost a certain amount of money, but it will save you the time and struggle and it will allow to to concentrate on your actual work.
Hello Chrissy,
Please assist me with the following query.
I`m working on building a dashboard for a project site with multiple lists, wherein data will be pulled from SPS lists.
Once the dashboard is working, it will be saved as site template so can be reused for different projects in future.
Environment: SharePoint 2013 Enterprise (On Prem)
So far I`ve googled a lot and finding it difficult to opt either one of the path –
a) Build a Dashboard using CEWP & XSL in SPS2013
or
b) Use Performance point Dashboard designer and map it with SPSList.
Kindly provide your view, thanks in advance.
Jason