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: https://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)