Member Expressions (MDX)

Details

In InterSystems MDX, a member expression can have any of the following forms:

• An member literal, which is an explicit reference to a single member by its name:

  [dimension_name].[hierarchy_name].[level_name].[member_name]
  

  Where:

  • [dimension_name] is an MDX identifier that names a dimension.

  • [hierarchy_name] is an MDX identifier that names a hierarchy within that dimension. You can omit the hierarchy name. If you do, the query uses the first level with the given name, as defined in this dimension.

    You can also omit the hierarchy name when you refer to a calculated member, because a calculated member is not in a hierarchy.

  • [level_name] is an MDX identifier that names a level within that hierarchy. You can omit the level name. If you do, the query uses the first member with the given name, as defined within this dimension.

    You can also omit the level name when you refer to a calculated member, because a calculated member is not in a level.

  • [member_name] is an MDX identifier that names a member of that level.

  For example:

  [gend].[h1].[gender].[female]
  

  

  For a measure, a member literal has the following variant form, because any measure is a member of a dimension called Measures. This dimension does not have a hierarchy or a level.

  [MEASURES].[measure_name]
  

  For example:

  [MEASURES].[%COUNT]
  

  

  Note that although a measure is a member, an expression like the preceding returns a scalar value and cannot be used in all the same ways as other member expressions.

• An explicit reference to a hierarchy as follows:

  [dimension_name].[hierarchy_name]
  

  This expression returns the All member of the dimension to which the hierarchy belongs.

  For example:

  aged.h1
  

  

  The preceding expression returns the All member of the AgeD dimension; in the sample Patients cube, the name of this member is All Patients.

• An expression that uses an MDX function to return a single member. For example:

  [gend].[h1].[gender].female.NEXTMEMBER
  

  Many MDX functions return members, including LAG, NEXTMEMBER, PARENT, and others.

  Note that %TERMLIST can return a member.

• An expression that uses the internal key of a member to return that member, via the following syntax:

  [dimension_name].[hierarchy_name].[level_name].&[member_key]
  

  Where member_key is the value of the KEY property of the member. For example:

  birthqd.h1.quarter.&[2]
  

  

  Note that member_key is a case-sensitive literal value rather than an expression. That is, you cannot replace it with a string-valued expression.

  For information on how KEY values are created, see the reference Intrinsic Properties. Also note that you can use the PROPERTIES function to find the value of the KEY property or any other property of a member.

• An expression that uses an InterSystems MDX extension to refer to a member in another cube, via the following syntax:

  [relationship_name].member_expression
  

  Where [relationship_name] is an MDX identifier that names a relationship in the cube used by the query and member_expression is a member expression suitable for that cube.

• A reference to a pivot variable that contains a member 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 in Using the Analyzer.

For example, the following member expressions are all equivalent:

[gend].[h1].[gender].Female
[gend].female
gend.H1.gender.female
gend.h1.FEMALE
gend.female

Calculated Members

Members can be defined within the cube definition, as part of the definition of a level. You can also create calculated members, which are typically based on other members. You can define calculated members in two ways:

• Within the WITH clause of a query. The member is available within the rest of the query, but is not available in other queries.

• Within the CREATE MEMBER statement. The member is available within the rest of the session (for example, within the rest of the session in the MDX shell).

Uses

You can use member expressions in the following ways:

• As a member argument to many MDX functions.

• As the argument to the WHERE clause.

• As an element of a set.