Syntax and Details
level_expression is an expression that returns a level. For example:
hierarchy_expression is an expression that returns a hierarchy. For example:
dimension_expression is a dimension name, included within square brackets if needed (see Identifiers). For example:
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.
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