MDX Set Value Expressions

The construct of the MDX set value expression is as follows:

Note

<index> denotes an integer argument. If an arbitrary <numeric_value_expression> appears here, it is truncated to the nearest integer.

<index> ::= <numeric_value_expression>

<percentage> ::= <numeric_value_expression>

<set_value_expression> ::= <dim_hier>.MEMBERS
                         | <level>.MEMBERS
                         | <member>.CHILDREN
                         | BOTTOMCOUNT(<set>, <index>
                           [, <numeric_value_expression>])
                         | BOTTOMPERCENT(<set>, <percentage>,
                           <numeric_value_expression>)
                         | BOTTOMSUM(<set>, <numeric_value_expression>,
                           <numeric_value_expression>)
                         | CROSSJOIN(<set>, <set>)
                         | DESCENDANTS(<member>, <level> [,<desc_flags>])

Note

In the absence of explicit <desc_flags> specification, SELF is the default.

                         | DISTINCT(<set>)
                         | DRILLDOWNLEVEL(<set> [, <level>])
                         | DRILLDOWNLEVELBOTTOM(<set>, <index>
                           [,[<level>], <numeric_value_expression>])
                         | DRILLDOWNLEVELTOP(<set>, <index>[, [<level>]
                           , <numeric_value_expression>])
                         | DRILLDOWNMEMBER(<set>, <set>[, RECURSIVE])
                         | DRILLDOWNMEMBERBOTTOM(<set>, <set>, <index>
                           [, <numeric_value_expression>], RECURSIVE])
                         | DRILLDOWNMEMBERTOP(<set>, <set>, <index>
                           [, [<numeric_value_expression>], RECURSIVE])
                         | DRILLUPLEVEL(<set>[, <level>])
                         | DRILLUPMEMBER(<set>, <set>)
                         | EXCEPT(<set>, <set> [, [ALL]])
                         | EXTRACT(<set>, <dim_hier>[, <dim_hier>...])
                         | FILTER(<set>, <search_condition>)
                         | GENERATE(<set>, <set> [, [ALL]])
                         | HIERARCHIZE(<set>)
                         | INTERSECT(<set>, <set> [, [ALL]])
                         | LASTPERIODS(<index> [, <member>])
                         | MTD([<member>])
                         | ORDER(<set>, <value_expression>
                           [, ASC | DESC | BASC | BDESC])

Note

In the absence of explicit specification, ASC is the default.

                         | PERIODSTODATE([<level>[, <member>]])
                         | QTD([<member>])
                         | TOGGLEDRILLSTATE(<set1>, <set2>[, RECURSIVE])

Note

With the exception of CROSSJOIN, all set functions that take more than one <set> argument require that the two <set> arguments have tuples of the same dimensionality.

                         | TOPCOUNT(<set>, <index>
                           [, <numeric_value_expression>])
                         | TOPPERCENT(<set>, <percentage>,
                           <numeric_value_expression>)
                         | TOPSUM(<set>, <numeric_value_expression>,
                           <numeric_value_expression>)
                         | UNION(<set>, <set> [, [ALL]])
                         | WTD([<member>])
                         | YTD(<member>)

Note

The following set functions require that their <set> arguments be formed from a single dimension, not the result of a CROSSJOIN: DRILLDOWNMEMBER, DRILLUPMEMBER, DRILLDOWNMEMBERTOP, DRILLUPMEMBERTOP, DRILLDOWNMEMBERBOTTOM, DRILLDOWNLEVEL, DRILLDOWNLEVELTOP, DRILLDOWNLEVELBOTTOM, DRILLUPLEVEL, and TOGGLEDRILLSTATE.

<desc_flags> ::= SELF
               | AFTER
               | BEFORE
               | BEFORE_AND_AFTER
               | SELF_AND_AFTER
               | SELF_AND_BEFORE
               | SELF_BEFORE_AFTER

Set value expressions are described in Set Value Expressions.