Set Functions

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>)

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]

returns a set that contains all members of the specified dimension, hierarchy, or level, including calculated members.

<Dimension>.AllMembers

<Hierarchy>.AllMembers

<Level>.AllMembers

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] 

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>])

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>])

returns all ancestors of the specified member up through the root level, including the member itself.

Ascendants (<Member>)

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

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>)

Axis (0)
Axis (1)

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

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.  

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.  

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.  

returns the children of a member.

<Member>.Children

returns the cross-product of two sets.

Crossjoin(<Set1>,<Set2>)

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>])

returns a set by removing duplicate tuples from a specified set. Duplicates are eliminated from the tail.

Distinct(<Set>)

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}])

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.  

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.  

drills down to the members in a specified set that are present in a second specified set.

DrilldownMember(<Set1>,<Set2[,Recursive])

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.  

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.  

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>])

drills to the members in a specified set that are present in a second specified set.

DrillupMember(<Set1>,<Set2>)

locates the difference between two sets and optionally retains duplicates.

Except(<Set1>,<Set2>[,All])

returns a set of tuples from extracted dimension elements.

Extract(<Set>,<Dimension>[,<Dimension>...])

returns the set that results from filtering a specified set that is based on a search condition.

Filter(<Set>,<Search Condition>)

applies a set to each member of another set and is joined to the resulting sets.

Generate(<Set1>,<Set2>[,All])

returns the first specified number of elements in a set.

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

orders the members of a set in a hierarchy.

Hierarchize(<Set>)

returns the intersection of two input sets and optionally retains duplicates.

Intersect(<Set1>,<Set2>[,All])

returns a set of members prior to and including a specified member.

LastPeriods(<Index>[,<Member>])

returns the set of members in a dimension, level, or hierarchy.

<Dimension>.Members

<Level>.Members

<Hierarchy>.Members

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>])

returns a set that contains a single member. The set is based on a string expression that contains a member name.

NameToSet(<Member Name>)

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>])

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.  

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>]])

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>])

takes an MDX set as input and returns a list of variable-length parameters. Functions that take variable-length parameter lists include the descriptive statistics functions (SUM(), MEAN(), PCTL(), etc.), COALESCE(), and CHOOSEC/N(). In addition, it takes the character parameters CAT/S/T/X() and COALESCEC().

Settolist(<Set>[,<Numeric Expression | Character Expression>])

returns the siblings of a specified member, including the member itself.

<Member>.Siblings

returns a set that is generated by removing calculated members from a specified set.

StripCalculatedMembers(<Set>)

returns a set that is constructed from a specified string expression in Multidimensional Expressions (MDX) format.

StrToSet (<String Expression>)

returns a subset of tuples from a specified set.

Subset(<Set>,<Start>[,<Count>])

returns a subset from the end of a set.

Tail(<Set>[,<Count>])

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.  

returns a specified number of items from the topmost members of a specified set.

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

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.  

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.  

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.  

returns a set that is generated by the union of two sets. Optionally, duplicate members are retained.

Union(<Set1>,<Set2>[,All])

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>)

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>])

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>])