Member & Set Expressions

Member Expressions

A member expression starts from a single member in a dimension and optionally uses various methods to derive a set of members from the same dimension. Derivation methods are appended using the dot operator:

dimension.member_spec[.derived_method[(arg)]...]

Member specification can be:

• A member name
• A member key prefixed by &
• A member tag prefixed by #
• The special keyword CurrentMember

note

Members cannot be referenced by Caption.

Examples:

[Account].[Rent]
[Account].#[netincome].[SelfAndDescendants]
[Period].CurrentMember.Ancestor(2).FirstLeafDescendant
[Department].&[23].Siblings
[Period].CurrentMember.SelfAndNext(11)

CurrentMember references (known as relative references) are only supported in assignments.

Note on Identifiers

Square brackets are used to enclose identifiers that start with or contain special characters. Anything in brackets will always be considered an identifier and never a keyword.

Therefore, [Account].CurrentMember and [Account].[CurrentMember] are two different things:

• The former refers to the current member in the Account dimension
• The latter refers to the account named "CurrentMember"

Best Practice

Enclose all dimensions, keys, names, and tags in brackets, and leave functions/methods without brackets.

Supported Methods

Method	Relative Supported	Description
Ancestor(n)	✅	Returns the ancestor n levels up. Nothing if member's level is < n
Children	✅	Returns all children of the member
Descendants	✅	Returns all descendants of the member (excluding the member)
First	✅	Returns the first member of a set in natural dimension order
FirstLeafDescendant	✅	Returns the first leaf descendant of the member (including the member itself)
Last	✅	Returns the last member of a set in natural dimension order
LastLeafDescendant	✅	Returns the last leaf descendant of the member (including the member itself)
LeafDescendants	✅	Returns all leaf descendants of the member. If the member is a leaf, returns the member
Next(n)	❌	Returns at most n members on the same level as the referenced member, excluding the member itself
NextMember	✅	Returns the next member on the same level of the hierarchy. Equivalent to Next(1)
Parent	✅	Returns the parent member. Will return the virtual root for level 1 members. Equivalent to Ancestor(1)
Prev(n)	❌	Returns the previous at most n members on the same level as the referenced member, excluding the member itself
PrevMember	✅	Returns the previous member on the same level of the hierarchy. Equivalent to Prev(1)
SelfAndChildren	✅	Returns the member and its children
SelfAndDescendants	✅	Returns the member and its descendants
SelfAndNext(n)	❌	Like Next(n) but includes the referenced member. Referenced member is included in the count
SelfAndPrev(n)	❌	Like Prev(n) but includes the referenced member. Referenced member is included in the count
Siblings	✅	Returns all children of the parent member, including the referenced member

For methods that support arguments, the argument must be an integer constant—it cannot be an expression. As an exception, Ancestor() also supports a parameter as the argument.

Set Expressions

Set expressions combine multiple member expressions using set operators:

Function	Description
Union(set1, set2[, setn...])	Performs a union between all sets; eliminates duplicate members
Intersect(set1, set2[, setn...])	Performs an intersection between all sets; eliminates duplicate members
Except(set1, set2[, setn...])	Discards from set1 all members that exist in set2 through setn
Range(set1, set2)	Returns all members between set1 and set2. Both arguments must be single-member sets that are on the same level in the hierarchy

note

Set expressions are supported only in Scope and Freeze statements.