MDX Set Functions
Set functions in Multidimensional Expressions (MDX) are used to manipulate sets of members or tuples. They allow you to create, modify, filter, and combine sets, which are fundamental to constructing complex MDX queries.
Common Set Functions
Set Operations
These functions perform set operations similar to those in relational algebra.
Description:
Returns a set containing all unique members or tuples from the specified sets.
Syntax:
UNION( { [USA], [Canada] }, { [Mexico] } )Example:
Returns a set of countries including USA, Canada, and Mexico.
Description:
Returns a set of members or tuples that exist in both specified sets.
Syntax:
INTERSECT( { [Product].[Bikes], [Product].[Components] }, { [Product].[Bikes], [Product].[Accessories] } )Example:
Returns a set containing only [Product].[Bikes].
Description:
Returns a set of members or tuples from the first set that are not present in the second set.
Syntax:
EXCEPT( { [Customer].[All Customers].[USA], [Customer].[All Customers].[Canada] }, { [Customer].[All Customers].[USA] } )Example:
Returns a set containing only [Customer].[All Customers].[Canada].
Set Modification and Creation
These functions help in creating or modifying sets based on existing ones or based on conditions.
Description:
Returns a set of tuples that have a non-empty value in the cube.
Syntax:
NONEMPTY( { [Measures].[Sales Amount] * [Product].[Category].Members } )Example:
Returns a set of product categories that have sales.
Description:
Returns a subset of the original set that satisfies the specified condition.
Syntax:
FILTER( { [Product].[Product Name].Members }, [Measures].[Sales Amount] > 10000 )Example:
Returns a set of product names where the sales amount is greater than 10,000.
Description:
Returns a set ordered based on a specified numeric expression.
Syntax:
ORDER( { [Product].[Product Name].Members }, [Measures].[Sales Amount], BDESC )Example:
Returns a set of product names ordered by sales amount in descending order.
Description:
Returns the first Count members of a set. If Count is omitted, it defaults to 1.
Syntax:
HEAD( { [Product].[Product Name].Members }, 5 )Example:
Returns the first 5 product names from the set.
Description:
Returns the last Count members of a set. If Count is omitted, it defaults to 1.
Syntax:
TAIL( { [Product].[Product Name].Members }, 3 )Example:
Returns the last 3 product names from the set.
Set Navigational Functions
These functions help in navigating the hierarchy and retrieving related members.
Description:
Returns a set of ancestors for a given member up to a specified level.
Syntax:
ANCESTORS( [Product].[Bikes], [Product].[Category] )Example:
Returns the ancestor category of the 'Bikes' product.
Description:
Returns a set of direct children of a given member.
Syntax:
CHILDREN( [Product].[Bikes] )Example:
Returns all subcategories or products directly under 'Bikes'.
Description:
Returns a set of all members at a specified level in a hierarchy.
Syntax:
MEMBERS( [Product].[Category], 1 )Example:
Returns all members at the first level of the Product hierarchy (e.g., top-level categories).
Understanding and effectively using these set functions is crucial for writing powerful and flexible MDX queries.