Configure cascading filters by using Dashboard Designer (SharePoint Server 2010 SP1)

  • Article

 

Applies to: SharePoint Server 2010 Enterprise

Important

The ability to create cascading filters as described in this article is available only in Microsoft SharePoint Server 2010 with Service Pack 1 (SP1). If you are using an earlier version of PerformancePoint Services, you will be unable to perform the procedures that are described in this article.

You can use PerformancePoint Dashboard Designer to create various dashboard filters that you connect to reports and scorecards. When a dashboard filter contains lots of items, dashboard users must spend time scrolling through a list or an expandable tree to select the items they want to use to filter dashboard content. To help eliminate this, you can configure dashboard filters in such a way that one is connected to another. This creates cascading filters. Cascading filters work so that as a dashboard user selects an item in one filter, the list of items in the next is scoped according to what was selected in the first. By including cascading filters in your dashboards, dashboard users can pinpoint the information that they need more efficiently.

For example, suppose that you have created a dashboard to show product sales information for North America. Dashboard users want to view information for specific cities. Rather than create a single filter that contains a long list of states or provinces and cities, you can configure cascading filters. You can create one Country filter that lists Canada and North America. You can then create a States or Provinces filter that contains a list of all the states and provinces in North America, and a Cities filter that contains a list of all the cities. You can connect the Country filter to the States filter and then connect the States filter to the Cities filter. After you publish the dashboard, dashboard users can view information for specific cities by first selecting a Canada or United States by using the Country filter, which narrows the list of items in the States or Provinces filter. Dashboard users can then select a state or province, which narrows the list of items in the Cities filter. Finally, dashboard users can select an item in the Cities filter to view information for a specific city.

When you configure cascading filters for a dashboard, you can add an Apply Filters button. This enables dashboard users to make their filter selections and then apply them all at the same time.

To add cascading filters to a PerformancePoint dashboard, you follow this process:

  1. Create filters to use as cascading filters

  2. Add cascading filters to a dashboard page

Create filters to use as cascading filters

During this phase, you create two or more dashboard filters to use as cascading filters. The dashboard filters must use data that is stored in SQL Server Analysis Services or PowerPivot and should use the same measure.

You can use any of the following filter templates to create your dashboard filters:

Use any of the following procedures in this section to create the dashboard filters.

To create a Member Selection filter

  1. In Dashboard Designer, in the Workspace Browser, click PerformancePoint Content.

  2. In the ribbon, on the Create tab, in the Dashboard Items group, click Filter.

    The Select a Filter Template dialog box opens.

  3. Click Member Selection, and then click OK.

    The Select a data source page opens.

  4. Use the SharePoint Site and Workspace tabs to select the Analysis Services data source that you want to use for the filter, and then click Next.

    The Select Members page opens.

  5. In the Filter Dimension section, click Select Dimension.

    The Select Dimension dialog box opens.

  6. Select the dimension hierarchy that you want to use for the filter, and then click OK.

  7. On the Select Members page, click Select Members.

  8. Select the members that you want to use for the filter. To specify a default filter selection, right-click an item, and then click Set as Default Selection. Then click OK.

  9. Use the Filter measure list to specify a measure to use for this filter, and then click Next.

    Tip

    Depending on the cascading filters that you want to create, you should select the same measure for all the filters. For example, suppose that you are creating cascading filters to show product sales across different geographical regions. You create several filters based on different hierarchies in the same Geography dimension. In this case, you would select a sales amount measure for all the geography filters.

  10. On the Select Display Method page, click a display type, and then click Finish.

    Tip

    As a best practice, we recommend that you select the List display type.

  11. In the Workspace Browser, notice that the default text for the new filter is highlighted. Type a name for the filter, and then press the Enter key.

  12. In the Workspace Browser, right-click the filter, and then click Save.

  13. Take one of the following steps:

    • Repeat this procedure to create another Member Selection filter.

    • Create another filter by following one of the other procedures in this section.

    • Proceed to Add cascading filters to a dashboard page.

To create a Named Set filter

  1. In Dashboard Designer, in the Workspace Browser, click PerformancePoint Content.

  2. In the ribbon, on the Create tab, in the Dashboard Items group, click Filter.

    The Select a Filter Template dialog box opens.

  3. Click Named Set, and then click OK.

  4. Use the SharePoint Site and Workspace tabs to select the Analysis Services data source that you want to use for the filter, and then click Next.

  5. Use the Select named set list to select a named set that is defined in Analysis Services.

    The MDX expression for the named set is displayed in the Named set expression box.

  6. Use the Filter measure list to specify a measure to use for this filter, and then click Next.

    Tip

    Depending on the cascading filters that you want to create, you should select the same measure for all the filters. For example, suppose that you are creating cascading filters to show product sales across different geographical regions. You create several filters based on different hierarchies in the same Geography dimension. In this case, you would select a sales amount measure for all the geography filters.

  7. On the Select Display Method page, click a display type, and then click Finish.

    Tip

    As a best practice, we recommend that you select the List display type.

  8. In the Workspace Browser, notice that the default text for the new filter is highlighted. Type a name for the filter, and then press the Enter key.

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

  10. Take one of the following steps:

    • Repeat this procedure to create another Named Set filter.

    • Create another filter by following one of the other procedures in this section.

    • Proceed to Add cascading filters to a dashboard page.

To create an MDX Query filter

  1. In Dashboard Designer, in the Workspace Browser, click PerformancePoint Content.

  2. In the ribbon, on the Create tab, in the Dashboard Items group, click Filter.

    The Select a Filter Template dialog box opens.

  3. Click MDX Query, and then click OK.

  4. Use the SharePoint Site and Workspace tabs to select the Analysis Services data source that you want to use for the filter, and then click Next.

  5. In the Enter MDX Formula box, specify the multidimensional expressions (MDX) query that you want to use.

  6. Use the Filter measure list to specify a measure to use for this filter, and then click Next.

    Tip

    Depending on the cascading filters that you want to create, you should select the same measure for all the filters. For example, suppose that you are creating cascading filters to show product sales across different geographical regions. You create several filters based on different hierarchies in the same Geography dimension. In this case, you would select a sales amount measure for all the geography filters.

  7. On the Select Display Method page, click a display type, and then click Finish.

    Tip

    As a best practice, we recommend that you select the List display type.

  8. In the Workspace Browser, notice that the default text for the new filter is highlighted. Type a name for the filter, and then press the Enter key.

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

  10. Take one of the following steps:

    • Repeat this procedure to create another MDX Query filter.

    • Create another filter by following one of the other procedures in this section.

    • Proceed to Add cascading filters to a dashboard page.

Add cascading filters to a dashboard page

When you are ready to add cascading filters to a dashboard, you start by creating a new dashboard page. You can either create a new dashboard or open an existing dashboard for editing and then add a new page to it.

To create a new dashboard

  1. In Dashboard Designer, in the Workspace Browser, click PerformancePoint Content.

  2. In the ribbon, on the Create tab, in the Dashboard Items group, click Dashboard.

    The Select a Dashboard Page Template dialog box opens.

  3. Select a template, and then click OK.

    Tip

    We recommend that you use a page layout template that enables you to put the cascading filters in a single dashboard zone. An example of such a template is the Header, 2 Columns template.
    You can also edit any page template by adding or removing dashboard zones. For more information, see Configure specific sizes of individual PerformancePoint dashboard zones and/or items with zones.

    The new dashboard opens for editing.

  4. In the Workspace Browser, notice that the default text for the new dashboard is highlighted. Type a name for the dashboard, and then press the Enter key.

  5. Keep the dashboard open, and proceed to add cascading filters and reports to the page.

To add a new page to an existing dashboard

  1. In Dashboard Designer, in the Workspace Browser, click PerformancePoint Content.

    The center pane displays two tabs: SharePoint and Workspace.

  2. Use the SharePoint and Workspace tabs to select the dashboard that you want to edit, and double-click the dashboard to open it for editing.

  3. In the center pane, on the Editor tab, in the Pages section, click New Page.

    The Select a Dashboard Page Template dialog box opens.

  4. Select a template, and then click OK.

    Tip

    We recommend that you use a page layout template that enables you to put the cascading filters in a single dashboard zone. An example of such a template is the Header, 2 Columns template.
    You can also edit any page template by adding or removing dashboard zones. For more information, see Configure specific sizes of individual PerformancePoint dashboard zones and/or items with zones.

    The new dashboard page opens for editing.

  5. In the Pages section, in the Name column, select the default text for the dashboard page and type a new name. Then press the Enter key.

  6. Keep the dashboard open, and proceed to add cascading filters and reports to the page.

Now that you have set up the dashboard page, the next step is to add cascading filters and reports to the page.

