Dimension MDX Examples

  • Top ten products on product category level based on actual
    {TopCount([Product].[Product Category].Members, 10, [Measures].[Actual])}
  • Last 10 products on product category level based on actual
    {BottomCount([Product].[Product Category].Members, 10, [Measures].[Actual])}
  • Return children if exists to selected member in the Filter Selector otherwise return selected member.
    StrToSet(IIF(IsLeaf(_FILTERTUPLE_.Item(0)), "{_FILTERTUPLE_.Item(0)}",
     "{_FILTERTUPLE_.Item(0).Children}")). 
  • Return members filtered on a custom property. We need to exclude the all level from the set to filter on since it does not have any custom properties.
    Filter(Except([Warehouse].Members,{ [Warehouse].[All Warehouse]}),
    [Warehouse].CurrentMember.Properties("PROPERTYNAME") = "PROPERTYVALUE")
  • How to define a default value for current month referring to the system clock if the month level in the time dimension is formatted yyyymm
    StrToMember("[Time].[Month].[" + Format(Now(), "yyyymm") + "]")
  • How to create a named set for current month referring to the system clock if the month level in the time dimension is formatted yyyymm
    {StrToMember("[Time].[Month].[" + Format(Now(), "yyyymm") + "]")}
  • How to create a named set referring to a separate column in the table used for the time dimension as a member property
    {Filter([Time].[Month].Members, [Time].CurrentMember.Properties
    ("IsCurrentMonth") <> "0").Item(0).Item(0)}
  • Referring to a named set called Current Month
    [Current Month].Item(0).Item(0)
  • Referring to last 6 months (up to current month)
    LastPeriods(6, [Current Month].Item(0).Item(0))
  • How to use the named set Current Month for positioning on the quarter level
    LastPeriods(6, Ancestor([Current Month].Item(0).Item(0), Time.Quarter))
  • Last 6 members on month
    {Tail([Time].[Month].members,6)}
  • Last 6 members on month where actual is larger then 0
    {Tail(Filter({[Time].[Month].members},[Measures].[Actual] > 0),6)}
  • All months where actual is larger then 0
    {Filter({[Time].[Month].members},[Measures].[Actual] >0)}
  • Order months depending on actual
    {Order([Time].[Month].members, [Measures].[Actual], BDESC)}
  • To use a Named Set you enter the name of the set in the expression tab. E.g. if you have a named set in the cube named Last 6 Month, you just enter:
    [Last 6 Month]

The _FILTERMEMBER_ token is available in expressions and will be replaced with current member of the dimension selected in connected Filter Selector. See Configure axis to change members on filter change for an example how to use this token to enable members to be different depending on current filter.

When using a reference to a dimension, level, member or a named set in an expression that includes spaces or a start with a number you have to include the name in brackets []. Read SQL Server Books Online for more information about MDX expression syntax.

The _FILTERTUPLE_.Item(<index>) token is available in expressions and will be replaced with current member of the dimension represented by given index (index 0 represents left/top most dimension) in connected Filter Selector. See Configure axis to change members on filter change for an example how to use this token to enable members to be different depending on current filter.

  • pe/examples/mdxexamples.txt
  • Last modified: 2014/02/27 13:19
  • by pcevli