SharePoint 2010: Creating Dashboards and Charts for SharePoint Lists using PerformancePoint and Excel Services from Start to Finish

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.

Posted in BI, SharePoint
25 comments on “SharePoint 2010: Creating Dashboards and Charts for SharePoint Lists using PerformancePoint and Excel Services from Start to Finish
  1. Ricardo Guzman says:

    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?

  2. Gui says:

    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.

  3. krishna says:

    How to set data labels for chart report?

  4. Lorena says:

    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.

  5. Jon Mansell says:

    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

  6. 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????

  7. P.S. – My contact info below…

  8. 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)

  9. pratik says:

    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.

  10. Kurt Werner says:

    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.

    • Chrissy LeMaire says:

      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.

  11. Kurt Werner says:

    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.

  12. Jason says:

    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

4 Pings/Trackbacks for "SharePoint 2010: Creating Dashboards and Charts for SharePoint Lists using PerformancePoint and Excel Services from Start to Finish"
  1. […] http://blog.netnerds.net/2012/03/sharepoint-2010-creating-dashboards-and-charts-for-sharepoint-lists… Like this:Like Loading… By Swetha Sankaran, on April 17, 2013 at 11:28 AM, under Uncategorized. Tags: Dashboard with PivotTable in Sharepoint 2010, SharePiont task lists. No Comments Post a comment or leave a trackback: Trackback URL. « Check if your sharepoint site is a publishing or non publishing site […]

  2. […]  Navigate the Islands!Your SharePoint lists are all set up and they are full of interesting data – but until that data is presented in an aggregated fashion, it will be pretty difficult to make it useful. Your users need to be able to compare, contrast, create ad hoc reports and assess the information. Your high level folks need dashboards, scorecards, KPIs and gauges.You need reporting options!Out-of-box SolutionsSharePoint has some reporting options built in:Create your own views from the settings page Pros: can be for all users or just for you, allows filtering (including the Me filter, which displays just your items)Cons: can’t join two lists, have to have correct permissionsHow-to links: http://office.microsoft.com/en-us/sharepoint-server-help/create-modify-or-delete-a-view-HA010377693.aspx http://blogs.msdn.com/b/dinaayoub/archive/2010/04/22/sharepoint-2010-how-to-create-a-view.aspxCreate views using SharePoint Designer Pros: variety of layout options, ability to join two listsCons: SharePoint Designer learning curve, easy to break thingsHow-to links: https://www.nothingbutsharepoint.com/sites/eusp/Pages/How-to-Link-Two-Lists-and-Create-a-Combined-View-in-SharePoint-2010.aspxhttp://office.microsoft.com/en-us/sharepoint-designer-help/create-a-custom-list-view-using-sharepoint-designer-HA010379079.aspxMore FlexibleSometimes you need to be able to visualize the data in ways that aren’t possible in a list view. More flexible options includeExcel workbook linked to list(s) Pros: familiar interface, powerful calculation and analysis enginesPros: new features like PowerPivot and PowerView allow for data modelling and aesthetically pleasing reportingPros: PowerPivot Gallery in SharePoint (on-prem only at this time) allow you to publish and share easilyHow-to links: http://office.microsoft.com/en-us/excel-help/power-view-explore-visualize-and-present-your-data-HA102835634.aspxhttp://www.powerpivotpro.com/what-is-powerpivot/http://www.youtube.com/watch?v=3xhwOEGjhxwAccess database linked to list(s) Pros: Access can be used to report on complex data spanning two or more listsPros: easy to read & write, familiar interface for some usersHow-to links: http://www.youtube.com/watch?v=KBA-Sz72Spwhttp://office.microsoft.com/en-us/windows-sharepoint-services-help/export-or-link-a-datasheet-view-to-access-HA001160860.aspxMost ScalableEnterprise reporting solutions can help you keep up on your data even as it grows.SQL Server Reporting Services Pros: powerful reporting tool, can use lookup functions (2008 R2 or later) or sub-reports to join list dataPros: Report Builder integrated with SharePointPros: can be scheduled or subscribed toCons: requires server install of productHow-to links: http://dataqueen.unlimitedviz.com/2012/07/create-a-sharepoint-ssrs-report-library/http://aproblemshared.fusionmanageit.co.uk/2012/08/aggregated-lookups-in-ssrs.htmlhttp://msdn.microsoft.com/en-us/library/ee210649(v=sql.105).aspxPerformancePoint Pros: reusable KPIs, integration with other Microsoft products (including SSRS reports!)Pros: can consume data from an Excel PowerPivot modelCons: requires SharePoint Enterprise or E3/E4 for Office 365Cons: some features only available for data cubesHow-to links: http://msdn.microsoft.com/en-us/office/bb660518.aspxhttp://www.youtube.com/watch?v=5VOG4fXr3v4http://blog.technock.net/2013/03/sharepoint-2010-performance-point-score.htmlhttp://gnetgroup.wordpress.com/2011/11/03/building-a-performancepoint-dashboard-without-a-cube/http://blog.netnerds.net/2012/03/sharepoint-2010-creating-dashboards-and-charts-for-sharepoint-lists…Learn MoreQdabra offers short video tutorials and in depth how-to hands-on-labs. Check out the following links:YouTube Video highlighting the above technique: ttp://www.youtube.com/watch?v=EC2b61W60IU&list=PL581899A129522F53Qdabra Toolkit includes detailed how-to instructions and sample templates: http://www.qdabra.com/webstore/p/75/qdabra-webinar-kit-reporting-with-performancepoint Published Jul 23 2013, 08:29 AM by alyssastgermain […]

Leave a Reply

Your email address will not be published. Required fields are marked *

*