Create a Member Selection Dashboard Filter in Performance Point Server 2007

By: Ray Barley   |   Comments (4)   |   Related: > Performance Point

Problem
We have heard that you can create filters in a Performance Point dashboard then apply them to one or more reports and scorecards.  Can you provide some step-by-step instruction on how to do this?

Solution
The PPS product is a recent addition to the Microsoft Business Intelligence (BI) offering.  PPS enables users to develop a variety of reports and scorecards, then compose them into a dashboard which is deployed to SharePoint.  Business users can then simply navigate to a URL in a SharePoint site to access the dashboard.

A dashboard filter is kind of like a WHERE clause in a SELECT statement but much more powerful.  As you mention the filter can be applied to one or more report or scorecard elements on the dashboard.  There are several different kinds of dashboard filters available; let's walk through setting up the most popular one - a Member Selection filter.

For this tip we will begin with the sample workspace from our earlier tip Creating a Scorecard in Performance Point Server 2007 (PPS) already open in the Dashboard Designer.  To deploy / launch the Dashboard Designer, create a Data Source, and create an Analytic Grid report, please refer to our earlier tip, Creating an Analytic Grid Report in Performance Point Server 2007 (PPS). 

Creating a Member Selection Dashboard Filter

After launching the Dashboard Designer and opening the sample workspace file, click Publish All on the Home tab then double click on the Sample Dashboard in the Workspace Browser area to begin editing the dashboard.  Filters are created by adding them to an existing dashboard.  After the filter is created, however, it can be added to other dashboards.

Click the Filters tab in the center area of the Dashboard Designer then click the New Filter button.

There are several templates available for creating dashboard filters.  MDX Query allows you to specify custom MDX code to perform filtering.  Member Selection allows you to choose a dimension then specify the members in that dimension to appear in a choice list.  A Named Set allows you to use a Named Set defined in your SQL Server Analysis Services cube as a filter.  Tabular Values allow you to use Excel, SQL Server, SharePoint Lists, etc. as a filter.  Time Intelligence allows you to filter based on members in the Time dimension of your cube.  Click Member Selection.

Provide a name for the filter.

Select AdventureWorks as the Data Source. 

The Select Members dialog allows you to choose the dimension hierarchy, the dimension members that will appear in the member selection list, and the default member selection.  Click the Select Dimension button.

Select the Geography hierarchy of the Geography dimension from the Select Dimension popup dialog.

Click the Select Members button then select individual members from the dimension hierarchy.  Right click on a member to set it as the default.  You can only set the default on the dialog below.

Choose the display method for the filter from the available options.  Choose the Multi-Select Tree which allows you to filter on one or more dimension members.

After completing the filter you are returned to the Editor tab of the Dashboard Designer for the Sample Dashboard.  Drag the new filter from the Available Items in the Detail Pane and drop it in the Header area as shown below (Add a dashboard item to this zone by dropping it here).

The filter is now displayed in the Header area.  At this point the filter is not yet connected to any of the other elements on the dashboard.  Hovering the mouse cursor over the Geography Filter will display a popup menu; drag Member UniqueName from the menu and drop it onto the scorecard in the rectangle titled Drop fields here to create links.

After dropping Member UniqueName onto the scorecard, the Edit Filter Link dialog is displayed.  As a result of the drag and drop operation, the Link Items tab is already completed and cannot be changed.  The Link Options tab allows you to specify how to apply the filter.  The Dashboard item endpoint can be Filters, Rows or Columns.  Filters applies the filter in the background; rows or columns actually adds the dimension members to the rows or columns of the report or scorecard.  Select Columns.  The Source value is set based on dragging Member UniqueName from the popup menu; leave that as the Source value setting.  When the data source is an Analysis Services cube,  you will use Member UniqueName as the Source value.

Repeat the drag and drop operation on the Geography Filter and add the filter to the Sample Analytic Grid Report.  Accept the defaults in the Edit Filter Link dialog.

Publish the dashboard.  To view the dashboard, click the Edit tab, click Preview, then select the Sample Dashboard.  Select Australia and the United States as the Geography filter; the report and scorecard will be displayed as shown below, with the countries selected in the filter shown on the columns.

As an alternative you can set the filter for the Analytic Grid report in the background; i.e. not shown on the rows or columns.  When the user changes the filter selection, the report will refresh showing the result of applying the filter.  To do this you remove the filter from the report (right click on the filter then select Delete), go back to the report designer, then drag / drop the Geography dimension hierarchy onto the Background area of the report. 

Publish the report then drag and drop the Geography filter onto the report.  The Edit Filter Link dialog will be displayed with Geography selected as the Dashboard item endpoint.  Accept the default.

Publish the dashboard then click the Edit tab, click Preview, then select the Sample Dashboard.  As you change the filter settings the report will be refreshed and the new filter settings will be applied.  However, you will no longer see the Geography dimension members on the columns of the report.  The filtering is done in the background.

Next Steps

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips