HOWTO: Dynamic rows or columns with a dimension property filter

To filter rows and columns by a filter selection has previously been discussed here. But one thing you might not have noticed is that the list of property values (aka the filter scope) is static. If a new member property appears the form needs to be modified to include it, even if the “all” button. This is different than a normal filter, where the UI allows a dynamic filter scope by a number of expressions. Luckily in the RDL both kinds of filters are stored in the same fashion. The only thing that makes a dimension property filter (DPF) different is that the hierarchy defined for the scope query is the attribute hierarchy that is also created on the AS cube. So in the report definition itself the scope can be modified from a static set to a dynamic set by looking at the RDL for a regular filter and deriving the kinds of expressions supported.

Warning, this involves going to the RDL tab of the report properties editor and editing the extensions that the PPS Planning AddIn uses. I’ll readily admit that this not always a trivial task. It takes some practice to map from the regular treeview tab to the RDL tab. Going into that level of detail is a topic for another post. So for now I’m going to assume that you’ve done some experimentation and know what you’re looking for. (As a hint, while experimenting in your-land use a simple matrix and only 1 filter at a time, it cuts down dramatically on all the extra XML you have to wade through.)

So then, here’s an example of just the HierarchyMemberSets for a normal filter with “Entity.CorpMgmtEnt.All” and “Entity.CorpMgmtEnt.All (children of)” selected as the scope:

   1: 

   2:   [Entity].[CorpMgmtEnt]

   3:   

   4:     

   5:       

   6:         ASH

   7:         [Entity].[CorpMgmtEnt].&[5018]

   8:       

   9:       Members

  10:     

  11:     

  12:       

  13:         ASH

  14:         [Entity].[CorpMgmtEnt].&[5018]

  15:       

  16:       Children

  17:     

  18:   

  19: 

The key is for a single static selection you’ll see “Members” and for a dynamic selection you’ll see “Children” where the type of selection varies. Once you figure out the kind of selection you want by looking at normal filters we can go look at what a property filter look like. So to start with I’ll remove the Entity filter and use a Currency filter based on the property name. Here’s the HierarchyMemberSets for that:

   1: 

   2:   [Currency].[MemberName]

   3:   

   4:     

   5:       

   6:         Canadian Dollar

   7:         Canadian Dollar

   8:       

   9:       Members

  10:     

  11:     

  12:       

  13:         EURO

  14:         EURO

  15:       

  16:       Members

  17:     

  18:     

  19:       

  20:         US Dollar

  21:         US Dollar

  22:       

  23:       Members

  24:     

  25:     

  26:       

  27:         Yen

  28:         Yen

  29:       

  30:       Members

  31:     

  32:   

  33:   

  34:     MemberName

  35:   

  36: 

So now we can see why the selection is static: They are all listed separately (just ignore the fact that the uniquename is not really a uniquename, that artifact is a topic for another post). You’ll note that the hierarchy though is using the attribute hierarchy created by AS so you might have even guessed that the AddIn might generate MDX supporting expressions for DPFs. In fact, I had forgotten about this until somebody posted in the forums a month or so ago that they wanted this scope to be dynamic.

Anyway, in my example I want all the currencies to be available no matter what changes are made to the dimension. So I’m going to change the scope to be the MDX expression “[Currency].[MemberName].[All].[Children]”. First I remove all the static member sets and insert a proper uniquename:

   1: 

   2:   [Currency].[MemberName]

   3:   

   4:     

   5:       

   6:         All

   7:         [Currency].[MemberName].[All]

   8:       

   9:       Children

  10:     

  11:   

  12:   

  13:     MemberName

  14:   

  15: 

Then click Validate RDL to make sure there are no syntax errors. Then click ok and the report will be refreshed. Now using the filter should give the same values as the static selection, but when a new currency member is added with a new value for the property MemberName it will automatically appear in this list. Voila! Not so simple, but not so hard.