Cascading Filters in PerformancePoint Services

One long-awaited and much-requested feature for PerformancePoint, Cascading Filters, is available in SharePoint Server 2010 SP1. I wanted to go through the deployment of a dashboard using Cascading Filters to show both the ease and a little of the power this feature provides.

The concept of cascading filters is that 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. Typically, this would be filters in the same hierarchy: time, geography, or organization, for example.

Before showing how to set up a dashboard with cascading filters, I'd like to show you a working example, focusing on just the filters.

In this example, using the Contoso Retail DW database's Sales cube, we have a geography-based (country, state/province, and city) filter cascade. With everything in place, the filter zone of the dashboard looks like this.

Changing the “States or Provinces” filter selection to another state makes the choices for the Cities filter change.

Suppose we change the “North America” filter choice.

If we pull down the "States or Provinces" filter, notice that it shows only the Canadian Provinces.

If we disconnect the "North America" filter from the "States or Provinces" filter and re-deploy, notice how the list grows.

By "uncascading" the filters, we see the benefit we were getting with cascading filters – refining the choices in one filter based on choices made in another filter.

Let's look at how this dashboard was made, and add in a report to better visualize the filter cascade's output.

After firing up Dashboard Designer, we create the data connection. The above shows the dialog that will supply the data connection to the Analysis Services server for this example. (If you're following along creating this content, your Server will be different.) Once the data connection is saved, we can create the filters.

With the selection in the PerformancePoint Content section of the Workspace Browser, tell Dashboard Designer to create a filter.

Let's use the Member Selection filter template and, of course, the data connection we just created.

We want filters based on geography. The “Sales Territory” has several levels of geographic data. For this example, let’s just use the “Sales Territory.Territory Hierarchy”, which contains all of the geographies represented, but focus on the country level and slice off just the countries represented in its “North America” section. First, the Filter dimension:

Then, pick the members of interest.

Set the display method. The simplest type is the list.

We can name the filter “North America”.

The next level of interest in the Sales Territory hierarchy is the state/province. Repeat the above process to create a filter for it from the dimension “Sales Territory.Territory Hierarchy” dimension, but this time, select only the children of Canada and United States.

Make this a List type filter, too, and name it "States or Provinces".

The final filter to be created is Cities, and will need the children of each Canadian Province and United States State.

Hint: A Select Grandchildren would come in handy. There is a shortcut you could take here: Use “Sales Territory.Sales Territory Name”, which corresponds closely to the city level in the Hierarchy, as a dimension and select all the items The cascading functionality will pick the children of the upstream filter correctly.

Now, create a dashboard, one with several zones, and a scorecard and/or a report. Drag the filters and the reports and scorecards onto the dashboard. Try to arrange the filters in a natural flow from the highest level to the lowest for the best experience for the dashboard user.

Now, the magic. To connect the “North America” filter to the “States or Provinces” filter, either

· Drag and drop the Member Unique Name from the “North America” filter onto the “States or Provinces” filter

--or--

· Use Create Connection for the “North America” filter (from the Ribbon's Edit section or its triangle menu) to send values to the “States or Provinces” filter, with a source value of "Member Unique Name".

Similarly, connect the "States or Provinces" to the Cities filter, using Member Unique Name.

Connect the Cities filter to any desired reports and scorecards. Deploy the dashboard, and the whole system will be ready for use.

The Apply Filters Button, or Paint it Once

One seemingly minor part of the SP1 upgrade—the ability to add the Apply Filters button to a dashboard from within Dashboard Designer—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", so you aren't waiting for the scorecards and reports during every adjustment of the filters.

Adding the button couldn't be simpler. It's sitting at the top of the list of filters in the Dashboard Designer's dashboard editor's Details pane.

Select it and use the Add To Zone control or just drag it onto the dashboard.

Now, when the user of the deployed dashboard changes a filter setting, other filters that are "downstream in the cascade" (dependent on that filter) will update, but reports and scorecards won't, letting the user find the exact filter settings desired, at which point the user can click the button and get all the filter values applied at once.

Here, for example, is the dashboard we've been building with the filters all applied. The Apply Filters button is inactive, because there are no pending filter changes.

Now, change the “States or Provinces” filter to Ontario. The Cities filter adjusts to the new upstream values (I opened it to show the new choices available) 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.

So, that's one path through the wonderful land of Cascading Filters! Appropriate deployment of Cascading filters will improve the efficiency and reduce frustration and wait times for the users of your dashboards. If you would like more information, be sure to visit this TechNet article.

It appears that this only allows for a single filter connection. It would be very nice to be able to have multiple connections between filters based on how the user might make selections from them. So having multiple connections just like reports would be feature request unless I am missing something here.

Anand, I thought I had replied to you the day you asked them, and then noticed that the reply hadn't made it onto the blog comments, My apologies for the delay, and I hope your customers have been patient.

I would check the prerequisites first. Most importantly, you must be using Office 2010, Services Pack 1 (SP1). The feature doesn't exist in 2010 before SP1 or 2007. Second, you must be using an Analysis Services or PowerPivot data source for both filters, and the types of the filters must be Member Selection, Named Set, or MDX Query.

It sounds like you're following along with my example, so the data source and filter types are probably correct. Please try dragging and dropping the "from" filter onto the "to" filter in the dashboard editor. If you have the wrong version of PerformancePoint Services or the filter or datasource types are wrong, the cursor will show as an "x". If all the prerequisites are met, you should get a connection dialog with some of the values already filled in.

If your site was upgraded from 2010 RTM so 2010 SP1, and the datasource you are using was already on the server, you may want to try creating another source, in case the upgrade process did not succeed for that datasource. I have no reason to think it would fail, but I want to eliminate any of the variables.

The URL referenced at the end of the post has more detailed information about the cascading filters facility. You may want to look over it if you haven't yet.

Anything on my comment about multiple connections between filters? Only allowing a single filter connection will not work in lots of scenarios. Is this being looked at for an enhancement at all? denglishbi@gmail.com

Hi, is there a way to get this cascading filtering working with Performance Point Filters which are not based on a analysis services source in the background? We have some filters based on tables in a relational database and we would like to have a way to cascade select the values in filters? Any workarounds to get it accomplished.

Hi, have a requirement from my client for cascading the filters in PPS Dashboard ,but the cascading was not happening properly i.e. instead of one value im getting two values though the second doesnot have any value (null ) .Can any one please suggest how to overcome that issue