Set Functions

The MDX functions that are listed here indicate their return type.

AddCalculatedMembers

returns a set that includes calculated members that meet the criteria of a given set definition (by default, calculated members are not returned by set functions). AddCalculatedMembers(<Set>)Example:

WITH MEMBER [geography].[geography].[all geography].[u.s.a].
                              [north u.s.a] AS ' SUM(  {[geography].[geography].[all geography].[u.s.a].[north
                              central],   [geography].[geography].[all geography].[u.s.a].[north
                              east],   [geography].[geography].[all geography].[u.s.a].[north west]})'
                              SELECT [Measures].ActualSalesSUM ON COLUMNS,        AddCalculatedMembers({[geography].[geography].[All
                              geography].[u.s.a].[north central]})  ON
                              ROWS FROM [booksnall]

AllMembers

returns a set that contains all members of the specified dimension, hierarchy, or level, including calculated members. <Dimension>.AllMembers<Hierarchy>.AllMembers<Level>.AllMembersThe following example references all levels and members below a specific level using AllMembers Function. You can include calculated Members (set function).

SELECT
                              {[time].AllMembers} ON COLUMNS  ,  {[geography].[global].[all global].[europe]}
                              ON ROWS   FROM [orionstar]  WHERE  [measures].[total_retail_pricesum]
                              SELECT {[measures].AllMembers} ON COLUMNS  ,  [geography].[global].[all
                              global].[europe] on ROWS FROM [orionstar]

Ancestors

returns the set of ancestors of a member to a specified level or distance. This includes or excludes ancestors at other levels. Here is the syntax for the Ancestors function:Ancestors(<member>,[<level>[,<anc_flags>]])Ancestors(<member>,<distance>[,<anc_flags>])The following example shows retrieving the Ancestor at a particular level:

WITH MEMBER [measures].[product
                              family sales] AS '(ancestor([product].currentmember,[product]. [product
                              family]),[measures].[unit sales])'  SELECT {[measures].[unit sales],[measures].[product
                              family sales]}  ON COLUMNS, {[product].members} ON ROWS FROM [sales]

Level

returns the set of ancestors of a member that are specified by <Member> to the level that is specified by <Level>. Optionally, the set is modified by a flag that is specified in <Anc_flags>.

Ancestors(<member>,[<level>[, <Anc_flags>]])

If no <Level> or<Anc_flags> arguments are specified, then the function behaves as in the following syntax: Ancestors(<member>, <member>.Level, SELF_BEFORE_AFTER)

Distance

returns the set of ancestors of a member. The set of ancestors is specified by <member> and is <distance> steps away in the hierarchy. Optionally, the set is modified by a flag that is specified in <Anc_flags>. Specifying a <Distance> of 0 returns a set consisting only of the member that is specified in <Member>.Ancestors(<member>, <distance>[,<Anc_flags>])

Ancestor Flag Options

`Options`	`Value Returned`
AFTER	Returns ancestor members from all levels between <Level> and <Member>, including <Member> itself, but not member(s) found at <Level>.
BEFORE	Returns ancestor members from all levels above <Level>.
BEFORE_AND_AFTER	Returns ancestor members from all levels above the level of <Member> except members from <Level>.
ROOT	Returns the root-level member. This flag is the opposite of the LEAVES flag for the Descendants function.
SELF (default)	Returns ancestor members from <Level> only. Includes <Member>, if and only if <Level> that is specified is the level of <Member>.
SELF_AND_AFTER	Returns ancestor members from <Level> and all levels below <Level>, down to and including <Member>.
SELF_AND_BEFORE	Returns ancestor members from <Level> and all levels between and above <Member>.
SELF_BEFORE_AFTER	Returns ancestor members from all levels above the level of <Member>, including <Member> and member(s) at <Level>.

Note: By default, only members at the specified level or distance are included. This function corresponds to an <Anc_flags> value of SELF. By changing the value of <Anc_flags>, you can include or exclude ancestors at the specified level or distance, the ancestors before or the ancestors after the specified level or distance (until the root node), as well as all requests of the root ancestor or ancestors regardless of the specified level or distance.

Assuming that the levels in the Location dimension are named in a hierarchical order, an example of levels would be All, Countries, States, Counties, and Cities.

Ancestor Expressions and Returned Values

`Expressions`	`Value Returned`
Ancestors (USA)	All members
Ancestors (Wake, Counties)	USA
Ancestors (Wake, Counties, SELF)	USA
Ancestors (Wake, States, BEFORE)	USA, All
Ancestors (Wake, Counties, AFTER)	Wake (includes member itself), North Carolina
Ancestors (Raleigh, States, BEFORE_AND_AFTER)	Raleigh, Wake, USA, All members
Ancestors (Raleigh, States, SELF_BEFORE_AFTER)	Raleigh, Wake, NC, USA, All members
Ancestors (NC, Counties, Root)	All members
Ancestors (Wake, 1)	North Carolina
Ancestors (Wake, 2, SELF_BEFORE_AFTER)	Wake, NC, USA, All members

Ascendants

returns all ancestors of the specified member up through the root level, including the member itself. Ascendants (<Member>)Example: The following example shows retrieving the member at the specific level above and the current member dynamically using the Ascendants function (set function).

SELECT  {Ascendants([time].[all
                              yqm] .[2002])} ON COLUMNS  , {Descendants([geography].[global].[all
                              global],2)} ON ROWS FROM [orionstar]  WHERE [measures].[total_retail_pricesum]

Axis

returns a set that is defined in an axis. Axis (0) pertains to row members, where Axis (1) pertains to column members.Axis (<Numeric Expression>)Example: Axis (0) Axis (1)

Note: The Axis function is not allowed in session- or global-named sets or calculations.

BottomCount

returns a specified number of items from the bottom of a set.

BottomCount(<Set>,<Count>[,Numeric
                              Expression>[,<True|False>]])

Example: The following example shows displaying the bottom 5 products in the Clothes product category for 2002Q4 using the BottomCount function.

SELECT
                              {[time].[yqm].[all yqm].[2002].[2002q4] } ON COLUMNS  ,  {BottomCount([product].[all
                              product].[clothes & shoes].[clothes]. children ,5,[measures].[total_retail_pricesum]
                              ) } ON ROWS   FROM [orionstar]  WHERE  [measures].[Total_retail_pricesum]

Note: The True|False flag is for including duplicates. If it is set to TRUE, then any member that has the same value as the last member will also be returned. If it is set to FALSE, then it will work as it always did. The default value for the flag is FALSE.

Note: Constant numeric expressions should not be entered for this function.

BottomPercent

sorts a set and returns the specified number of bottommost elements whose cumulative total is at least a specified percentage.

(<Set>,<Percentage>[,<Numeric
                              Expression>])

Example: The following example shows displaying the bottom 25% of Clothes Items in the Product Category for 2002 using the BottomPercent function.

SELECT
                              {[time].[yqm].[all yqm].[2002] } ON COLUMNS  ,  {BottomPercent([product].[all
                              product].[clothes & shoes]. [clothes].children ,25,[measures].[total_retail_pricesum]
                              )  } ON ROWS   FROM [orionstar]  WHERE  [measures].[total_retail_pricesum]

Note: Constant numeric expressions should not be entered for this function.

BottomSum

sorts a set by using a numeric expression and returns the specified number of

bottommost elements whose sum is at least a specified value.

(<Set>,<Value>[,<Numeric
                              Expression>])

Example: The following example shows obtaining the items that have a cumulative total below the specified amount using BottomSum function.

SELECT
                              {[time].[yqm].[all yqm].[2002] } ON COLUMNS  ,  {BottomSum([product].[all
                              product].[clothes & shoes]. [clothes].children ,6000000,[measures].[total_retail_pricesum]
                              )  } ON ROWS  FROM [orionstar]  WHERE  [measures].[total_retail_pricesum]
                              WHERE  [measures].[total_retail_pricesum]

Note: Constant numeric expressions should not be entered for this function.

Children

returns the children of a member. <Member>.Children

Crossjoin

returns the cross-product of two sets. Crossjoin(<Set1>,<Set2>)

