Set Expressions
Details
The general syntax for a set expression is as follows:
{expression1, expression2, ...}
This list can include any number of items. In DeepSee MDX, if the list includes only one item, you can omit the curly braces.
In this list, expression1, expression2, and so on can have any of the following forms:
-
A range of members, specified as follows:
member1:member2
This expression returns a set that consists of the two given members and all members between them, given the order of members in the level that contains them. The members must belong to the same level.
For example:
birthd.year.1960:birthd.year.1980
For member2, you can omit the dimension, hierarchy, and level identifiers. For example:
birthd.year.1960:1980
An expression that uses an MDX function that returns a set, for example:
homed.zip.MEMBERS
Many MDX functions return sets, including MEMBERS, NONEMPTYCROSSJOIN, ORDER, and others.
Note that %TERMLIST can return a set.
Another set expression.
The name of a named set. See the following section.
(Note that in other implementations of MDX, for each tuple in a set, you must use the dimensions in the same order as in the other tuples in the set. For example, if the first tuple uses dimension A in its first list item, all the other tuples must do so as well. DeepSee MDX does not have this restriction. Similarly, in other implementations of MDX, a set cannot include a combination of tuples and other types of set elements. DeepSee MDX does not have this restriction either.)
A reference to a pivot variable that contains a set expression. To refer to a pivot variable, use the following syntax:
FeedbackOpens in a new tab$VARIABLE.variablename
Where variablename is the logical variable name. Do not enclose this expression with square brackets. This syntax is not case-sensitive; nor is the pivot variable name.
For information on defining pivot variables, see “Defining and Using Pivot Variables” in Using the DeepSee Analyzer.
You can precede any set expression with the keyword phrase NON EMPTY, for example:
NON EMPTY {birthd.year.1960:1980} NON EMPTY birthd.year.1960:1980 NON EMPTY {homed.zip.MEMBERS} NON EMPTY homed.zip.MEMBERS
The NON EMPTY keyword phrase suppresses empty elements of the set; the set is evaluated and then empty elements are removed. This keyword is particularly useful with CROSSJOIN and in scenarios where a filter can potentially cause elements to be null.
Named Sets
A named set consists of two elements: a set name and a set expression. You can define named sets in two ways:
-
Within the WITH clause of a query. The set name is available within the rest of the query, but is not available in other queries.
-
Within the CREATE SET statement. The set name is available within the rest of the session (for example, within the rest of the session in the MDX shell).
Uses
You can use set expressions in the following ways:
-
As a set argument of many MDX functions. Note that for some functions, the set must consist only of members. For other functions, the set must consist only of members or tuples. This book notes these requirements where needed.
-
As an axis in the SELECT statement.
-
As the definition of a named set, as described in the previous subsection.