PerformancePoint Server – Agile BI http://agilebi.com A community for sharing ideas about business intelligence development using agile methods. Wed, 15 Sep 2010 02:16:08 +0000 en-US hourly 1 https://wordpress.org/?v=6.2.2 MDX Filters in PPS Monitor http://agilebi.com/blog/2008/04/26/mdx-filters-in-pps-monitor/ Sat, 26 Apr 2008 22:44:42 +0000 http://8.8 Continue reading →]]> 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: )

]]>