MDX/Rule Functions Explained

Square Bracket ([])

Required if the dimension or level name has a space or the name is a reserved word. However, it is recommended that you use a square bracket in all cases. In the example below the dimension name (Dim Name) has a space in it, but hierarchy does not and therefore has no square brackets. However, you could surround Hierarchy with square brackets as well.

-[Dim Name].Hierarchy

Ampersand (&)

Required when using a member key versus a member name. In the example below, the member key is an integer value of 1 so an ampersand precedes it. However, it will be the member name that is displayed on the report (not the key).

{} - Brace operator that constructs a set.

Syntax:

{<Member> [, <Member>...]}

|| - Concatenates two strings.

Syntax:

<String> || <String>

Closing Period - Returns the last sibling among the descendants of a member at a specified level.

Syntax:

ClosingPeriod ( <<level>>, <<member>>)

EXAMPLE:

ClosingPeriod(Month,Time.CurrentMember)

                    If Time.CurrentMember is year 2018 then the closing period at the Month level will be December’18.

Opening Period - Returns the first sibling among the descendants of a member at a specified level.

Syntax:

OpeningPeriod(<<level>>,<<member>>)

EXAMPLE:

OpeningPeriod(Month,Time.CurrentMember)

If Time.CurrentMember is year 2018, January 2018 is returned as the opening period at the Month level.

Lag - Returns the previous member at the specified index location to the current member at the same level.

Syntax:

<<Member>>.Lag(<<index>>)

EXAMPLE:

Time.CurrentMember.lag(1)

If Time.CurrentMember is year 2018, then the above function returns year 2017.

Lead - Returns the next member at the specified index location to the current member at the same level.

Syntax:

<<Member>>.Lead(<<index>>)

EXAMPLE:

Time.CurrentMember.Lead(1)

If Time.CurrentMember is year 2017, then the above function returns year 2018.

Parallel Period - Returns the Nth child of member that was number of periods ago (prior) on a particular ancestor level.

Syntax:

ParallelPeriod(<<Level>>,<<No. of Periods ago>>,<<Current Member>>)

EXAMPLE:

ParallelPeriod(Year,2,2015Q2)

Returns:

2013Q2

PeriodsToDate - Returns a set of periods (members) from a specified level starting with the first period and ending with a specified member.

Syntax:

<Set> PeriodsToDate()

<Set> PeriodsToDate(<Level>)

<Set> PeriodsToDate(<Level>, <Member>)

NOT - Returns the negation of a condition.

Syntax:

NOT <Logical Expression>

Current Member - Returns the current member in reference.

Syntax:

<<Dimension>>.CurrentMember

Ancestor - Returns the ancestor of a member at a specified level or distance.

Syntax:

Ancestor(<<member>>,<<ansLevel>>)

EXAMPLE:

                    Ancestor(Q214,2014)

                

Returns: FY2014

Avg - Returns the average value of a numeric expression evaluated over a set.

Syntax:

<Numeric Expression> Avg(<Set>)

<Numeric Expression> Avg(<Set>,<Numeric Expression>)

Last Child - Returns the last child of the member.

Syntax:

<<Member>>.LastChild

First Child - Returns the first child of the member.

Syntax:

<<Member>>.FirstChild

First Sibling - Returns the first sibling of the member.

Syntax:

<<member>>.FirstSibling

Last Sibling - Returns the last sibling of the member.

Syntax:

<<member>>.LastSibling

Sum - Returns the sum of a numeric expression evaluated over a set.

Syntax:

<Member> StrToMember(<String>)

StrtoMember - Returns a member from a string in MDX format containing a member, specified in the StringExpression.

Syntax:

StrToMember(StringExpression)

EXAMPLE:

(StrtoMember(“Scenario.&[“+Time.CurrentMember.properties(“Budget_id”)+”]”),[Calculated].&[1])

Descendants - Returns a set of descendants of a member at a specified level, optionally including or excluding descendants in other levels.

Syntax:

Descendants(Member, [DescLevel[, DescFlags]])

<Set> Descendants(<Member>)

<Set> Descendants(<Member>, <Level>)

<Set> Descendants(<Member>, <Level>, <Flag>)

• If ‘Level’([DescLevel} is the same as member, then only ‘Member’ is returned.

• DescFlags available are as follows:

  • SELF : Default, Returns descendant members from ‘level’ only includes ‘Member’

  • BEFORE: Returns descendant members from all levels between the specified member and the specified level, or at the specified distance. It includes the specified member, but does not include members from the specified level or distance.

  • BEFORE_AND_AFTER: Returns descendant members from all levels subordinate to the level of the specified member. It includes the specified member, but does not include members from the specified level or at the specified distance.

  • SELF_AND_AFTER: Returns descendant members from Level and all levels subordinate to Level.

  • SELF_AND_BEFORE: Returns descendant members from the specified level and from all levels between the specified member and the specified level including the specified member.

  • SELF_BEFORE_AFTER: Returns descendant members from all levels subordinate to the level of the specified member, and includes the specified member.

  • LEAVES: Returns leaf descendant members between the specified member and the specified level, or at the specified distance.

In Practice

To return all the Quarters based on a selected time member, you might write an expression like this:

Descendants(Ancestor([Time].CurrentMember,Year),Quarter)

IIF - LogicalExpression is anything that evaluates to a non-zero.

The available comparison operators are =, <>, >, <, >=, and <=. The following can be used to combine conditional expressions: OR, AND, XOR, and NOT.

IIF(LogicalExpression, ExpressionIfTrue, ExpressionIfFalse)