Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Create a Member Selection Dashboard Filter in Performance Point Server 2007


By:   |   Last Updated: 2008-04-03   |   Comments (4)   |   Related Tips: > 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

  • Take a look at Performance Point Server.  It is a new tool in the Microsoft BI stack that will help you to provide the kind of Business Intelligence applications that users want.  For a great starting point and to download an evaluation copy visit this site.
  • Download a copy of the sample Dashboard Designer workspace here and experiment with the Dashboard Designer.  You can get the Adventure Works BI database used in the sample here (click on AdventureWorksBICI.msi).


Last Updated: 2008-04-03


next webcast button


next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, March 20, 2012 - 3:15:54 AM - Kranthi kumar G Back To Top

Hi Ray,   Thanks a lot. Its really helpful.....


Monday, March 19, 2012 - 11:48:22 AM - Ray Barley Back To Top

I think you can find the answer here: http://blogs.msdn.com/b/performancepoint/archive/2008/02/12/always-display-default-filter-selection-in-dashboards.aspx


Monday, March 19, 2012 - 1:11:36 AM - Kranthi kumar G Back To Top

Hi, The Default selection works for the first time we load the page, but when we select any other member and closes the Dashboard and for the next time if we load the same Dashboard we are not getting the default selection, just we are getting the member that we selected previously. please tell me how to refresh the old selection get the default value.

 

Thanks,

Kranthi kumar G.


Friday, December 26, 2008 - 4:15:23 PM - RichardS Back To Top

Thanks for this article.

Do you know if PPS has a way to create bookmarks for filters?  That is, a way for a user to save filter settings for use in another session or for other users? Typically the user would be asked for a name for the settings.

In a related questions, can filter settings be shared accross multiple dashboards?

 

Thanks

Richard

 


Learn more about SQL Server tools