Grammar Rules

The following is a comprehensive view of the syntax for MDX in Analytic Services.

In this document, the syntax for MDX is illustrated using BNF notation.

[<with_section>]
SELECT [<axis_specification>
       [, <axis_specification>...]]
[FROM [<cube_specification>]]
[WHERE [<slicer_specification>]]

<cube_specification> ::= 
       '[' <ident_or_string>.<ident_or_string> ']'
     | <delim_ident>.<delim_ident>

<delim_ident> ::= 
       '[' <ident> ']' 
     | <ident_or_string>

<ident_or_string> ::=  
       ' <ident> ' 
     | <ident>

Note: <ident> refers to a valid Analytic Services application/database name. In the cube specification, if there are two identifiers, the first one should be application name and the second one should be database name. For example, all of the following are valid identifiers:

<axis_specification> ::= 
        [NON EMPTY] <set> [<dim_props>] ON 
        COLUMNS | ROWS | PAGES | CHAPTERS | 
        SECTIONS | AXIS (<unsigned_integer>)

<dim_props> ::= 
        [DIMENSION] PROPERTIES <property> [, <property>...]

<slicer_specification> ::= <set> | <tuple> | <member>

Note: The cardinality of the <set> in the slicer should be 1.

<member> ::= 
        <member-name-specification>
      | <member_value_expression>

<member-name-specification> ::=

A member name can be specified in the following ways:

  1. By specifying the actual name or the alias; for example, Cola, Actual, COGS, and [100].

    If the member name starts with number or contains spaces, it should be within braces; for example, [100]. Braces are recommended for all member names, for clarity and code readability.

    For attribute members, the long name (qualified to uniquely identify the member) should be used; for example, [Ounces_12] instead of just [12].

  2. By specifying dimension name or any one of the ancestor member names as a prefix to the member name; for example, [Product].[100-10] and [Diet].[100-10] This is a recommended practice for all member names, as it eliminates ambiguity and enables you to refer accurately to shared members.

    Note: Use only one ancestor in the qualification. Analytic Services returns an error if multiple ancestors are included. For example, [Market].[New York] is a valid name for New York, and so is [East].[New York]. However, [Market].[East].[New York] returns an error.

  3. By specifying the name of a calculated member defined in the WITH section.
<member_value_expression> ::=
        Parent ( <member> ) 
           | <member>.Parent         
      | FirstChild ( <member> ) 
           | <member>.FirstChild     
      | LastChild ( <member> ) 
           | <member>.LastChild      
      | PrevMember ( <member> [,<layertype>] )
           | <member>.PrevMember [( <layertype> ) ] 
      | NextMember ( <member> [,<layertype>] )
           | <member>.NextMember [( <layertype> ) ] 
      | FirstSibling ( <member> )
           | <member>.FirstSibling   
      | LastSibling ( <member> )
           | <member>.LastSibling    
      | Ancestor ( <member> , <layer> | <index> )
      | Lead ( <member>, <index> [,<layertype>] )
           | <member>.Lead ( <index> [,<layertype>] )   
      | Lag  ( <member>, <index> [,<layertype>] )
           | <member>.Lag  ( <index> [,<layertype>] )   
      | CurrentMember ( <dim_hier> )
           | <dim_hier>. CurrentMember  
      | DefaultMember ( <dim_hier> )
           | <dim_hier>. DefaultMember  
      | OpeningPeriod ( [<layer> [,<member>]] )
      | ClosingPeriod ( [<layer> [,<member>]] )
      | Cousin (<member>, <member>)
      | ParallelPeriod( [<layer>[, <index>[, <member>]]] )
      | Item ( <tuple>, <index> ) 
           | tuple[.Item] ( <index> ) 

<dim_hier> ::= <dimension>

<dimension> :: = 
        <dimension-name-specification>
      | DIMENSION ( <member> | <layer> )
           | <member>.DIMENSION  
           | <layer>.DIMENSION

<dimension-name-specification> ::= 
    Same as <member_name-specification> case 1. 
    e.g. Product, [Product]

<layertype> ::= 
        GENERATION | LEVEL

