Using Reporting Services Report (SSRS) with PerformancePoint Services (PPS)

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

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.

Back in January I did a posting in regards to Using Excel Services Reports with PerformancePoint Server (PPS).  This has been a very popular posting and I thought I would add another one in regards to using Reporting Services (SSRS) reports with PerformancePoint (in PPS 2007 this type of report was called SQL Server Report).

Some of the reason that you might want to include a SSRS report in your PPS dashboard would be because:

• leverage an existing report created by an end-user
• incorporate existing operational reports
• use additional charting options – map, area, range, scatter, polar, bar (not column), funnel, 3D, sparklines, data bars
• need more flexibility and control over reports, styles, colors, scales, etc.
• join multiple data sources into a single report
• combine relational and OLAP data into a single report

The example that I will be showing is using SSRS in SharePoint Integrated Mode, but you can also do this in Native Mode as well, you would just see a different setup screen when you are configuring the report in Dashboard Designer (a tad bit easier in my opinion configuring these in Native Mode – which is labeled as ‘Report Center’ mode in Dashboard Designer, confusing I know…).  I will also be using Report Builder 3.0 to create and deploy the report to the SharePoint site.

Go to Report Library in SharePoint site, select Documents from Ribbon, select New Document, and pick Report Builder Report	This will either launch Report Builder or ask you if you want to run and install the application if you haven’t done so yet
Report Builder is a ClickOnce application and by clicking Run you will install the application	Once installed the Report Builder application will start up
In this example we will build a Map	Reposition the map up a bit so it appears above the legends
A Bubble Map will be used to be able to analyze two metrics	A new data set will need to be added that contains the spatial data
A new data source will be added connecting to the Contoso Retail DW SSAS database	Use the Sale cube, filter for the United States, setup the Fiscal YQM as a Parameter, pick State Province Name, Sales Amount, and Sales Total Cost
Use STATENAME and map this to the State Province Name field from the data set	Pick a theme for the style, setup the bubble size to visualize Sales Amount, and polygon color for the Sales Total Cost
Setup Chart and Legend titles, polygon tooltip, remove color legend, resize/reposition map, and remove default marker size	Save report to SharePoint library
Now we are going to add a new Report to our existing PerformancePoint Content library	This will launch Dashboard Designer and like the Report Builder you may be prompted to install it (this is also a ClickOnce application)
If nothing launches then you need to make a small adjustment in your IE security settings to Enable ‘Automatic prompting for file downloads’	Now we will create the new PerformancePoint Report
Use the SharePoint Integrated mode, specify the URLs for the Report Server and the RDL file, uncheck the Show toolbar, and specify a name for the PPS report	Next we will create a filter that we can use with the report once it is displayed in the dashboard page
The filter we will create will be for the Fiscal YQM and we will remove periods that don’t have any Sales Amount	We will use a Tree style display and only allow a single selection
Name the filter and get ready to create the dashboard	Add a new Dashboard item
Name the dashboard item, page, add the filter, add the report, and remove the extra column (zone) on the page	Create a Connection (formerly link in PPS 2007) between the filter and the report
The filter will connect to the DateFiscalYQM parameter on the report and will pass the Member Unique Name (an SSAS member value to the report)	Save the PPS content items and deploy the dashboard to the Dashboards library
Select the Master Page and whether or not you want to include the page navigation or not	Test out the filter and view the results with the deployed PPS dashboard

My example here used the Contoso Retail DW sample data which is available from the Microsoft downloads here – Microsoft Contoso BI Demo Dataset for Retail Industry.  This is also using Reporting Services 2008 R2 which includes the new Map report item, Report Builder 3.0, PerformancePoint Services, and SharePoint 2010 Enterprise.

I have two other postings that I did earlier in the year in regards to the new Map report item here that you can check out if you have questions in regards to that:

• Mapping data with SSRS 2008 R2
• Mapping data with Report Builder 3.0 (SSRS 2008 R2) Case Sensitive

Download:

Feel free to download the PPS Workspace file (ddwx) and the SSRS report (RDL) file from my SkyDrive which I have included in a zip file.

 

You might find this posting useful if you want to reuse the workspace file – Migrating PerformancePoint 2010 Content to New Server.

Enjoy and Happy New Year!