Connect a PerformancePoint scorecard item to a SQL Server Reporting Services report

  • Article

 

Applies to: SharePoint Server 2010 Enterprise

By using PerformancePoint Dashboard Designer, you can connect a scorecard key performance indicator (KPI) to a Microsoft SQL Server Reporting Services (SSRS) report. When you do this, you can use the KPI as a filter for the SQL Server Reporting Services report. That way, when users click the KPI, the Reporting Services report updates to display additional information about that KPI.

In this article:

  • Begin with a dashboard that contains a scorecard and a Reporting Services report

  • Connect a KPI to a Reporting Services report

Begin with a dashboard that contains a scorecard and a Reporting Services report

Make sure that the dashboard page contains a scorecard and a Reporting Services report that you want to connect to the scorecard. The Reporting Services report must contain at least one parameter.

If you do not have such a dashboard, you can create one. To create a basic dashboard, follow these steps.

To create a basic dashboard

  1. Create a scorecard that contains one or more KPIs. For more information, see Create a scorecard by using Dashboard Designer.

  2. Create a Reporting Services report that contains at least one parameter. For more information, see Create a Reporting Services report by using Dashboard Designer.

    Tip

    Make a note of the parameters that are defined for the report. You can view this information in Dashboard Designer by clicking the Editor tab for the Reporting Services report.

  3. Create a dashboard page. Consider using a two-column page layout template to display the scorecard and the report next to one another. For more information, see Create a dashboard page by using Dashboard Designer.

  4. Add the scorecard and the Reporting Services report to the dashboard page that you created in step 3. For more information, see Add dashboard items to a page by using Dashboard Designer.

  5. Keep the dashboard open for editing, and proceed to Connect a KPI to a Reporting Services report.

Connect a KPI to a Reporting Services report

When you create a connection between a KPI and a Reporting Services report, you configure the connection by using a custom property that is defined for the KPI. The first step is to identify or define a custom property for each KPI that you want to connect to the report.

To verify that a custom property was defined for a KPI

  1. Begin with a dashboard page that is open for editing in Dashboard Designer. Click the Editor tab for the dashboard page that you want to change.

  2. Locate the scorecard that you want to connect to the Reporting Services report. In the Workspace Browser, double-click the name of the scorecard to open it for editing.

  3. Locate the KPI that you want to connect to the report. In the Workspace Browser, double-click the name of that KPI to open it in the center pane. Then click the Properties tab.

  4. In the Custom Properties section, view the custom properties that are defined for the KPI. Verify that at least one custom property contains a value such as a key, unique name, or caption that the Reporting Services report recognizes as an input for one or more parameters.

    Note

    If no custom properties are listed, or you cannot find a custom property that contains an appropriate value, then you must define one for the KPI.
    See the following procedure for information about how to define a custom property.

To define a custom property for a KPI

  1. Begin with a dashboard page that is open for editing in Dashboard Designer. Click the Editor tab for the dashboard page that you want to change.

  2. Locate the scorecard that you want to connect to the Reporting Services report. In the Workspace Browser, double-click the name of the scorecard to open it for editing.

  3. Locate the KPI that you want to connect to the report. In the Workspace Browser, double-click the name of that KPI to open it in the center pane. Then click the Properties tab.

  4. In the Custom Properties section, click New Property.

    The Property Type Selector dialog box appears.

  5. Select Text for the property type, and then click OK.

    The new property is listed in the Custom Properties section.

  6. In the Value column, specify a value that the Reporting Services report will recognize as a valid input for its parameter. The value that you specify can be a query that corresponds to a key, unique name, or caption.

    For example, suppose that a scorecard contains inventory control KPIs to display performance information for inventory control in various countries. Suppose that the Reporting Services report uses SQL Server Analysis Services data, and that the report displays detailed information about product inventory for those same countries. In this case, you could use a multidimensional expressions (MDX) query that resembles [Product].[Product].DEFAULTMEMBER. Then when you connect the custom property to the Reporting Services report, the scorecard will send product information to the report so that the report displays information for only the country that a dashboard user has clicked.

    As another example, suppose that the Reporting Services report is an interactive map that displays sales information for various regions or countries. Suppose the report contains a Geography parameter. In this case, you can specify a value or a query that corresponds to a particular region or country.

  7. In the Custom Properties section, in the Name column, select the default text and type the name that you want to use for the custom property.

  8. (This step is optional.) In the Description column, type a brief description for the custom property.

  9. In the Workspace Browser, right-click the KPI, and then click Save.

Now that each KPI that you want to connect to the Reporting Services report contains at least one appropriate custom property, the next step is to configure the connection between each KPI and the report. You use the custom property to create and configure the connection.

To create a connection by using a KPI’s custom property

  1. Begin with a dashboard page that is open for editing in Dashboard Designer. Click the Editor tab for the dashboard page that you want to change.

  2. Locate the scorecard that you want to connect to the Reporting Services report. Under Available Fields, in the KPI section, click the custom property that you want to use and drag it to the Drop fields to create connections box for the report.

    When you release the mouse button, the Connection dialog box appears.

  3. In the Connection dialog box, notice that the Connect to list and the Source value list are already populated. These lists contain the recommended solutions for the connection to work. Keep these default settings, and then click OK.

  4. (This step is optional.) To apply a formula that additionally filters the content, click the Connection Formula button. The Connection Formula dialog box appears.

    In the Connection Formula dialog box, type a formula that will be used to filter the data before it reaches the report. The formula that you specify can include a Time Intelligence statement or an MDX query.

    Tip

    For more information about the kinds of formulas that you can use, see Use the Connection Formula dialog box in Dashboard Designer to configure a filter connection.

    After you have specified a formula, click OK to close the Connection Formula dialog box.

  5. Click OK to close the Connection dialog box. In the center pane of the workspace, in the Connections box for the Reporting Services report, you can see the name of the scorecard that you have connected to it.

  6. In the Workspace Browser, right-click the dashboard, and then click Save.

To view and test the connection, publish the dashboard to SharePoint Server. For more information, see Deploy a PerformancePoint dashboard.

See Also

Concepts

Assemble dashboard pages and link items together by using Dashboard Designer
Plan, design, and implement a PerformancePoint dashboard
Create advanced PerformancePoint dashboards