Export (0) Print
Expand All
Avg
IIf
Lag
Max
Min
Mtd
Qtd
Sum
Var
Wtd
Ytd
Expand Minimize
1 out of 4 rated this helpful - Rate this topic

TopCount (MDX)

Updated: 17 July 2006

Sorts a set in descending order and returns the specified number of elements with the highest values.


TopCount(Set_Expression,Count [ ,Numeric_Expression ] )
Set_Expression

A valid Multidimensional Expressions (MDX) expression that returns a set.

Count

A valid numeric expression that specifies the number of tuples to be returned.

Numeric_Expression

A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number.

If a numeric expression is specified, the TopCount function sorts, in descending order, the tuples in the set specified by the specified set according to the value specified by the numeric expression, as evaluated over the specified set. After sorting the set, the TopCount function then returns the specified number of tuples with the highest value.

ms144792.note(en-US,SQL.90).gifImportant:
Like the BottomCount function, the TopCount function always breaks the hierarchy.

If a numeric expression is not specified, the function returns the set of members in natural order, without any sorting, behaving like the Head (MDX) function.

Example

The following example returns, for the Bike category, the first five members in the set of members of the City level in the Geography hierarchy in the Geography dimension for fiscal year 2003 ordered by the Reseller Sales Amount measure (beginning with the members of this set with the largest number of sales).

SELECT TopCount
   ({[Geography].[Geography].[City].Members 
      *[Date].[Fiscal].[Fiscal Year].[FY 2003]}
   , 5
   , [Measures].[Reseller Sales Amount]
   ) ON 0,
   [Product].[Product Categories].Bikes ON 1
FROM [Adventure Works]

Release History

17 July 2006

Changed content:
  • Updated syntax and arguments to improve clarity.
  • Added updated examples.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.