Using Excel Services Reports with PerformancePoint Server (PPS)
Posted by denglishbi on January 24, 2010
UPDATE (10/1/2010): I corrected the image of the screenshot for configuring the Products parameter in the Excel file. I originally had one displayed with DateFiscal for the setup instead of the one referencing Products. I apologize for that. If you wanted to do one for DateFiscal you would need to setup the named range for Sales!$B$4.
UPDATE (6/21/2011): Modified table layout of step-by-step walkthrough to be two columns instead of three. Added a downloaded PDF file as well.
One of the nice features about creating dashboards with PerformancePoint Server (PPS) is the ability to incorporate different types of reporting types like Analytical Grids/Charts, SQL Reporting Services Reports (SSRS), ProClarity Analytical Views, and Strategy Maps. Another one is being able to leverage Excel Services. As we all know Excel is one of the most widely used tools, so it only makes sense that at some point we will probably want to be able to integrate an existing Excel file within the organization.
Some of the reasons that you might want to use Excel as a report in a PPS dashboard could be:
- use an Excel report that has already been developed by end-user
- leverage some of the nice Excel 2007 conditional formatting options – data bars, color scales, and icon sets
- want more control over the style and format of the chart or table
- need to incorporate a pie, bar (not column), area, scatter, radar, or bubble chart into your dashboard
The first thing that you will need to do ahead of time before you are able to do this is enable Excel Services on SharePoint 2007 and configure it. If you are unfamiliar with this process you can check out this reference – Demo: Enable Excel Services and data connections for a SharePoint team site and check the video and download it for reference. NOTE: Excel Services is an Enterprise feature within SharePoint Server.
Once you have Excel Services configured you will then need to create the Excel file with the items that you would like to include in your dashboard. In this example I will be using Analysis Services as the data source (the ever famous Adventure Works DW) and will create an Excel PivotTable and PivotChart. These two Excel items will then be made available in SharePoint (Excel Services) for reference by the PPS dashboard that will then be connected to dashboard filters. NOTE: this demonstration is using a virtual environment that is setup in a standalone configuration (meaning everything is installed on the server – SQL Server, PPS, SharePoint, etc.).
(You can click on the images below to view a larger image – the flow goes from left to right)
NOTE: If you don’t see the Excel Services parameter in Dashboard Designer it is most likely because in the Excel filter you have multiple values selected. Go back into the file and make sure only one item is selected so that it will be recognized and pulled into the parameters section in Dashboard Designer.
For some additional references in regards to Excel Services checkout some of the links below: