Create a Custom Table filter by using Dashboard Designer

  • Article

 

Applies to: SharePoint Server 2010 Enterprise

When you use PerformancePoint Dashboard Designer to create a dashboard filter, you can select from various filter templates that include the Custom Table filter template. You would use the Custom Table filter template in situations such as those that are described in the following list:

  • You want to create one dashboard filter that pulls in data from multiple data sources. That filter will then be connected to multiple reports and scorecards in the dashboard.

  • You want to specify the names of the items that you use in the filter, instead of using the default names that are used in the data source that you plan to use for the filter.

  • You want to create your own list of items to use in the filter and then map that list to similar kinds of items that are in a database.

When you create a dashboard filter by using the Custom Table filter template, you typically use the following process:

  1. Phase 1: Create and save a table.

  2. Phase 2: Create a tabular data source.

  3. Phase 3: Create the dashboard filter.

Phase 1: Create and save a table

During this phase, you use an application such as Microsoft Excel to construct a table. The table that you create is used as a mapping table to map database members to fully qualified names. This enables you to bring together information from different data sources in a single table, or to configure the names of the members that dashboard users will see.

Tip

In this article, we assume that you use Excel to create and configure the custom table. However, you can also use a SharePoint list or a table in Microsoft SQL Server.

To create a table to use with a Custom Table dashboard filter

  1. Using Excel, create a data table that contains the items that you want to use for the dashboard filter.

  2. Make sure to organize the data into columns, with one column for each kind of information. For example, you might have one column that contains regions/countries, and another that contains cities.

  3. Decide which columns will be used for the following purposes in the filter:

    • Members   This column contains the members that you want to include in the filter. If you are creating a hierarchical structure in the filter, the members in this column are second-level members in the hierarchy.

      During Phase 3, when you create the dashboard filter, you will select this “Members” column as the Key column in Dashboard Designer.

    • Parent members   This column contains the parent members that you want to include in the filter. These members are at a level higher than the “Members” column. If you are creating a hierarchical structure in the filter, the parent member names are the first-level members in the hierarchy. Depending on the table that you use, you might not specify a column for parent members.

      During Phase 3, when you create the dashboard filter, you will select this “Parent members” column as the Parent Key column in Dashboard Designer.

    • Display names   This column contains the member names that you want dashboard users to see in the dashboard. Depending on the table that you use, you might not specify a column for default members.

      During Phase 3, when you create the dashboard filter, you will select this “Display names” column as the Display value column.

    • Default   This column should contain only the values “True” and “False,” and it should be configured as a dimension that has no aggregation.

      During Phase 3, when you create the dashboard filter, you could use this “Default” column as the Is default column in Dashboard Designer. However, you are not required to have a “Default” column to create the filter.

  4. Save the file to your computer, and then proceed to create a tabular data source in Dashboard Designer.

Phase 2: Create a tabular data source

During this phase, you create a data connection that uses the table that you created during Phase 1.

To create a tabular data source that uses the custom table

  1. In Dashboard Designer, in the Workspace Browser, click Data Connections.

  2. On the Create tab, in the Dashboard Items group, click Data Source.

    The Select a Data Source Template dialog box opens.

  3. In the Template pane, click Import from Excel Workbook, and then click OK.

    Tip

    In this article, we assume that you used Excel to create the custom table. If you used a SharePoint list or a table in SQL Server, make sure that you select the appropriate tabular data source template.

    In the center pane, on the Editor tab, click Import.

  4. Locate the Excel workbook that you created, and then click Open.

    Tip

    If an Edit data in Excel dialog box opens, specify whether to use column headers that might be on the first row of the Excel workbook. Select or clear the Headers on First Row check box, and then click either Discard changes or Accept changes.

    The data in the Excel workbook is displayed in the center pane.

  5. In the center pane, click the Properties tab.

  6. In the Name box, type the name that you want to use for the data source.

  7. (This step is optional.) To specify a location for the data source, click the Display Folder button, and then select or create a folder.

  8. In the Workspace Browser, right-click the data source, and then click Save. Proceed to create the dashboard filter.

Phase 3: Create the dashboard filter

During this phase, you create a dashboard filter by using the Custom Table filter template in Dashboard Designer.

To create a dashboard filter that uses the Custom Table filter template

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

  2. On the Create tab, in the Dashboard Items group, click Filter.

    The Select a Filter Template dialog box opens.

  3. In the Select a Filter Template dialog box, click Custom Table, and then click OK.

    The Create a Filter wizard opens.

  4. On the Select a Data Source page, select the data source that you want to use, and then click Next.

    • Use the SharePoint Site tab to view a list of data sources that have been saved to Microsoft SharePoint Server 2010.

    • Use the Workspace tab to view a list of data sources that you have created or used.

  5. On the Preview Table page, view the information that is available in the table that you selected. Make sure to note which columns that you want to use for the dashboard filter, and then click Next.

  6. On the Choose Key Columns page, specify which columns to use as Key, Parent key, Display value, and Is default settings.

    • Use the Key list to specify the column that contains the members that you want to use in the filter. If you are creating a filter that has a hierarchical structure, the Key column represents second-level members in the filter hierarchy.

    • Depending on whether you are creating a filter that has a hierarchical structure, use the Parent Key list to specify the column that contains the members that you want to designate as first-level members in the filter hierarchy. Otherwise, set the Parent key list to (None).

    • Use the Display Value list to specify the column that contains the members that you want to designate as captions for your filter. PerformancePoint Services passes those captions to any scorecards and reports that are linked to the filter.

    • In general, set the Is default list to (None).

      If the data source contains a dimension that contains “True” and “False” values and that dimension has no aggregation, you can select that dimension in the Is default list.

      Important

      Do not use the Is default list to select a column that contains text or non-numeric values. Otherwise, an error message will occur in the dashboard.

    Click Next.

  7. In the Select Display Method page, select one of the following display types:

    • List   Select this option to display the filter as a simple list in a drop-down menu. When you use this display type, dashboard consumers click an item in the list to specify what information to display.

    • Tree   Select this option to display the filter as a list that includes an expandable tree control in a drop-down menu. When you use this display type, dashboard consumers expand the tree control, and then they select one item or a group of items to apply as a filter.

    • Multi-Select Tree   Select this option to display the filter as a list that includes an expandable tree control that has check boxes in a drop-down menu. When you use this display type, dashboard consumers expand the tree control, and then they select the check boxes for the items that they want to use as a filter.

    After you select the display type that you want to use for your filter, click Finish.

  8. In the center pane, click the Properties tab.

  9. In the Name box, type the name that you want to use for the filter.

  10. (This step is optional.) To specify a location for the filter, click the Display Folder button, and then select or create a folder.

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

After you have created your filter, you can add it to a dashboard page and then connect it to one or more dashboard items. For more information, see Connect a filter to a report or a scorecard by using Dashboard Designer.

See Also

Concepts

Overview of PerformancePoint dashboard filters and connections
Create a dashboard filter by using Dashboard Designer
Assemble dashboard pages and link items together by using Dashboard Designer