Dan English's BI Blog

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

Archive for January 24th, 2010

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:



Posted in PerformancePoint | Tagged: , , | 7 Comments »