Isolating the best N members in a set

Hierarchies can contain a lot of members. In this recipe, we are going to show you how to extract only the significant members: ones with the highest value for a certain measure.

This requirement is often necessary because not only does it allow end users to focus their efforts on a smaller set of members, but it also makes the queries much faster.

We will base our example on the TopCount() function, a function that returns the exact number of members as specified. In addition to that function, MDX has two more similar functions, namely TopPercent() and TopSum(). Contrary to the TopCount() function, these functions return an unknown number of members. In other words, they are designed to return a set of members based on their contribution, in a percentage or in an absolute value, respectively.

Further similarities and differences between TopCount(), TopSum(), and TopPercent() functions will be covered in later sections of this recipe.

Getting ready

Start...