How do I integrate SSRS Reports with Dashboard Filters


PerformancePoint Monitoring Filters work with SSRS reports, both relational and OLAP. To configure a dashboard pages to use common filters to drive SSRS reports and other PerformancePoint views, follow the below steps. NOTE: this functionality is dependent on CTP3 or later releases of PerformancePoint Monitoring.

For this example, we will configure a dashboard configuring containing one page with one scorecard view (OLAP data source) and one SSRS Relational report and a second page containing the same OLAP scorecard and a second SSRS OLAP report. Note, the SSRS reports are both configures in SSRS to have an SSRS parameter based on Geography – the parameters must be set up in SSRS prior to connecting the parameter to a Filter in the PerformancePoint dashboard.

Create report views for your SSRS reports



  1. Right click the Reports folder in the dashboard designer and select New Report.



  2. Select SQL Server Report and click OK.



  3. Enter a name for your report and click Finish.



  4. Enter the Server URL to the SSRS report server. EX: http://servername/reportserver.



  5. Click the Browse button and select the SSRS report you wish to include. Click OK.



  6. image



  7. Ensure that the parameters came through and are listed in the Report Parameters section.



  8. Select each parameter, click Edit and set a default value.



  9. Publish the report to your PerformancePoint Server by selecting the Home tab and clicking Publish All.



  10. Repeat steps 1 through 8 for all your report views you wish to include. Note that the process does not differ for Relational vs OLAP SSRS Reports.


Configure your dashboard



  1. Right click on Dashboards in the Dashboard Designer and select New Dashboard.



  2. Select your template (in this example, we will choose 3 Rows) and click OK.



  3. Assign a name to your dashboard and click Finish.



  4. Drag your scorecard, in this example I choose an OLAP scorecard, and pull it into the Center Row.



  5. Drag your SSRS report, in this example I pull an SSRS Relational Report, into the Bottom Row.



  6. Create a Filter in the Dashboard. In this example, I build a filter off of my OLAP data source that sources my scorecard and is the same cube as my SSRS OLAP report is built from.



  7. Drag your Filter into the Top Row.



  8. Click on the Filter in the top row you will see a list of options listed under your parameter name as shown below:



  9. image



  10. Select Display and drag it to your SSRS Relational Report input area in the Bottom Row. Note, in order for this to work, your names in your SSRS Relational Report parameter, must match the captions of your members in the cube. Display specifies that we will provide the cube member name caption as the input to the report.



  11. In the Edit Filter Link dialog, select the SSRS Report parameter from the Dashboard item endpoint drop down to create the link between the PerformancePoint filter and SSRS report parameter.



  12. Click on the parameter again and select MemberUniqueName and drag it into the input area for the scorecard. We do this because our scorecard is built off of a cube data source and needs the MDX member unique names passed as inputs.



  13. In the Edit Filter Link dialog, select Filters form the Dashboard item endpoint to pass the parameter as a filter to the scorecard.



  14. Create a second page in your dashboard, also configured with 3 rows.



  15. Place your same OLAP scorecard in the Center Row, your SSRS OLAP report in the Bottom Row and the same filter in the Top Row.



  16. Click on the filter and drag the MemberUniqueName property to the input of the SSRS OLAP Report. In this case, we need the member unique name because the SSRS report is an MDX query which requires the MDX unique name as the parameter value.



  17. In the Edit Filter Link dialog, select the SSRS Report parameter from the Dashboard item endpoint drop down to create the link between the PerformancePoint filter and SSRS report parameter.



  18. Click on the parameter again and select MemberUniqueName and drag it into the input area for the scorecard.



  19. In the Edit Filter Link dialog, select Filters form the Dashboard item endpoint to pass the parameter as a filter to the scorecard.



  20. Publish your dashboard by selecting the Home tab and then selecting Publish All.


Export and view the dashboard



  1. Click the Export tab and then select Sharepoint Site from the ribbon bar.



  2. Choose the dashboard we just created and click Next.



  3. Enter the Sharepoint URL and select the Document Library you wish to publish to, click Next.



  4. Select the master page you wish to publish with and click Finish.



  5. Your exported dashboard should look as follows:



  6. image



  7. Changing the parameter will result in changes to both the scorecard values and the SSRS Report as follows:



  8. image



  9. Click on page 2 and try the same with your OLAP SSRS report and scorecard – values in both views will change.


 


Alyson Powell Erwin (alysonp@microsoft.com)


Comments (9)

  1. PerformancePoint Monitoring Filters work with SSRS reports, both relational and OLAP. To configure a

  2. Caty says:

    I want to create a dashboard with a scorecard (with KPIs), a chart and a table, where the chart and table can be actualized, according to the KPI selected in the scorecard, with relevant information about that KPI. I’ve noticed that it appears the option “Show details” when I click a KPI in a scorecard, but it is disabled. Is it possible to enable that command? Would that command be useful for the situation I described?

    If it isn’t possible, I’m thinking of using SSRS Reports, but also don’t know how to integrate them in an effective way… Could you suggest something?

  3. SteveKeefe says:

    I am trying to create SSRS reports to use in PerformamcePoint Server.

    I’m using the CTP 3 PerformancePointServer VHD. I used the GUI Configuration tool for setting up Reporting Services .

    I created a VS BI project with a project property :Target URL set to http://localhost/reportserver.

    The project builds, but Won’t deploy the Report that I created, in the Designer that

    is part of the project.

    I assume that this is the way to create an SSRS report that I can use in PerformancePoint Server.

    Anyone know of a tutorial on setting up SSRS on the PerformancePoint Server vhd?

  4. So I was going to do a post on displaying a reporting services report in a PPSM Dashboard but then realised

  5. ivonne.roche@gmail.com says:

    It is working for me except that I want to pass multiple values to the parameter on the report.

    Can you help?

  6. BPMK says:

    Hi, There is a usefull article, written by Microsoft PerformancePoint Team blog : http://blogs.msdn.com

  7. Ahmad Elayyan says:

    Hi All,

    i create a report and deploy it successfully, this report take a parameter. when i select reports from brows and select a report, error msg box appear to me [Unable to connect to server]

    actually i have another solution which is to use a web page insted of Report to display the comments, and this web page accept a parameter, but here i want to use SSRS

    any one have a same problem? anyone can help?

  8. Ahmad Elayyan says:

    to solve Parameter Issue, Just give the Parameter an available values, then all thing will be Okay.

  9. emilejacobs says:

    I want to integrate SSRS into my Dashboard in such a way that when a user right click on a Chart, he must be able to select a report from the drop-down list and the report must be opened in a new window, based on the values passed from the chart.  I have seen this in a Performance Point video clip, but I cannot seem to find any information on the web.  I might be looking for the wrong information…

    Any help will be GREATLY appreciated.