Learning
Community
Open Exchange
Global Masters
InterSystems IRIS Data Platform 2019.3 / Application Development / InterSystems MDX Reference / MDX Functions / %TOPMEMBERS
Previous section   Next section

%TOPMEMBERS

Returns a set of all members of the first level in the given hierarchy. Or, given a level, it returns a set of all the members of that level. This function is an InterSystems extension to 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

Previous section   Next section