MDX Filters in PPS Monitor

I had a question on how to implement MDX Filters in PerformancePoint Monitor awhile back.  Nick Barclay  provided an answer here.

Some samples Nick provided were:

   1: [Time].[Fiscal Year].Children

and

   1: DESCENDANTS(
   2:   [Geography].[Geography].[All Geographies]
   3:  ,[Geography].[Geography].[Country]
   4:  ,SELF_AND_BEFORE) 

Depending on how you have implemented your cube, you can also do some other clever things.  On a recent project, I had a Date dimension with a hierarchy that would present the current month as ‘Current Month’, so MDX such as the following :

   1: {[Dim Date].[Calendar].[Month Name].&[Current Month].Lag(6):[Dim Date].[Calendar].[Month Name].&[Current Month]}

could be used to provide the last 6 months contained in the cube as a drop down list:

   1: Nov 2007
   2: Dec 2007
   3: Jan 2008
   4: Feb 2008
   5: Mar 2008
   6: Current Month

Another useful possibility is to provide a filter based on a hierarchy, but an individual member (in this case the ‘Unknown’ member) should not be displayed in the pick list:

   1: FILTER(
   2:     DESCENDANTS(
   3:          [Entity].[Entity]
   4:         ,[Entity].[Entity]
   5:         ,SELF_AND_BEFORE
   6:     )
   7:     ,NOT [Entity].[Entity].CURRENTMEMBER IS [Entity].[Entity].&[Unknown]
   8: )

Leave a Reply