Export (0) Print
Expand All

Connect a PerformancePoint scorecard item to an Excel Services report

SharePoint 2010

Published: August 23, 2011

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

In this article:

Begin with a dashboard that contains a scorecard and an Excel Services report

Make sure that the dashboard page contains a scorecard and an Excel Services report that you want to connect to the scorecard. The Excel 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 an Excel Services report that contains at least one parameter. For more information, see Create an Excel Services report by using Dashboard Designer.

  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 Excel 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 an Excel Services report.

Connect a KPI to an Excel Services report

When you create a connection between a KPI and an Excel 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 Excel 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 query that the Excel Services report recognizes as an input for one or more parameters.

    note Note:

    If no custom properties are listed, or you cannot find a custom property that contains an appropriate value, 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 Excel 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 Excel Services report will recognize as a valid input for its parameter. The value that you specify can be a query that corresponds to a dimension hierarchy or member.

    For example, suppose that a scorecard contains inventory control KPIs to display performance information for inventory control in various regions or countries. Suppose that the Excel Services report uses SQL Server Analysis Services data, and that the report displays detailed information about product inventory across different product categories. Suppose also that the Excel Services report contains a Geography parameter. In this case, you could use a multidimensional expressions (MDX) query to specify a region or country as input for that parameter in the Excel Services report. Then, when you connect the custom property to the Excel Services report, the scorecard sends geographical information to the report so that the report displays information for only the region or country that a dashboard user has clicked.

  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 Excel 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 Excel 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, use the Connect to list to select the name of the parameter that was defined in the Excel Services file, 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 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 Excel 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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft