HOWTO: Filter rows and columns by selecting from a filter

(Sorry for the gap since my last post, things have been a bit busy lately in Peter Eb. land…)

One simple way to do this filtering is to enable suppress blank rows or suppress blank columns. Then when there is no data (non-null) in the cube for those rows/columns they won’t be shown. This is basically turning on NON EMPTY for an axis. Its useful in a lot of scenarios, but is also very heavy handed. You don’t get any all null rows or columns which may not work well for scenarios where data isn’t seeded.

This usually gets authors started, but quickly they will want to only show data that is relevant for the filter selection without filtering out all blanks. The feature we built for this scenario is a “dimension property filter” (DPF). Instead of adding it like a normal dimension its a matrix action in the authoring pane.

What a DPF allows the author to pick a dimension property that controls the filtering. Under the hood we use the sub-select MDX syntax to create a sub cube which actually performs the filtering. Then the user selects the value that controls which members from that dimension are displayed. So if you wanted to control which type of accounts are displayed you can start with the common Time/Scenario on columns and Accounts on rows, start with All Members so we can see a report with far too many accounts displayed…

Then add a dimension property filter by selecting the matrix in the action pane and choosing “Add dimension property filter” instead of “Select dimensions” you’ll get this dialog where I’ve already chosen Account and the Debit_Credit property.

 

The values selected (the list on the right) controls filter scope for the DPF (see my previous post for a few more details on filter scopes). You can pick a subset of values or choose to include them all in the scope. So using this mechanism a lot of different kinds of filtering possibilities are possible. If you are working with a dimension that allows properties to be custom defined you can have a process that highlights not just categories like this but also flags the top-10 “valued” items to focus on. Or you might also want to flag members that don’t fit into some kind of mathematical calculation and just flag a bunch of members as “important”. (Yes MDX has the topcount function, but you might not want to have to recreate the logic for this set over all your various reports. And named sets would be another good solution for both these scenarios, but the PPS member picker doesn’t support them, yet)

And here’s the matrix now with Credit chosen:

And that’s it!

 

 

Playing in the land of Peter Eb. while writing this post...Give Me One Good Reason by Blink-182 from

Comments

• Anonymous
  June 12, 2008
  Great post.  This is a very common scenario!  Can you expand on "Under the hood we use the sub-select MDX syntax..."  Can you give an example of the MDX query that uses the debit/credit selection?  Where would we enter this MDX? Thanks!

• Anonymous
  June 16, 2008
  All you have to do to see the MDX that the addin uses is put excel's focus inside a matrix cell, then choose view mdx. The sub select syntax I believe is new for AS2005. Basically you do select {abcdefg} on 0 from select {zys} from [cube]. And the 2nd select effectively creates a filtered sub-cube that the 1st select will be reading from.