Descendants

returns the set of descendants of a member to a specified level or distance. Optionally, this includes or excludes descendants at other levels. By default, only members at the specified level or distance are included. Descendants(<Member>,[<Level>[,<Desc_flags>]])Descendants(<Member>,<Distance>[,<Desc_flags>])

Descendants Flag Options

`Options`	`Value Returned`
AFTER	Returns descendant members from all levels that are subordinate to <Level>.
BEFORE	Returns descendant members from all levels between <Member> and <Level>, not including members from <Level>.
BEFORE_AND_AFTER	Returns descendant members from all levels that are subordinate to the level of <Member>, except members from <Level>.
LEAVES	Returns leaf descendant members between <Member> and <Level> or <Distance>. This flag is the opposite of the ROOT flag for the Ancestors function.
SELF (default)	Returns descendant members from <Level> only. Includes <Member>, only if <Level> is specified at the level of <Member>.
SELF_AND_AFTER	Returns descendant members from <Level> and all levels subordinate to <Level>.
SELF_AND_BEFORE	Returns descendant members from <Level> and all levels between <Member> and <Level>.
SELF_BEFORE_AFTER	Returns descendant members from all levels that are subordinate to the level of <Member>.

Distinct

returns a set by removing duplicate tuples from a specified set. Duplicates are eliminated from the tail.Distinct(<Set>)

DrilldownLevel

drills down to the members of a set one level below the lowest level that is represented in the set, or to one level below an optionally specified level of a member that is represented in the set. DrilldownLevel(<Set>[,{<Level>|,<Index}])

DrilldownLevelBottom

drills down the members of a set to one level below the lowest level that is represented in the set, or to one level below an optionally specified level of a member that is represented in the set. However, instead of including all children for each member at the specified <Level>, only the bottom <Count> of children is returned, based on <Numeric Expression>.

DrilldownLevelBottom(<Set>,<Count>[,[<Level>]
                              [,<Numeric Expression>]])

Note: Constant numeric expressions should not be entered for this function.

DrilldownLevelTop

drills down the members of a set to one level below the lowest level that is represented in the set, or to one level below an optionally specified level of a member that is represented in the set. However, instead of including all children for each member at the specified <Level>, only the top <Count> of children is returned, based on <Numeric Expression>.

DrilldownLevelTop(<Set>,<Count>[,[<Level>]
                              [,<Numeric Expression>]])

Note: Constant numeric expressions should not be entered for this function.

DrilldownMember

drills down to the members in a specified set that are present in a second specified set. DrilldownMember(<Set1>,<Set2[,Recursive])

DrilldownMemberBottom

drills down to the members in a specified set that are present in a second specified set, therefore limiting the result set to a specified number of members.

DrilldownMemberBottom(<Set1>,<Set2>, <Count>[,
                              [<Numeric Expression>] [,Recursive]])

Note: Constant numeric expressions should not be entered for this function.

DrilldownMemberTop

drills to the members in a specified set that are present in a second specified set, therefore limiting the result set to a specified number of members.

DrilldownMemberTop(<Set1>,<Set2>, <Count>[, [<Numeric
                              Expression>] [,Recursive]])

Note: Constant numeric expressions should not be entered for this function.

DrillupLevel

removes all members in the set that are below the specified level. If the level is not given, then it determines the lowest level in the set and removes all members at that level. DrillupLevel(<Set>[,<Level>])

DrillupMember

drills to the members in a specified set that are present in a second specified set. DrillupMember(<Set1>,<Set2>)

Except

locates the difference between two sets and optionally retains duplicates. Except(<Set1>,<Set2>[,All])

Extract

returns a set of tuples from extracted dimension elements. Extract(<Set>,<Dimension>[,<Dimension>...])

Filter

returns the set that results from filtering a specified set that is based on a search condition. Filter(<Set>,<Search Condition>)

Generate

applies a set to each member of another set and is joined to the resulting sets. Generate(<Set1>,<Set2>[,All])

Head

returns the first specified number of elements in a set.

Head(<Set>[,<Numeric
                              Expression>])

