%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
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
