Displaying few important members, others as a single row, and the total at the end
There are times when isolating the best or worst members is not enough. In addition to the few important members, business users often want to see the total of all other not-so-important members, and also a single row representing the total of all the members.
An example of this type of reporting requirement is shown in the following table:
|
Reseller Sales Amount | |
|---|---|
|
Top 1st Reseller |
$877,107.19 |
|
Top 2nd Reseller |
$853,849.18 |
|
Top 3rd Reseller |
$841,908.77 |
|
Top 4th Reseller |
$816,755.58 |
|
Top 5th Reseller |
$799,277.90 |
|
Other Resellers |
$76,261,698.37 |
|
All Resellers |
$80,450,596.98 |
In the first recipe in this chapter, Isolating the best N members in a set, we learned how to isolate the best members using the TopCount() function. The challenge in this recipe is to get only one row for the total of all Other Resellers, and only one row for the total of All Resellers. We will also need to make sure that the Top N...
