Using Performance Points scorecards as filters to solve cascading dropdown and row count issues

Performance Point Filters and scorecards

One of the criticisms often levelled at Performance is the lack of cascading dropdowns. Not when selecting from a single hierarchy (which PPS does quite well, up to a soft limit of 5000 members), but when attempting to cross filter.

For instance, in our example from the Adventure Works database, we may want to initially select bike racks from the product subcategory hierarchy of the Product dimension, and then only have 2007 and 2008 years of the date dimension available as bike racks were not sold in 2005 and 2006. In order to do this, we of course need to filter by a measure, in this case Sales Amount works nicely.

NB: Using the native count from a dimension may yield better performance. It’s not surfaced in this measure group from adventure works.

This is not possible using standard PPS filters, however it *is* possible using PPS scorecards as a filter type.

To demonstrate this functionality, we need to create three items:

1.       Product Categories filter

2.       Years scorecard

3.       Analytic Chart to test functionality

1: Product Categories filter

Having created a data connection to the cube, create a new Member Selection filter, and use the Product Categories multi-level hierarchy, as in Figure 1 below. I used a Multi-select tree as the type.

2. Years Scorecard

Where the magic happens J

Create a new blank KPI, delete the target, rename the actual to “Year” and point the actual to Sales Amount, as shown in Figure 2 below.

 Create a new blank scorecard, and add the KPI to the left hand side. Drag the Calendar.Calendar hierarchy as the last child of the KPI, and autoselect the children of the all (this will allow for drilldown from years to dates)

Now, right click on the “Year” metric column, and Hide it.

Right click on the “Sales Amount” row and Hide it.

Finally, right click on any of the cells, choose View Settings, select the Filter tab, and tick the Filter empty rows checkbox, as in Figure 3 below

And really, at this point your scorecard as a filter is set up.

3: Analytic Chart to test the scorecard

Now that we’ve set up the filters, let’s set up the test chart.

Create a new analytic chart, and add Calendar.Calendar to the bottom axis, Internet Sales Amount to the series, and Product.Product Categories to the background. Select children of All Periods for the calendar hierarchy. (This last isn’t strictly necessary, but renders the chart better when no filter is selected)

Your chart should look like Figure 4 below:

OK, so final step is pulling this all together in a dashboard. Create a new dashboard with a header and 2 columns. Add the filter to the top zone, the scorecard to the left zone, and the report to the right zone.

Connect the filter to the scorecard : Connect to “Page” and use “Member Unique Name for the source value.

Connect the filter to the report : Connect to “Product Categories” and use “Member Unique Name for the source value.

And the magic part: Connect the scorecard to the report. Do this either by dragging the “Row Member à Member Unique Name” from the scorecard to the report, or by clicking on the Report and clicking Create connection - it won’t work if you click on the scorecard.

Connect to “Date Calendar”, and use “Row Member à Member Unique Name” as the source.

Your dashboard should look like the following:

So let’s deploy to Sharepoint and see what we’ve done:

Nifty! We can now control the Years filter by selecting from the Product Categories. If you needed multiple levels, you can also send values from one scorecard to another.

This also solves the maximum members problem as the scorecard is an AJAX component that requeries when you drill down

4: Bonus section : Connection formula

So the filtering is nice, but what if we wanted to see more than just the single member?

Well, that’s easy enough. Go back to Dashboard Designer, and right click on “Years Filter” under connections on the Internet Sales Amount part, as in Figure 7 below, and click “Edit Connection”

Entering <<UniqueName>>.Children  (case matters for <<UniqueName>>) will return the children of the currently selected member ~ in this case H1 and H2. This will unfortunately break when we choose a day, and perhaps we want to always show months rather than halves.

As everything except <<UniqueName>> is MDX, we can do this and more J

One possibility is Descendants(<<UniqueName>>, [Date].[Calendar].[Month])  - this will also break at day level though.

So what can be done is to use a CASE statement for different levels:
CASE WHEN <<UniqueName>>.LEVEL.ORDINAL  < 4

THEN  

Descendants(<<UniqueName>>, [Date].[Calendar].[Month])

WHEN <<UniqueName>>.LEVEL.ORDINAL  = 4

THEN

<<UniqueName>>.Children

ELSE

<<UniqueName>>.Lag(30): <<UniqueName>>

END

Here: Above month level, display all the months. At month level, display all the days. Below Month level, display the last 30 days.

Performance Point scorecards provide a very flexible and powerful filtering mechanism, and should always be considered when building dashboards.

One final tip: If you add multiple measures as columns, you can show a member for *any* of the measures being present.

Final preview image: