Aggregate Storage and MDX Outline Formulas

Calculator

@ABS(Actual-Budget)

MDX

Abs([Actual]-[Budget])

@ANCEST(Product,2,Sales) 

( 
  Sales,
  Ancestor(
    Product.CurrentMember,
    Product.Generations(2)
   )
)

@ANCESTORS("New York")

 Ancestors([New York].parent, [Market].levels(2))

@ANCESTVAL(Product,2,Sales) 

(Sales, 
  Ancestor(
   Product.CurrentMember,
   Product.Generations(2)
  )
 ).Value

Calculator

@ATTRIBUTE(Can)

MDX

Attribute([Can])

@ATTRIBUTEBVAL(Caffeinated)

Product.CurrentMember.Caffeinated

@ATTRIBUTESVAL("Pkg Type")

Product.CurrentMember.[Pkg Type]

@ATTRIBUTEVAL(Ounces)

Product.CurrentMember.Ounces

If the dimensionality of all elements in the input set to @AVG is the same, use Avg. Translate SKIPNONE to INCLUDEEMPTY.

If the dimensionality of all elements in the input set to @AVG is not the same, then perform average by explicitly adding the tuples and dividing by the set cardinality (the number of tuples in the set).

Note that the MDX Avg function skips missing cell values by default.

Calculator

@AVG(SKIPMISSING, @CHILDREN(East))

MDX

Avg([East].Children)

If SKIPMISSING is replaced by SKIPNONE, the translation changes to:

Avg([East].Children, Sales, INCLUDEEMPTY)

For SKIPZERO, the translation is:

Avg([East].Children,
  IIF(Market.CurrentMember.Value=0, Missing,
    IIF(Market.CurrentMember= Missing,0,
        Market.CurrentMember.Value
    )
  )
 )

For SKIPBOTH, the translation is:

Avg([East].Children,
     IIF(Market.CurrentMember=0, Missing,
         Market.CurrentMember.Value)
   )

Calculator

@AVGRANGE(SKIPMISSING, Sales, @CHILDREN(West))

MDX

Avg(CrossJoin({Sales},{[West].Children)})

If SKIPMISSING is replaced by SKIPNONE, the translation becomes:

Avg({[West].Children)},Sales,INCLUDEEMPTY)

If SKIPZERO is used, then the translation is:

Avg([West].Children),
 IIF(Sales = 0,  Missing,
    IIF(Sales =  Missing, 0, Sales)
    )
 )

Calculator

@CHILDREN(Market)

MDX

Children(Market)

or

Market.Children

Calculator

@MEMBER(@CONCATENATE("Qtr1", "1"));

MDX

Concat("01", "01")

Use Count if SKIPNONE.

Use NonEmptyCount if SKIPMISSING.

For SKIPZERO, see the example in the next column.

For SKIPBOTH, use Count (Filter(set, value <> 0 && value <> MISSING))

Calculator

@COUNT(SKIPMISSING,@RANGE(Sales, Children(Product)))

MDX

NonEmptyCount(CrossJoin({Sales},{Product.Children}))

Note that Count always counts including the empty cells, whereas NonEmptyCount does not.

For SKIPNONE, the translation is:

Count(Product.Children)

For SKIPZERO, the translation is:

NonEmptyCount
 (Product.Children,
  IIF(Sales=0,  Missing,
   IIF(Sales =  Missing, 0, sales)
  )
 )

Calculator

@CURGEN(Year)

MDX

Year.CurrentMember.Generation

Calculator

@CURLEV(Year)

MDX

Year.CurrentMember.Level

Calculator

@CURRMBR(Product)

MDX

[Product].CurrentMember

Calculator

@CURRMBRRANGE(Year, LEV, 0, -1, 1)

MDX

RelMemberRange
      (Year.CurrentMember, 1, 1, LEVEL)

Calculator

@EXP("Variance %"/100);

MDX

Exp([Scenario].[Variance %]/100)

Calculator

@FACTORIAL(5)

MDX

Factorial(5)

Calculator

@ICHILDREN(Market)

MDX

Union({Market}, {Market.children})

Calculator

@IDESCENDANTS(Market)

MDX

Descendants(Market)

Calculator

@ILSIBLINGS(Florida)

MDX

MemberRange(Florida.FirstSibling, Florida.Lag(1))

Calculator

@INT(104.504)

MDX

Int(104.504)

Calculator

@ISANCEST(California)

MDX

IsAncestor(Market.CurrentMember, California)

Calculator

@ISDESC(Market)

MDX

IsAncestor([Market], [Market].Dimension.CurrentMember)

or

Count(Intersect({Member.Descendants}, 
  {Member.dimension.CurrentMember}) = 1

Calculator

@ISGEN(Market, 2)

MDX

IsGeneration(
  Market.CurrentMember, 2)

Calculator

@ISIANCEST(California)

MDX

IIF(
  IS(Market.CurrentMember, California)
   OR 
  IsAncestor(Market.CurrentMember, California),
  <true-part>, <false-part>
)

Calculator

@ISICHILD(South)

MDX

IIF(
 Is(Market.CurrentMember,South)
  OR 
 IsChild(Market.CurrentMember,South),
 <true-part>, <false-part>
)

Calculator

@ISIDESC(South)

MDX

(Count(Intersect({[South].Descendants}, {South}) = 1
OR
Is(CurrentMember, [South]))

Calculator

@ISIPARENT(Qtr1) 

MDX

IIF(
  Is(Time.CurrentMember, [Qtr1])
   OR 
  IsChild([Qtr1], Time.CurrentMember),
  <true-part>, <false-part>)

Calculator

@ISISIBLING(Qtr2)

MDX

IIF(
     IsSibling( 
     [Qtr2], Time.CurrentMember
         ),
 <true-part>, <false-part>
)

However, if the second argument subscribes to MDX set rules then the translation is easier, as shown. For example:

Calculator

@ISMBR("New York":"New Hampshire")

MDX

IIF(
 Count(
   Intersect(
    {MemberRange([New York],[New Hampshire])},
    {Market.CurrentMember}
   )
  ) = 1,
 <true-part>, <false-part>
)

Calculator

@ISPARENT("New York")

MDX

IsChild(Market.CurrentMember,[New York])

Calculator

@ISSAMEGEN(West)

MDX

IIF(
 Ordinal(
   Market.CurrentMember.Generation
 ) 
 = Ordinal(West.Generation),
 <true-part>, <false-part>
)

@RSIBLINGS

MemberRange(member.Lead(1), member.LastSibling)

Calculator

@LSIBLINGS(Qtr4)

MDX

MemberRange([Qtr4].FirstSibling, [Qtr4].Lag(1))

Calculator

@RSIBLINGS(Qtr1)

MDX

MemberRange([Qtr1].Lead(1), [Qtr1].LastSibling)

Calculator

@MAX(Jan:Mar)

MDX

Max(MemberRange([Jan], [Mar]))

Calculator

@MAXRANGE(Sales, @CHILDREN(Qtr1))

MDX

Max(
  CrossJoin(
    {Sales},
        {[Qtr1].Children}
  )
)
Max([Qtr1].Children, Sales)

Calculator

@MAXS(SKIPMISSING,Sales,@CHILDREN(Qtr1))

MDX

Max(
  Children([Qtr1]),Sales)
)

For SKIPZERO, the translation is:

Max (Children ([Qtr1]), IIF (Sales = 0, MISSING, Sales))

For SKIPBOTH, the translation is the same as for SKIPZERO, because Max skips missing values by default.

Calculator

@MAXSRANGE(SKIPMISSING, Sales, @CHILDREN(Qtr1))

MDX

Max(
  Children([Qtr1]),Sales)
)

For SKIPZERO, the translation is:

Max (Children ([Qtr1]), IIF (Sales = 0, MISSING, Sales))

For SKIPBOTH, the translation is the same as for SKIPZERO, because Max skips missing values by default.

Calculator

@MDANCESTVAL(2, Market, 2, Product, 2, Sales)

MDX

