I had a post, MDX #7 – Get only 10 elements from a MDX query, which showed an example of using the TopCount() function to get the top 10 customers who have the most Internet Sales Amount.

What can we do to get the top 5 resellers in each region/country?

Many of us probably know how to write a decent TSQL code to the top N records in a group (without using a cursor). What can we do in MDX query?

Try to get all the resellers in each country, and order them by the sales amount

Let’s first try to get all the resellers in each region/country, then worry about the top 5 next.

1. In order to make the Reseller Sales Amount show as a column, we used the measure [Measures].[Reseller Sales Amount] on the 0 axis.

2. In order to help to get the top 5 resellers in each country, let’s order the reseller first by creating a SET with the ORDER() function, [Measures].[Reseller Sales Amount] as the measure and BDESC flag to break the hierarchy.

3. In order to make all the region/countries AND the resellers show as rows, we used the CROSSJOIN of the dimension [Sales Territory].[Sales Territory Country] and the order set [Ordered Resellers].

Use Generate() function to evaluate Top 5 Resellers over a set of [Sales Territory Country]

Now we need to worry about how to only show the top 5 resellers in each region/country. TopCount() will be a good function to start with. In addition, we will also use the Generate() function to combine with the TopCount() function to get the top 5 in each country.

Here is the link to the reference of how the Generate function can be used: http://technet.microsoft.com/en-us/library/ms145526.

We are going to use the set expression syntax:

Generate( Set_Expression1 , Set_Expression2 [ , ALL ] )

The 3rd parameter ALL is optional.

In this example, we will only use the first 2 set parameters.

1. The first SET parameter are all the members from dimension [Sales Territory].[Sales Territory Country].

2. The 2nd SET parameter takes the dimension [Sales Territory].[Sales Territory Country] again, but with the CurrentMember function which returns the current member along the [Sales Territory Country] hierarchy during the iteration of each country. You can tell that the use of the CurrentMember  function is the key here.

3. The TopCount() function is straightforward. It returns the 5 top resellers (based on measure [Measures].[Reseller Sales Amount] ). Together with the CROSSJOIN with each country during the iteration, it produces what we exactly needed.

To make sure that we got the result correct, compare the above result with the result from our first query.