<layer> ::= 
        <layer-name-specification>
      | Levels ( <dim_hier>, <index> )
           | <dim_hier>.Levels ( <index> )      
      | Generations ( <dim_hier>, <index> )
           | <dim_hier>.Generations ( <index> ) 
      | <member>.Generation
      | <member>.Level

<layer-name-specification> ::= 

A layer name can be specified in the following ways:

  1. By specifying the generation or level names; for example, States or Regions.

    The generation or level name can be within braces; for example, [Regions]. Using braces is recommended.

  2. By specifying the dimension name along with the generation or level name; for example, Market.Regions and [Market].[States] This naming convention is recommended.
<tuple> ::= 
        <member>
      | ( <member> [,<member>].. )
      | <tuple_value_expression>

A tuple is a collection of member(s) with the restriction that no two members can be from the same dimension. For example, (Actual, Sales) is a tuple. (Actual, Budget) is not a tuple, as both members are from the same dimension.

<tuple_value_expression> ::= 
        CurrentTuple ( <set> )
           | <set>.Current        
      | Item ( <set>, <index> )
           | <set>[.Item] (<index>) 

A set is a collection of tuples where members in all tuples must be from the same dimensions and in the same order.

For example, {(Actual, Sales), (Budget, COGS)} is a set.

{(Actual, Sales), (COGS, [100])} is not a set because the second tuple has members from Scenario and Product dimensions, whereas the first tuple has members from Scenario and Measures dimensions.

{(Actual, Sales). (COGS, Budget)} is not a set because the second tuple has members from Scenario and Measures dimensions, whereas the first tuple has members from Measures and Scenario dimensions (the order of dimensions is different).

<set> ::= 
        MemberRange ( <member>, <member> [,<layertype>] )
           | <member> : <member>  
      | { <tuple>|<set> [, <tuple>|<set>].. }
      | ( <set> )
      | <set_value_expression>

<set_value_expression> ::=
      | Members ( <dim_hier> )
           | <dim_hier>.Members 
      | Members ( <layer> )
           | <layer>.Members   
      | Children ( <member> )
           | <member>.Children  
      | CrossJoin ( <set> , <set> )
      | Union ( <set> , <set> [,ALL] )
      | Intersect ( <set> , <set> [,ALL])
      | Except ( <set> , <set> [,ALL])
      | Extract ( <set> , <dim_hier> [, <dim_hier>].. )
      | Head ( <set> [, <index>])
      | Subset ( <set> , <index> [,index])
      | Tail ( <set> [,index])
      | Distinct ( <set> )
      | Siblings ( <member> ) 
           | <member>.Siblings
      | Descendants ( <member> , [{<layer>|<index>}[, <Desc_flags>]] )
      | PeriodsToDate ( [<layer>[, <member>]] )
      | LastPeriods ( <index>[, <member>] )
      | xTD ( [<member>] )  
            where xTD could be {HTD|YTD|STD|PTD|QTD|MTD|WTD|DTD}
      | Hierarchize ( <set> [,POST] )
      | Filter ( <set> , <search_condition> )
      | Order ( <set>, <value_expression> [,BASC | BDESC] )
      | TopCount ( <set> , <index> [,<numeric_value_expression>] )
      | BottomCount ( <set> , <index> [,<numeric_value_expression>] )
      | TopSum ( <set> , <numeric_value_expression> 
                       , <numeric_value_expression> )
      | BottomSum ( <set> , <numeric_value_expression> 
                          , <numeric_value_expression> )
      | TopPercent ( <set> , <percentage> , <numeric_value_expression> ) 
      | BottomPercent ( <set> , <percentage> , <numeric_value_expression> )
      | Generate ( <set> , <set> [, [ALL]] )
      | DrilldownMember ( <set> , <set>[, RECURSIVE] )
      | DrillupMember ( <set> , <set> )
      | DrilldownByLayer ( <set> [, {<layer>|<index>] )
           | DrilldownLevel ( <set> [, {<layer>|<index>] ) 
      | DrillupByLayer ( <set> [, <layer>] )
           | DrillupLevel ( <set>[, <layer>] ) 
      | WithAttr ( <member> , <character_string_literal> 
                 , {<signed_numeric_literal> |<character_string_literal>} )
      | Attribute ( <member> )
      | Uda ( <dimension> | <member> , <character_string_literal> )
      | RelMemberRange ( <member>, <prevcount>, <nextcount>, [,<layertype>] )
      | Ancestors ( <member>, <layer>|<index> )
      | <conditional_expression>

Note: <conditional_expression> is expected to return a <set> in the above production.

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

<value_expression> ::= 
        <numeric_value_expression>
      | <string_value_expr>
 
<numeric_value_expression> ::=
        <term>
      | <numeric_value_expression> + <term>
      | <numeric_value_expression> - <term>

<term> ::=
        <factor>
      | <term> * <factor>
      | <term> / <factor>

<factor> ::= 
        [+ | -]<numeric_primary>

<numeric_primary> ::=
        <value_expr_primary>
      | <numeric_value_function>
      | <mathematical_function>

Note: The data type of <value_expr_primary> in the above production must be numeric.

<base> ::= 
        <numeric_value_expression>
<power> ::= 
        <numeric_value_expression>

<mathematical_function> ::= 
        Abs ( <numeric_value_expression> )
      | Exp ( <numeric_value_expression> )
      | Factorial ( <index> )
      | Int ( <numeric_value_expression> )
      | Ln ( <numeric_value_expression> )
      | Log ( <numeric_value_expression> [, <base>] )
      | Log10 ( <numeric_value_expression> )
      | Mod ( <numeric_value_expression> , <numeric_value_expression> )
      | Power ( <numeric_value_expression> , <power> )
      | Remainder ( <numeric_value_expression> )
      | Round ( <numeric_value_expression> , <index> )
      | Truncate ( <numeric_value_expression> )
 
<string_value_expression> ::= <value_expr_primary>

<value_expr_primary> ::=
        <unsigned_numeric_literal> 
      | <character_string_literal>
      | ( <numeric_value_expression> )
      | <tuple> [.Value]
      | <member>[.Value]
      | <property>
      | <conditional_expression>
      | MISSING

Notes

<conditional_expression> ::= 
        <if_expression> 
      | <case_expression> 
      | CoalesceEmpty ( <numeric_value_expression>
                      , <numeric_value_expression>)

<case_expression> ::=   
        <simple_case> | <searched_case>

<if_expression> ::= 
        IIF ( <search_condition>, <true_part>, <false_part> )
<true_part> ::= 
        <numeric_value_expression>  | <set>
<false_part> ::= 
        <numeric_value_expression> | <set>

<simple_case> ::= 
        CASE <case_operand>
             <simple_when_clause>...
             [ <else_clause> ]
        END

<simple_when_clause> ::= 
        WHEN <when_operand> 
             THEN <result>
<else_clause> ::= 
        ELSE <numeric_value_expression> | <set>

<case_operand> ::= 
        <value_expression>
<when_operand> ::= 
        <value_expression>
<result> ::= 
        <numeric_value_expression> | <set>

<searched_case> ::= 
        CASE
             <searched_when_clause>...
             [ <else_clause> ]
        END

<searched_when_clause> ::= 
        WHEN <search_condition> 
             THEN <result>

<numeric_value_function> ::=
        Avg ( <set> [, <numeric_value_expression>] [, IncludeEmpty] )
      | Max ( <set> [, <numeric_value_expression>])
      | Min ( <set> [, <numeric_value_expression>])
      | Sum ( <set> [, <numeric_value_expression>])
      | NonEmptyCount ( <set> [, <numeric_value_expression>])
      | Count ( <set> [, IncludeEmpty] )
      | ToDate ( <character_string_literal> , <character_string_literal> )
      | Ordinal (<layer>)


<index> ::= 
        <numeric_value_expression>
<percentage> ::= 
        <numeric_value_expression>
 
<search_condition> ::=
        <bool_term>
      | <search_condition> OR <bool_term>

<bool_term> ::=
        <bool_factor>
      | <bool_term> AND <bool_factor>

<bool_factor> ::=
        <bool_primary>
      | NOT <bool_primary>

<bool_primary> ::= 
        <value_expression> [=|>|<|<>|>=|<=] <value_expression>
      | <property> IN <member>|<character_string_literal>
      | <property>
      | IsEmpty ( <value_expression> )
      | ( <search_condition> )
      | IsSibling(<member>,<member>)
      | IsLeaf(<member>)
      | IsGeneration(<member>,<index>)
      | IsLevel(<member>,<index>)
      | IsAncestor(<member>,<member>)
      | IsChild(<member>,<member>)
      | IsUda (<member>, <character_string_literal>)
      | IsAccType (<member>, <AcctTag>)
      | Is ( <member> , <member> ) 
           | <member> Is <member>
      | IsValid (<member> | <tuple> | <set>  | <layer> | <property>)

Note: Only properties with boolean values can be used as <bool_primary>.

<AcctTag> ::= 
        FIRST 
      | LAST 
      | AVERAGE
      | EXPENSE
      | TWO-PASS

<with_section> ::=
        WITH <frml_spec>

<frml_spec> ::=
        <single_frml_spec>
      | <frml_spec> <single_frml_spec>

<single_frml_spec> ::=
        <set_spec>
      | <member_specification>

<set_spec> ::=
        SET <set_name> AS ' <set> '

<set_name> ::=

The name of the set to be defined. The name cannot be same as any names/aliases of database members, generation/level names, or UDA names.

<member_specification> ::= 
        MEMBER <member_name> AS ' <numeric_value_expression> '
        [, <solve_order_specification>]

<member_name> ::= 
        <dimension-name-specification>.<calculated member name>

<calculated member name> ::= 

Names used for calculated members cannot be the same as any names/aliases of database members, generation/level names, or UDA names.

<solve_order_specification> ::= 
        SOLVE_ORDER = <unsigned_integer>
 
<property> ::= 
        <member>.<property_specification>
      | <dim_hier>.<property_specification>
      | <property_specification>

Note: The last two alternatives in the above rule can be used only inside the DIMENSION PROPERTIES section.

For example, assume an axis has 2 dimensions, Product and Market. Using DIMENSION PROPERTIES Gen_number, [Product].level_number, the generation number will be present in the output for the members of both dimensions, whereas the level number will be present only for the members of the Product dimension.

Within a value expression, [Product].Gen_number refers to the generation number of the member named [Product].

[Product].CurrentMember.Gen_number refers to the generation number of the current member of the [Product] dimension.

For example,

Filter ([Product].Members, [Product].Gen_number >  1)
returns an empty set. Product.Generation is 1, so the search condition fails for each tuple of [Product].Members.
Filter ([Product].Members, [Product].CurrentMember.Gen_number >  1) 
returns all members of Product dimension except the top dimension member, [Product].
<property_specification> ::= 
        MEMBER_NAME
      | MEMBER_ALIAS
      | GEN_NUMBER
      | LEVEL_NUMBER
      | <dimension-name-specification>
      | <uda-specification>

Note: The <dimension-name-specification> in <property_specification> should be an attribute dimension-name specification. The attribute dimension names are treated as properties of members from their corresponding base dimensions.

<uda-specification> ::=

The <uda-specification> specifies a User Defined Attribute(UDA). UDA properties are Boolean-valued properties. A TRUE value indicates presence of a UDA for a member. For example,

Filter (Market.Members, Market.CurrentMember.[Major Market])
returns the Market dimension members tagged with "Major Market" UDA in the outline.

For more discussion of properties, see About MDX Properties.

<signed_numeric_literal> ::= 
        [+|-] <unsigned_numeric_literal>

<unsigned_numeric_literal> ::= 
        <exact_numeric_literal>
      | <approximate_numeric_literal>

<exact_numeric_literal> ::= 
        <unsigned_integer>[.<unsigned_integer>]
      | <unsigned_integer>.
      | .<unsigned_integer>

<unsigned_integer> ::= 
        {<digit>}...

<approximate_numeric_literal> ::= 
        <mantissa>E<exponent>

<mantissa> ::= 
        < exact_numeric_literal>

<exponent> ::= 
       [<sign>]<unsigned_integer>

<digit> ::= 
       0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

Note: Numbers can also be input in scientific notation (mantissa/exponent), using the E character.

<character_string_literal> ::= 
       <quote>[<character_representation>...] <quote>

<character_representation> ::= 
       <nonquote_character> 
     | <quote_symbol>

<nonquote_character> ::= 
       Any character in the character set other than <quote>

<quote_symbol> ::= 
       <quote> <quote>

<quote>  ::= "

©2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com