How to configure cascading filters in Dashboard Designer (SharePoint 2010 SP1)


In the past I posted the new SharePoint SP1 features for PerformancePoint Services which included the new cascading filters option. In this post, I’m going to show you the basic steps to achieve this goal with Dashboard Designer in SharePoint 2010 SP1.

Goal: Create a set of two or more filters where the output of one filter becomes the input of another; or more precisely, the choices made in one filter limit the choices available in another. A good example would be filters in the same hierarchy such as time or geography.

Pre-requisites: You must have created a Business Intelligence Center site in SharePoint 2010 with PerformancePoint Services features enabled and have a SSAS connection file to AdventureWorks cube.

First of all, go to your Business Intelligence Center site in SharePoint 2010 and click Create Dashboards and choose Start Using PerformancePoint Services. Then, go to the PerformancePoint Content library add a new filter. In the “Select a Filter Template” window, choose “Member Selection”.

Click Next, and choose the already created data source for SSAS cube. Next, in the “Select Members” window select the “Products.Products Categories” in “Filter Dimension” field and select children members below All Products in “Filter Members” field. For the example, choose “Internet Sales Amount” in the Filter Measure combo box. Click on Next button and choose All Categories for example. In the “Select Display Method” window choose List. Name it Product Category Filter.

Now repeat the same steps for Product Sub Category Filter and Product Filter.

NOTE: Select only the children members bellow each option in order to design a complete tree options for each tree level. Name the filters Product Subcategory and Product Names respectively.

To show the result of our design let’s create a new PerformancePoint dashboard and add the previous filters to the recently created page. Add a new dashboard to workspace with two zones and name it Product Sales such as the main page for the dashboard. Add the three filters to the dashboard content area.

Now the important part: Connect all filters using their hierarchy. Drag and drop the Member Unique Name from the “Product Category Filter” filter onto the “Product SubCategory Filter” filter. If you prefer, use Create Connection for the “Product Category Filter” filter (from the Ribbon's Edit section or its triangle menu) to send values to the “Product SubCategory Filter” filter, with a source value of "Member Unique Name".

Connect the "Product SubCategory Filter" to the “Product Filter” filter, using Member Unique Name. Connect the “Product Filter” filter to any desired reports and scorecards.

Deploy the dashboard, and the whole system will now be ready for use.

With the SP1 upgrade you now have the ability to add the Apply Filters button to a dashboard from within Dashboard Designer which ties in very closely well with Cascading Filters.

What really makes the Apply Filters button a good addition to Cascading Filters is that it lets you settle all the filter choices before the scorecards and reports "repaint" event, so you aren't waiting for the scorecards and reports during every adjustment of the filters. When we change the “Product Category Filter” filter to Clothing, the “Product SubCategory Filter” filter adjusts to the new upstream values and the Apply Filters button turns on, but the report doesn't change at all. Clicking the Apply Filters button makes the report update to the new value and the button go back to its inactive state.

Hope you find this post useful.

See you next time.

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.