MDX Functions that Return a Set

The following MDX functions return sets. In Essbase, sets can contain members, tuples, or other sets.

The pure MDX set functions perform operations on sets, to return a set. The metadata set functions take information from the Essbase outline to return a set. The data set functions evaluate data in the cube to return a set.

Pure Set Functions

Functions in this category derive their results without getting any further information from the cube.

Table 4-25 MDX Pure Set Functions

Function	Result
CrossJoin	Returns a cross-section of two sets from different dimensions.
Distinct	Deletes duplicate tuples from a set.
Except	Returns a subset containing the differences between two sets.
Generate	For each tuple in set1, return set2.
Head	Returns the first n members or tuples present in a set.
Intersect	Returns the intersection of two input sets.
Subset	Returns a subset from a set, in which the subset is a numerically specified range of tuples.
Tail	Returns the last n members or tuples present in a set.
TupleRange	Returns the range of tuples between (and inclusive of) two tuples at the same level.
Union	Returns the union of two input sets.

Metadata-based Set Functions

Functions in this category derive their results using metadata information from the cube.

Table 4-26 MDX Metadata-based Set Functions

Function	Result
Ancestors	Returns a set of ancestors up to a specified layer or distance.
Attribute	Returns all base members that are associated with the specified attribute member.
Children	Returns all child members of the input member.
Descendants	Returns the set of descendants of a member at specified layers.
DrilldownByLayer	Drills down members of a set that are at a specified layer.
DrilldownMember	Drills down on any members or tuples of <set1> that are also found in <set2>.
DrillupByLayer	Drills up the members of a set that are below a specified layer.
DrillupMember	Tests two sets for common ancestors, and drills up members in the first set to the layer of the ancestors which are present in the second set.
Extract	Returns a subset containing only the tuples of a specified dimensionality.
Hierarchize	Sorts members according to the default member ordering as represented in the database outline.
LastPeriods	Returns a set of members ending either at the specified member or at the current member in the time dimension.
MemberRange	Returns the range of members positioned between two input members (inclusive) at the same generation or level.
Members	Returns a set of all members of a given dimension, hierarchy, or layer.
PeriodsToDate	Returns a set of dynamic-time-series members from the beginning of a given layer up to a given member in that layer (or up to the default member); or, returns members up to the current member of the Time dimension.
RelMemberRange	Returns a set based on the relative position of the specified member.
Siblings	Returns the siblings of the input member.
Uda	Returns all members that share a specified user-defined attribute.
WithAttr	Returns all base members that are associated with an attribute member of the specified type.
AttributeEx	Given the varying attribute member and the perspective setting, returns the associated base member list.
WithAttrEx	Given the varying attribute dimension, condition, predicate, and perspective setting, returns the base member list satisfying the predicate.
xTD	Functions returning period-to-date values.

Data-based Set Functions

Functions in this category derive their results using data values from the cube.

Table 4-27 MDX Data-based Set Functions

Function	Result
BottomCount	Returns a set of n elements ordered from smallest to largest, optionally based on an evaluation.
BottomPercent	Returns the smallest possible subset, with elements listed from smallest to largest, of a set for which the total results of a numeric evaluation are at least a given percentage.
BottomSum	Returns the smallest possible subset, with elements listed from smallest to largest, of a set for which the total results of a numeric evaluation are at least a given sum.
Case	Performs conditional expressions.
Filter	Returns those parts of a set which meet the criteria of a search condition.
IIF	Performs a conditional test, and returns an appropriate numeric expression or set depending on whether the test evaluates to true or false.
Leaves	Returns the set of level 0 (leaf) members that contribute to the value of the specified member.
Order	Sorts members of a set in order based on an expression.
TopCount	Returns a set of n elements ordered from largest to smallest, optionally based on an evaluation.
TopPercent	Returns the smallest possible subset, with elements listed from largest to smallest, of a set for which the total results of a numeric evaluation are at least a given percentage.
TopSum	Returns the smallest possible subset, with elements listed from largest to smallest, of a set for which the total results of a numeric evaluation are at least a given sum.