To add cascading filters and reports to a dashboard page

  1. Begin with a blank dashboard page open for editing. In the ribbon, click the Edit tab.

  2. Identify the dashboard zone where you want to put the cascading filters. Click that zone, and then, on the Edit tab, in the Layout group, click Zone Settings.

    The Zone Settings dialog box opens.

  3. On the Orientation tab, select Vertical, and then click OK.

    Note

    We configure the zone that will contain the cascading filters to use a vertical orientation as a best practice. This helps make it easier for dashboard users to view and use the cascading filters.

  4. In the Details pane, expand Filters, and then expand PerformancePoint Content.

  5. From the Details pane, drag a cascading filter to the dashboard zone. Repeat for each cascading filter. Make sure to arrange them in the correct order in the zone.

  6. (This is optional.) From the Details pane, drag Apply Filters Button to the same zone where the cascading filters are positioned. Make sure to put it at the bottom of the list in the zone.

    Note

    As a best practice, we recommend that you add the Apply Filters button when you add cascading filters to a dashboard. This enables dashboard users to make filter selections and then apply those selections all at the same time. In addition, reports and scorecards do not refresh until a dashboard user clicks the Apply Filters button.

  7. In the Details pane, expand Scorecards (or Reports), and then expand PerformancePoint Content.

  8. From the Details pane, drag a scorecard (or a report) to a zone in the dashboard.

  9. Repeat Steps 7-8 until you have added all the report types that you want to use in the dashboard.

  10. In the Workspace Browser, right-click the dashboard, and then click Save to save your changes. Do not close the dashboard.

Now that you have added dashboard items to the page, the next step is to connect the cascading filters.

To connect cascading filters

  1. Begin with the dashboard page open for editing. Locate the top-level cascading filter.

  2. Click the down arrow for a cascading filter, and then click Create Connection.

    The Connection dialog box opens.

  3. On the Items tab, use the Send values to list to select the next level cascading filter.

  4. On the Values tab, use the Source value list to select an item, such as Member Unique Name. Then click OK to close the Connection dialog box.

  5. Locate the next-level cascading filter, and repeat Steps 2-4 for that filter.

  6. Repeat Step 5 until you reach the lowest-level cascading filter.

  7. Locate the lowest-level cascading filter. Click the down arrow for that filter, and then click Create Connection.

    The Connection dialog box opens.

  8. On the Items tab, use the Send values to list to select a report or a scorecard to which you want to connect the filter.

  9. On the Values tab, select a combination of values by using the Connect to list and the Source value list.

    Depending on how a report or scorecard is configured, the list of options that are available in the Connect to and Source value lists will vary. By using these lists, you can choose from many combinations. For example, you can select a page, row, or column in one list, and Display Value or Member Unique Name in the other list.

    Although it might seem as if you can select any combination of values in the Connect to and Source value lists, not all combinations will work correctly. You will not see whether a filter connection works until you publish the dashboard to a SharePoint site, such as a designated location where you can preview and test the dashboard.

    Some examples of combinations that work are listed in the following table.

    Connect to list item Source value list item Description

    Measures

    Member Unique Name

    This configuration causes the filter to display information in a report or a scorecard for the measure that is used by the filter.

    Dimension Hierarchies on Rows or Columns

    Member Unique Name

    This configuration causes the filter to replace rows or columns in a report or scorecard to be replaced with the item or items that dashboard consumers select by using the filter.

    Axis, Page, Row, Column, or Row (or Column) Hierarchies

    Display Value

    This configuration causes member captions from a report or scorecard’s underlying database to be displayed in the report or scorecard when the filter is applied. (Member captions resemble labels like "Baseball Bats" or "Baseball Gloves.")

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

    In the Connection Formula dialog box, type a formula that will be applied to filter the data before it reaches the report or scorecard. Depending on the filter that you are connecting, the formula that you specify can include a Time Intelligence statement or an MDX query. For example, if you are connecting a Time Intelligence Connection Formula filter to a report or a scorecard, you can only specify a Time Intelligence statement.

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

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

  12. Repeat Steps 7-11 for each item that you want to connect to the cascading filters.

  13. In the Workspace Browser, right-click the dashboard, and then click Save. Deploy the dashboard to view and test the cascading filters.

    Important

    When you connect a filter to another filter, a report, or a scorecard, you can create an invalid filter connection that will only exhibit problems when you view the dashboard in its SharePoint site. When an invalid filter connection is made, a report or scorecard displays unexpected content or an error message. If this occurs, reconfigure the filter connection.

See Also

Concepts

Create a dashboard filter by using Dashboard Designer
Overview of PerformancePoint dashboard filters and connections
Plan, design, and implement a PerformancePoint dashboard