SSRS Tips: Demystifying Top N Bottom N filter

The purpose of this post is to clear the confusion regarding the behavior of Top N & Bottom N filter option available in SQL Reporting Services (SSRS).
Couple of days back I came across a post in one of the public forum stating an issue with the behavior of  Top N filter in SSRS report. Fortunately it had the sample data to illustrate the issue. Though initial look seemed like there was indeed an issue,after doing some R & D and playing with it I realized that output it was giving was by design and it was not really an issue. I thought of explaining it via blog so as to avoid the confusion.
To illustrate the behavior, lets consider the below sample data

The data represents information of vehicle models along with their costs. The requirement was to do filtering on certain Makes and show the Top N values.
So we went ahead and created a sample report with above dataset. The code used was below

SELECT ‘Acura’ AS ‘Make’, ‘TL’ AS ‘Model’, 2014 AS ‘Year’ ,30000 AS ‘Cost’
UNION
SELECT ‘Acura’ AS ‘Make’, ‘TL’ AS ‘Model’, 2013 AS ‘Year’ ,25000 AS ‘Cost’
UNION
SELECT ‘BMW’ AS ‘Make’, ‘328’ AS ‘Model’, 2014 AS ‘Year’ ,35000 AS ‘Cost’
UNION
SELECT ‘BMW’ AS ‘Make’, ‘528’ AS ‘Model’, 2013 AS ‘Year’ ,50000 AS ‘Cost’
UNION
SELECT ‘BMW’ AS ‘Make’, ‘Z4’ AS ‘Model’, 2012 AS ‘Year’ ,40000 AS ‘Cost’
UNION
SELECT ‘BMW’ AS ‘Make’, ‘BMW i’ AS ‘Model’, 2014 AS ‘Year’ ,120000 AS ‘Cost’
UNION
SELECT ‘BMW’ AS ‘Make’, ‘X3’ AS ‘Model’, 2013 AS ‘Year’ ,40000 AS ‘Cost’
UNION
SELECT ‘Lexus’ AS ‘Make’, ‘ES 350’ AS ‘Model’, 2013 AS ‘Year’ ,45000 AS ‘Cost’
UNION
SELECT ‘Lexus’ AS ‘Make’, ‘RX’ AS ‘Model’, 2013 AS ‘Year’ ,50000 AS ‘Cost’
UNION
SELECT ‘Lexus’ AS ‘Make’, ‘CT’ AS ‘Model’, 2014 AS ‘Year’ ,32000 AS ‘Cost’
UNION
SELECT ‘Ford’ AS ‘Make’, ‘F-150’ AS ‘Model’, 2011 AS ‘Year’ ,25000 AS ‘Cost’
UNION
SELECT ‘Ford’ AS ‘Make’, ‘Escape’ AS ‘Model’, 2012 AS ‘Year’ ,25000 AS ‘Cost’
UNION
SELECT ‘Fors’ AS ‘Make’, ‘Fusion’ AS ‘Model’, 2013 AS ‘Year’ ,30000 AS ‘Cost’
Order By Make,Model

Now we added a tablix in the report and used data as below

If you check the output now you’ll find the output same as on the first screenshot. The default sorting applied on data is by Make and Model fields as per the ORDER BY columns used above.
Now as per the requirement, I added a filter to show only Acura, BMW data and applied Top 5 filter over it to show the first 5 values. The expectation was to get 2 rows of Acura followed by first 3 rows of BMW from the data above.The filter expressions used were the below

Now when I ran the report the output I got was the below
This was a bit surprising for me as I expected it to return first five rows (2 for Acura and next 3 for BMW).
I did some analysis and found out the reason. By default, the rows are retrieved from the dataset in the descending order (ie last record upwards). So it took the last 5 records after applying the filter on the Make field and displayed it in the output.
Then my assumption was if we make it Bottom N instead of Top N it should give me what I was looking for. I tweaked the second filter to make it Bottom N and execute the report and this is what I got as output.
This had me perplexed as I was expecting just 5 rows as output. I tried tweaking the N value couple of times  and checked and finally got the missing link. The reason why it gives 6 rows is because it returns all the records in case of a tie ie multiple records with the same value for the field on which you do the sorting. 
Just change N to 2 and you’ll get the below as output.
The moment you make it 3 or above it will find that it has a tie on next value ie BMW and hence it returns all the rows in the group.
See output for 3 which will same as the output for N = 5 above
Now try making make values unique as below and repeat the execution
SELECT ‘Acura1’ AS ‘Make’, ‘TL’ AS ‘Model’, 2014 AS ‘Year’ ,30000 AS ‘Cost’
UNION
SELECT ‘Acura2’ AS ‘Make’, ‘TL’ AS ‘Model’, 2013 AS ‘Year’ ,25000 AS ‘Cost’
UNION
SELECT ‘BMW1’ AS ‘Make’, ‘328’ AS ‘Model’, 2014 AS ‘Year’ ,35000 AS ‘Cost’
UNION
SELECT ‘BMW2’ AS ‘Make’, ‘528’ AS ‘Model’, 2013 AS ‘Year’ ,50000 AS ‘Cost’
UNION
SELECT ‘BMW3’ AS ‘Make’, ‘Z4’ AS ‘Model’, 2012 AS ‘Year’ ,40000 AS ‘Cost’
UNION
SELECT ‘BMW4’ AS ‘Make’, ‘BMW i’ AS ‘Model’, 2014 AS ‘Year’ ,120000 AS ‘Cost’
UNION
SELECT ‘BMW5’ AS ‘Make’, ‘X3’ AS ‘Model’, 2013 AS ‘Year’ ,40000 AS ‘Cost’
UNION
SELECT ‘Lexus1’ AS ‘Make’, ‘ES 350’ AS ‘Model’, 2013 AS ‘Year’ ,45000 AS ‘Cost’
UNION
SELECT ‘Lexus2’ AS ‘Make’, ‘RX’ AS ‘Model’, 2013 AS ‘Year’ ,50000 AS ‘Cost’
UNION
SELECT ‘Lexus3’ AS ‘Make’, ‘CT’ AS ‘Model’, 2014 AS ‘Year’ ,32000 AS ‘Cost’
UNION
SELECT ‘Ford1’ AS ‘Make’, ‘F-150’ AS ‘Model’, 2011 AS ‘Year’ ,25000 AS ‘Cost’
UNION
SELECT ‘Ford2’ AS ‘Make’, ‘Escape’ AS ‘Model’, 2012 AS ‘Year’ ,25000 AS ‘Cost’
UNION
SELECT ‘Fors’ AS ‘Make’, ‘Fusion’ AS ‘Model’, 2013 AS ‘Year’ ,30000 AS ‘Cost’
Order By Make,Model

And you’ll get below output for N = 3

Which clearly indicates that it was the tie in value which caused it to return those additional records.
 So for my actual sample data I had to get a unique combination of values for the sort to work on so as to avoid the duplicate rows on the output. So I went back and changed the Bottom N filter as per below to include the Make value too as the combination of Model + Make was unique.

And I got the desired output for Bottom 3 as below

So always remember these facts while working with Top N and Bottom N filters in SQL Reporting Services

  • The default sorting in the report would be in the descending order of the dataset output
  • The output will include all the value ties (duplicate values)  based on the fields used for sorting inside the filter. This is analogous to the TOP N WITH TIES functionality available in Transact SQL queries.

Hope this blog should save you some effort while working with Top N/Bottom N filters in SSRS by clarifying the functionality it provides.

Published by dataplatformcentral