Configure an Excel Services report to receive a PerformancePoint dashboard filter

You can use PerformancePoint Dashboard Designer to create and publish dashboards that can include a variety of reports, such as Excel Services reports. You can include an Excel Services report in a PerformancePoint dashboard, and link a dashboard filter to that report. That way, dashboard consumers can focus on the information that is most relevant to them. To link a dashboard filter to an Excel Services report in a PerformancePoint dashboard, you must configure the Excel Services report to receive the filter.

When you prepare an Excel Services report to receive a PerformancePoint dashboard filter, you do so in two stages. During Stage I, you assign a name to a selected filter in an Excel 2007 workbook, and then republish the workbook to Excel Services. When you republish the workbook, the Excel filter becomes an Excel Services report parameter. During Stage II, you use Dashboard Designer to link a PerformancePoint dashboard filter to the Excel Services report parameter that you created during Stage I.

Stage I: Name a filter in the Excel 2007 workbook

In this stage, you assign a name to a selected filter in Excel 2007, and then republish the workbook to Excel Services.

Important: Before you begin, make sure that you have created an Excel Services report that contains one or more filters. (For more information, see Create an Excel Services report using Dashboard Designer.)

  1. Open the Excel 2007 workbook that you want to use for your PerformancePoint dashboard.

  2. Select a filter in the workbook, and then type a name for that filter in the name box. Make sure to select a cell that contains member selections, and not a cell that contains the name of a hierarchy. For example, if you have a Geography dimension that includes a group of regions or countries, you would select the cells that contain the regions and countries, not the cell that contains the word Geography.

    Note: The name box is located immediately above cell A1 in the workbook.

  3. Repeat for each filter that you want to link to a PerformancePoint dashboard filter.

  4. Republish the Excel 2007 workbook to Excel Services. Each filter that you selected and named in Excel 2007 becomes a report parameter in Excel Services.

    Tip: When you republish the workbook, click Excel Services Options and verify that each filter you named is listed on the Parameters tab. (For more information, see Publish a workbook to Excel Services.)

Top of Page

Stage II: Link a PerformancePoint dashboard filter to the Excel Services report

In this stage, you use Dashboard Designer to link a dashboard filter your Excel Services report.

Important: Before you begin, make sure that you have created an Excel Services report and that you have named a filter in the Excel workbook.

  1. In Dashboard Designer, click the Home tab, and then click Refresh to refresh the list of elements that are saved to Monitoring Server.

  2. In the Workspace Browser, click Reports. The center pane of the workspace displays two tabs: Server and Workspace.

    The Server tab lists all the reports that you and other dashboard authors have published to Monitoring Server. The Workspace tab lists all the reports that you have created or opened in your workspace while using Dashboard Designer.

    Double-click the Excel Services report that you want to modify to open its workspace.

  3. In the center pane of the workspace, click the Editor tab. In the Parameters section, verify that the parameter that you want to use appears in this section. If it is not listed, repeat the process described in Stage I.

  4. Do one of the following:

  5. Identify an existing dashboard filter or create a dashboard filter that uses the same data source as the Excel Services report. (For more information, see How do I create a dashboard filter?)

  6. Link the dashboard filter to the Excel Services report. (For more information, see Link an item in a scorecard to a Reporting Services report using Dashboard Designer.)

    Important: When you link a filter to a report, you use a dialog box that is called the Filter Link Editor. In the Filter Link Editor dialog box, make sure that Dashboard endpoint item box displays the name of the Excel 2007 filter that you named in Stage I. In addition, make sure that the Source value box displays the correct value. The Source Value box will vary depending on the particular data source connection that is used by the Excel Services report. For example, if the Excel Services report uses data stored in SQL Server Analysis Services, then the Source Value box must contain Member UniqueName. If the Excel Services report uses any other type of data source, then the Source Value box must contain Display Name. Monitoring Server uses the Dashboard endpoint item and Source Value boxes to run queries whenever dashboard consumers select an item in a filter that is linked to the Excel Services report.

  7. After you link the dashboard filter to the Excel Services report, preview the dashboard to make sure the filter functions correctly. (For more information, see Preview a dashboard.)

  8. Click the Home tab, and then click Publish Item to save your changes to Monitoring Server.

  9. Deploy (or redeploy) your dashboard. For more information, see Export a dashboard to a SharePoint site.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×