How to get MDX “top” and “bottom”

Often, you will want to get the top or bottom X number of dimension members based on the dimension hierarchy.  Within Analysis Services, you have the functions of TopCount, TopSum, and TopPercent (and the equivalent of Bottom functions) but these are based on a Measure.  And what you want is the top child or bottom child of a member based on the dimension order – not on the Measure order.
 
Well, to do this:
with
member [Measures].[test] as ‘1’
select {
  [Measures].[My Count]
} on columns, {
    topcount(descendants([Domain].[Domain Hierarchy], 2, LEAVES), 1),
    bottomcount(descendants([Domain].[Domain Hierarchy], 2, LEAVES), 1)
} on rows
from [Cube]

The trick for this is that:
The Descendants function will insure to get all of the members of the dimension hierarchy.  The two (2) is the number of levels you want to go down; you’ll want to specify what you want.

The problem in this case with TopCount and BottomCount (because in this case you’re looking for the SQL equivalent of top and bottom against the dimension) is that its based on a measure.  So you’ll notice the TopCount and BottomCount is applied against a measure I created ([Measures].[test]) which has a value of 1.

Since all of the values are 1, then the TopCount and BottomCount will simply go with the internal hierarchy which then is the equivalent of FirstChild.FirstChild and LastChild.LastChild in these cases respectively because I had specified 1 as the last parameter of TopCount/BottomCount.  You can specify a higher number to get the top and bottom 5 for example.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s