Previously I wrote a blog on Top N and Bottom N reporting. There were a couple of gotcha I did not cover in that blog so I thought I would cover those here to answer some of the questions I have received. The other blog can be found here.

On a report you are trying to group the top N number of rows together and all others should be in a separate group. Let’s say you want the top 5 sales people based on amount.

In my other blog I showed how to use the Top N and Bottom N filters on groups. The major problem comes in when you have duplicate amounts at the cut off. For example if you want the top 5 sales employees to be in one group based on their sales amount, and three employees tie for the number 5 slot. This would cause more than 5 people to show in the top N group and those same people would show in the bottom N using the technique I showed in the previous blog.

So how do you fix this? You will need a different method to do this. You need to find the cut off amount and spilt the top from the bottom based on that amount.

Here is the table we will use on the report. Notice that positions 4, 5, and 6 have the same amount. If you set the filter on the report to top 5 you will get 6 rows in the top. The Bottom N method I showed in the previous blog will fail because the bottom group will not know about the extra rows in the top group. This will cause row 6 to show in both groups.

A better way to do this Top N and Bottom N grouping would be to find the cut off amount and filter the groups based on this.

Here is the main query pulling the data to show on the table.

SELECT RowNum, Name, Amount

FROM TopN

Now you need to create a new data set and the query will look like below

With O as(

Select top (@N) t.RowNum, t.Amount, t.Name

From TopN t

Order by Amount desc

)

Select Top 1 Amount

From O

Order by Amount

I named this dataset MinTopAmount. This query is using a CTE to get the top N amounts. N is a parameter on the report, you can hard code this if desired. Then the outside query gets the lowest amount from that query. This gives you the lowest amount to show in the top N group. This query will only return single row and column with the cut off amount.

Now you need to set up the filters on the groups to filter based on the amount instead of Top or Bottom. These are two adjacent groups on a single table.

The Sum of the Amount value form the new dataset, which is named MinTopAmount in this example, should not sum anything because the query only returns one row due to the top 1 in the query with the CTE. The bottom group will have the exact same filter except it will use less than instead of greater than or equal to.

With the N set to 5 it will divide based on the amount at the fifth spot. If there is a tie, it won’t matter.

In this image you can see the bottom group in blue. Notice even though we selected Top 5 we get 6 rows in the top due to the tie, everyone else is in the bottom group.

If you have any issues let me know.