Dan English's BI Blog

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

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

Posted by denglishbi on December 31, 2010

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.

image image
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
image image
Report Builder is a ClickOnce application and by clicking Run you will install the application Once installed the Report Builder application will start up
image image
In this example we will build a Map Reposition the map up a bit so it appears above the legends
image image
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
image image
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
image image
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
image image
Setup Chart and Legend titles, polygon tooltip, remove color legend, resize/reposition map, and remove default marker size Save report to SharePoint library
image image
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)
image image
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
image image
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
image image
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
image image
Name the filter and get ready to create the dashboard Add a new Dashboard item
image image
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
image image
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
image image
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:

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.

image image

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!

About these ads

17 Responses to “Using Reporting Services Report (SSRS) with PerformancePoint Services (PPS)”

  1. [...] Posts Using Reporting Services Report (SSRS) with PerformancePoint Services (PPS)Using Excel Services Reports with PerformancePoint Server (PPS)SQL Server 2008 R2 RTM [...]

  2. Armand Brunelle said

    Hello Dan – got this to work! Good stuff – thank you for sharing. I am wondering if there is a way to use the Reporting Services report as a Data Connection for use in creating a PerformancePoint Analytic Chart? I have some Reporting Services Report that I would like to create a chart from and put it on a dashboard. Thank you!

    • denglishbi said

      The Analytical reports, Grid and Chart, will only work with multidimensional data sources – Analysis Services and PowerPivot. You could use PowerPivot to connect to the Reporting Services report (from the Report or through Atom data feed) to pull in the data and then leverage the Analytical report option that way by pointing to the PowerPivot file.

  3. Armand Brunelle said

    Wow Dan – thanks for the quick reply! I will give PowerPivot a try.

  4. Steven Schotpoort said

    Hello Dan, I got this to work too, but when I open the dahsboard from a system different then the server I created the dahsboard on, I get a “Windows Security” logon screen. I only get to see the dashboard elements, but the reporting services report keep giving me the logon screen. Did you also encounter this. If so, how did you solve it?

    Kind regards,

    Steven Schotpoort

    • denglishbi said

      Were you able to ever see the report after you entered your credentials? Is the site setup as a Trusted Site in your IE security settings? Are you using Kerberos at all for this? Assuming you are not using Kerberos, the thing that you will most likely need to do is modify the Report Server configuration file. Either comment out or remove RSWindowsNegotiate and leave RSWindowsNTLM in the file rsreportserver.config located in the ReportServer folder on the server in the Authentication section. This should resolve your issue you are seeing.

  5. Hi Dan,

    Do you know if it is possible to pass parameters from on PPS SSRS report to PPS SSRS Report. For example I have a scorecard created with SSRS Report and would like to have a report action that passes in a parameter to an adjacent SSRS PPS Web part.

    I hope this makes sense.

    Thanks,

    Rich

    • denglishbi said

      There are no capabilities to make connections between PPS reports. I should probably be a little more clear on this. You can use scorecards to communicate to other items on the dashboard. So create the scorecard in PPS instead of SSRS and then pass the items to the SSRS reports, KPI Detail report, Analytical Grid/Chart, etc.

    • Thanks Dan,

      The reason that we have chosen to use a SSRS report in place of a PPS Scorecard is due to the way that the scoring is done. The target and bands and determined by the items in the report. ie the target for Group 1 Item 1 is 10, the first threshold for Group 1 item 1 is 15, etc. , the target for Group 1 item 2 is 20, the first threshold for Group 1 item 2 is 25. As far as I know that only way to accomodate this is to create a KPI for each specific scenario where the thresholds are different. Target is easy as long as it is stored by item in the datasource (Data source is relational so I cannot create the banding in SSAS KPI`s). Do you know if there is any planning in the works to allow the KPI designer to link the banding values to data stored in the datasource.

      Thanks,

      Rich

      • denglishbi said

        I am not aware of any plans for this functionality to be added to associate the banding values to data source values. The targets you could do, but not the banding.

  6. [...] Posts SSAS MDX Round = Banker’s RoundingUsing Reporting Services Report (SSRS) with PerformancePoint Services (PPS)Free Online PerformancePoint Services 2010 Training VideosInformatica PowerCenter vs. Integration [...]

  7. tavis said

    Hey Dan,

    first time poster, long time reader :D

    I have a scenario where I place an SSRS reporting into a PPS dashboard. The SSRS report has nothing but a gauge, and a table with some related data. For some reason the gauge image only shows up when I have the “show toolbar” option checked, otherwise the gauge shows up as a broken image. In either case, the data for the table always shows up. If I navigate to the report server directly and view the report I have no problems, it only seems to happen from within PPS (both in designer and once deployed). I’ve experienced this in a few different environments……have you ever had this happen? Any thoughts on the matter would be greatly appreciated.

    other details
    SSRS is in integrated mode 2008R2
    3 server SharePoint 2010 farm (wfe, app, db)

  8. cemuney said

    Thanks for article.
    What about using Actions in SSRS Reports in Sharepoint Integrated Mode. (Not the Actions in cube).

    As an example based your report.
    When i click the State i want to see the Detail report by Cities or product Categories.
    I want to see two reports in the same web pages.

    How can we use “go to bookmark, go to report” or

    how can we change the second reports parameter(filter) by clicking the row on the left report? :(
    Cem Uney

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 93 other followers

%d bloggers like this: