Identifying the best/worst members for each member of another hierarchy

Sales territory country and reseller are two different hierarchies in Adventure Works DW. To analyze sales, we might choose not to look at every reseller in every country. Instead, we often only need to look for the top or bottom N resellers per country.

In this case, we can view the country as the outer hierarchy, and the reseller as the inner hierarchy. Quite often, we need to analyze the combination of hierarchies in a way that the top or bottom N members of the inner hierarchy are displayed for each member of the outer hierarchy.

Displaying only the top or bottom N members of the inner hierarchy for each member of the outer hierarchy is sort of a report reduction, where we preserve the important combinations of members and leave out the rest of the cross join.

This recipe shows you how to create a TopCount() calculation to retrieve the top N resellers in each sales territory.

Getting ready

Start SQL Server Management...