Hierarchize

orders the members of a set in a hierarchy. Hierarchize(<Set>)

Intersect

returns the intersection of two input sets and optionally retains duplicates. Intersect(<Set1>,<Set2>[,All])

LastPeriods

returns a set of members prior to and including a specified member. LastPeriods(<Index>[,<Member>])

Members

returns the set of members in a dimension, level, or hierarchy. <Dimension>.Members<Level>.Members<Hierarchy>.Members

Mtd

returns the set of members that consist of the descendants of the Month level ancestor of the specified member, including the specified member itself. This function is analogous to the PeriodsToDate() function with the level defined as Month. Mtd([<Member>])

NameToSet

returns a set that contains a single member. The set is based on a string expression that contains a member name. NameToSet(<Member Name>)

NonEmptyCrossjoin

returns the cross-product of one or more sets as a set. This excludes empty tuples and tuples without associated fact table data.

NonEmptyCrossjoin(<Set1>[,<Set2>][,<Set3>...]
                              [,<Crossjoin Set Count>])

Order

arranges members of a specified set and optionally preserves or breaks the hierarchy.

Order(<Set>[,[<Numeric
                              Expression>][,BASC|BDESC]])

Order(<Set>[,[<String
                              Expression>][,BASC|BDESC]])

Order(<Set>,<Numeric
                              Expression>[,ASC|DESC])

Order(<Set>,<String
                              Expression>[,ASC|DESC])

Note: Constant numeric expressions should not be entered for this function.

PeriodsToDate

returns the set of members that consist of the descendants of the ancestor of the specified member at the specified level, including the specified member itself. PeriodsToDate([<Level>[,<Member>]])

Qtd

returns the set of members that consist of the descendants of the Quarter level ancestor of the specified member, including the specified member itself. This function is analogous to the PeriodsToDate() function with the level defined as Quarter. Qtd([<Member>])

Siblings

returns the siblings of a specified member, including the member itself. <Member>.Siblings

StripCalculatedMembers

returns a set that is generated by removing calculated members from a specified set. StripCalculatedMembers(<Set>)

StrToSet

returns a set that is constructed from a specified string expression in Multidimensional Expressions (MDX) format. StrToSet (<String Expression>)

Subset

returns a subset of tuples from a specified set. Subset(<Set>,<Start>[,<Count>])

Tail

returns a subset from the end of a set. Tail(<Set>[,<Count>])

ToggleDrillState

Toggles the drill state of members. ToggleDrillState(<Set1>,<Set2>[,RECURSIVE])

Note: In a graphical user interface, drilling up and down is often accomplished by double-clicking a label to expand or contract the information. Drilling down on a member causes the member's children to be returned; drilling up causes them to disappear from the results.

TopCount

returns a specified number of items from the topmost members of a specified set. TopCount(<Set>,<Count>[,<Numeric Expression>[,<True|False>]])

Note: Constant numeric expressions should not be entered for this function.

TopPercent

sorts a set and returns the topmost elements, whose cumulative total is at least a specified percentage.

TopPercent(<Set>,<Percentage>[,<Numeric
                              Expression>])

Note: Constant numeric expressions should not be entered for this function.

TopSum

sorts a set and returns the topmost elements whose cumulative total is at least a specified value.

TopSum(<Set>,<Value>[,<Numeric
                              Expression>])

Note: Constant numeric expressions should not be entered for this function.

Union

returns a set that is generated by the union of two sets. Optionally, duplicate members are retained. Union(<Set1>,<Set2>[,All])

VisualTotals

returns a set that is generated by dynamically totaling child members in a specified set. A pattern for the name of the parent member in the result set is used. VisualTotals (<Set>,<Pattern>)

Wtd

returns the set of members that consist of the descendants of the Week level ancestor of the specified member, including the specified member itself. This function is analogous to the PeriodsToDate() function with the level defined as Week. Wtd([<Member>])

Ytd

returns the set of members that consist of the descendants of the Year level ancestor of the specified member, including the specified member itself. This function is analogous to the PeriodsToDate() function with the level defined as Year. Ytd([<Member>])