How To fix Top N Rank Tie Issue in SSRS Data Viz

Working with the Microsoft Services Report Builder can be finicky sometimes. Recently I was trying to create a report where I need to have the Top N filter display only 5 bars in the column chart.

However, the chart displayed 7 bars instead of 5 bars because there was a tie between the “Top 7” items in my data.

This is actually nice to have as it helps the report developer avoid overlooking data that could be relevant to the chart.

But there are situations where the client needs the Top 5 items and nothing more. For example, having 20 bars in a chart with dimensions meant for 5 bars will make the chart unreadable. So having exactly 5 bars is important for readability.

This article will show you how to fix the Top N rank tie issue where you can display the exact number of items you need to display in SSRS.

Dataset

If you want to practice along, you can get the dataset here.

Requirement

The requirement is to create a chart showing the Top 5 teams based on points acquired during the season.

Problem

The report returns 7 clubs that are tied at 50 points instead of the Top 5 clubs only.

Solution – How To fix Top N Rank Tie Issue in SSRS

The solution is to use an additional dimension/field to filter the initial Top N items in your data.

In my case, I will use the “GoalsDifference” field to filter my initial Top 5 results. Also, I will be using using “Top N” filter operator. If your category group appears in reverse order, use the “Bottom N” operator and adjust the sorting accordingly.

Here’s a step-by-step guide on how to accomplish the solution.

1. Select the Category Group > Category Group Properties

How To fix Top N Issue in SSRS

2. Select the Filters menu pane > Click Add to add a new filter

3. Set your additional dimension as Expression, set Operator as Top N, and set your Value as the same value used in your Top N filter.

how to fix top n rank tie issue in ssrs - solution 2

If you’re using a text field as your additional dimension/field and getting your item in reverse order, use the Bottom N operator.

4. Go to the Sorting menu on the left pane. Click the Add button to add a new sorting option. I set mine to the GoalDifference column and set the Order to Z-A (descending). Click Ok.

How To fix Top N Rank Tie Issue in SSRS

Conclusion – How To fix Top N Rank Tie Issue in SSRS

If you followed the steps above, you should be able to fix the Top N rank tie issue in SSRS without using code. Feel free to practice with other chart elements and parameters to meet your requirements.

Your final SSRS viz element should have only 5 items now.

Bonus

How would you achieve this ranking in SQL?

SELECT
[Team]
,[MatchPlayed]
,[Win]
,[GoalsDifference]
,[Points]
FROM [GrionicDB].[dbo].[PremierLeague]
ORDER BY Points desc, GoalsDifference desc

I hope this article was helpful.

You can download the project here.

Thanks for reading.