Skip to main content

Set Expressions (MDX)

This section describes how to create and use set expressions in InterSystems MDX.

Details

The general syntax for a set expression is as follows:

{expression1, expression2, ...}

This list can include any number of items. In InterSystems 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 member expression.

  • A numeric expression or string expression.

  • 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.

  • A tuple expression.

    (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. InterSystems 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. InterSystems 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:

    $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.

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. The reference documentation 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.

FeedbackOpens in a new tab