Construct a tuple consisting of Sales from the Measures dimension, the ancestor of the current member along the Market dimension, and the ancestor of the current member along the Product dimension. Then get the value of the tuple.

(Sales, Ancestor(Market.CurrentMember, 2),
 Ancestor(Product.CurrentMember, 2)).Value

Calculator

@MDPARENTVAL(2, Market, Product, Sales)

MDX

Construct a tuple consisting of Sales from the Measures dimension, the parent of the current member along the Market dimension, and the parent of the current member along the Product dimension. Then get the value of the tuple.

(Sales, Market.CurrentMember.Parent, 
 Product.CurrentMember.Parent).Value

Calculator

@MERGE(@CHILDREN(East),@CHILDREN(West))

MDX

{Union([East].Children, [West].Children)}

Calculator

@MIN(Jan:Mar)

MDX

Min(MemberRange([Jan], [Mar]))

Calculator

@MINRANGE(Sales, @CHILDREN(Qtr1))

MDX

Min(
  CrossJoin(
    {Sales},
        {[Qtr1].Children}
  )
)
Min([Qtr1].Children, Sales)

Calculator

@MINS(SKIPMISSING,Sales,@CHILDREN(Qtr1))

MDX

Min(
 Filter(
  Children([Qtr1]),
  Sales <> Missing
 )
)

For SKIPZERO, the translation is:

Min(
 Filter(
  Children([Qtr1]),
  Sales <> 0
 )
)

For SKIPBOTH, the translation is:

Min(
 Filter(
  Children([Qtr1]),
  Sales <> 0 AND
  Sales <> Missing
 )
)

Calculator

@MINSRANGE(SKIPMISSING, Sales, @CHILDREN(Qtr1))

MDX

Min(
 Filter(Children([Qtr1]), 
  Sales <> Missing
 )
)

For SKIPZERO, the translation is:

Min(
 Filter(Children([Qtr1]), 
  Sales <> 0
 )
)

For SKIPBOTH, the translation is:

Min (
 Filter(Children([Qtr1]), 
  Sales <> 0 AND
  Sales <> Missing
 )
)

@NEXT(member,[n, range]) returns the nth cell value in the range from the supplied member. The function returns a missing value if the supplied member does not exist in the range. If range is not specified, level-0 members of the Time dimension are used.

MDX does not have an equivalent function for an arbitrary range. However, if the range is restricted to members from a specific level or generation, then using NextMember (if n=1) or Lead/Lag will work as shown in the sample translation. This is probably the common case.

Calculator

@Next(Cash)

MDX

(NextMember(
 [Year].CurrentMember, LEVEL), 
 [Cash]).Value

Alternative:

Calculator

@Next(Cash, 2)

MDX

CrossJoin(
  Year.CurrentMember.Lead(2, LEVEL),
  Cash).Value

Calculator

@PARENTVAL(Market, Sales)

MDX

([Sales], [Market].CurrentMember.Parent).Value

@PRIOR(member,[n, range]) returns the nth cell value in the range from the supplied member. The function returns a missing value if the supplied member does not exist in the range. If range is not specified, level-0 members of the Time dimension are used.

MDX does not have an equivalent function for an arbitrary range. However, if the range is restricted to members from a specific level or generation, then using PrevMember (if n=1) or Lead/Lag will work as shown in the sample translation. This is probably the common case.

Calculator

@Prior(Cash)

MDX

PrevMember(Year.CurrentMember, LEVEL), [Cash]).Value

Alternative:

Calculator

@Prior(Cash, 2)

MDX

(Year.CurrentMember.Lag(2, LEVEL), [Cash]).Value

Calculator

@RANGE(Sales, @CHILDREN(East))

MDX

CrossJoin({Sales}, {[East].Children})

Calculator

@REMOVE(@CHILDREN(East),@LIST("New York",Connecticut))

MDX

Except ({[East].Children}, {[New York],[Connecticut]})

Calculator

@SUMRANGE("New York",Jan:Jun)

MDX

Sum(CrossJoin({[New York]}, {[Jan]:[Jun]}))