DeepSee MDX Reference
%TOPMEMBERS
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

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 a DeepSee 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:
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 DeepSee 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