Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Using Excel Services Reports with PerformancePoint Server (PPS)

Posted by denglishbi on January 24, 2010

[tweetmeme source=”denglishbi” only_single=”false”]

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)

image image
Open up a new Excel workbook and create the data connection to the Analysis Services database Connecting to a local instance of Analysis Services
image  image
Select the AdventureWorksDW database and the Adventure Works cube Enter a name for the Office Data Connection (odc) file
 image  image
Going to create a PivotChart and Pivot table Select ‘Reseller Sales Amount’, ‘Date.Fiscal’, and ‘Geography.Geography’
image image
Only select FY 2004 in the column labels and change the chart type from Column to Pie Remove Chart Title and add Data Bars conditional formatting to the PivotTable
image image
Click in the PivotTable and in the Options enter a meaningful name and deselect the grand total options since we are only going to show one time period in this example Now select the pie and go into the Format Data Labels options and choose the options you would like to include
 image   image
Select the PivotChart and in the Properties in the Layout portion of the Ribbon give the Chart a meaningful name Changed the worksheet name and add an additional hierarchy from the field list in the Report Filter section – Product.Product Categories
 image  image
Go into the data connection properties and export the data connection to the data connection library in SharePoint (just like in the demo link provided above) Publish the Excel workbook to Excel Services
image image
Specify the path to the document library, provide a meaningful file name, and specify the Excel Services options (I just selected items in the workbook and then added the parameter in to be used with PPS) Setup the reference in Dashboard Designer to the Excel Services workbook and the PivotTable
image image
You can click on the View button to see a preview of the item you are referencing Setup the reference in Dashboard Designer to the Excel Services workbook and the PivotChart (you can copy and paste the previous report, change the name in properties, and change the item name being referenced)
image image
Create the dashboard page and link the filters to the reports Deploy the dashboard page
Test the dashboard page filter and drill into the data within the table for further analysis  

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:



7 Responses to “Using Excel Services Reports with PerformancePoint Server (PPS)”

  1. […] Services (SSIS) ComparisonMapping data with SSRS 2008 R2SQL Server 2008 R2 RTM Evaluation InstallUsing Excel Services Reports with PerformancePoint Server (PPS)Using Reporting Services (SSRS) with […]

  2. Dragan Kovacevic said

    Hi Dan,
    Great article first of all.

    I followed your example and when I select the value from the filter in SharePoint dashboard I get error: “An error occurred while attempting to set one or more parameters in this workbook. As a result, none of the parameters have been set.”

    I also assumed that prior to linking filter to the reports you created one with the filter dimension: Product.Category and added it to the dashboard in the header zone (what is visible from the screen shoot).
    In your article (in the screen shoot) you had Filter Link Editor to create link between filter and the reports. The only way I was able to do similar thing is by setting up connection between parts by using Connection dialog (selected Create Connection in the part action menu).
    In the Items tab I have
    – Get values from: Header – (1) Products
    – Send values to: Right Column – (1) AW Sales Chart (name of my report)
    In the Values tab I have:
    – Connect to: Products
    – Source value: Member Unique Name

    Any Idea why I did I go wrong?

    • denglishbi said

      It is possibly because of your filter you created. I set mine up using the member selection against the ‘Product.Product Categories’ hierarchy. Try using this instead. Your end-point will be the Products on the Excel Report and then using the Member UniqueName. Let me know if that does not work for you and I can send you my workspace file if needed. I should have included my files for reference. I am going to be doing another posting using an SSRS SharePoint report in another posting and I will make sure I include my files this time – I will bundle them all together.

  3. […] Posts Using Excel Services Reports with PerformancePoint Server (PPS)SQL Server ‘Denali’ SQL11 CTP1 Install ExperienceFree Online PerformancePoint Services 2010 […]

  4. […] Posts Using Reporting Services Report (SSRS) with PerformancePoint Services (PPS)Using Excel Services Reports with PerformancePoint Server (PPS)SQL Server 2008 R2 RTM Evaluation InstallPerformancePoint Services 2010 Analytical Grid Filter Fix, […]

  5. […] Using Excel Services Reports with PerformancePoint Server (PPS) […]

  6. Amelie BM said

    Hello, I have this error: ” An error occurred while attempting to set one or more of the parameters in this workbook.
    As a result, none of the parameters have been set.
    Click OK to return to the workbook.”

    My data source is a table, I created a pivote table and chart. Upload the file to Sharepoint, created filter and reports In the same way as mentioned Dragan, but I can’t find solution.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: