%TOPMEMBERS (MDX)

Returned Type

This function returns a set of members.

Syntax and Details

level_expression.%TOPMEMBERS

Or:

hierarchy_expression.%TOPMEMBERS

Or:

dimension_expression.%TOPMEMBERS

Where:

• level_expression is an expression that returns a level. For example:

  [dimension_name].[hierarchy_name].[level_name]
  

  

• hierarchy_expression is an expression that returns a hierarchy. For example:

  [dimension_name].[hierarchy_name]
  

  

• dimension_expression is a dimension name, included within square brackets if needed (see Identifiers). For example:

  [dimension_name]
  

  

  The system interprets this as a reference to the first visible hierarchy within the given dimension.

Given a level name, this function is equivalent to the MEMBERS function.

Given a hierarchy name, this function returns a set that consists of the members of the first level defined in that hierarchy.

Given a dimension name, this function returns a set that consists of the members of the first level defined in the first visible hierarchy of this dimension.

The Analyzer uses this function when you drag and drop a dimension into the Rows or Columns. Specifically, when you drag and drop a dimension, the Analyzer uses the expression [dimension_name].[hierarchy_name].%TOPMMEMBERS, where hierarchy_name is the first hierarchy defined in the dimension.

Example

For example, consider the following cube contents:

BirthD
  H1
    Decade
    Year
    Period
    Date

The following query uses the %TOPMEMBERS function with H1 hierarchy (the only hierarchy in this case), so it retrieves all members of the Decade level:

SELECT birthd.%TOPMEMBERS ON 1 FROM patients
                              All Patients
 1 1910s                                 71
 2 1920s                                223
 3 1930s                                572
 4 1940s                                683
 5 1950s                              1,030
 6 1960s                              1,500
 7 1970s                              1,520
 8 1980s                              1,400
 9 1990s                              1,413
10 2000s                              1,433
11 2010s                                155

See Also

• MEMBERS