SSRS - Display only Top N Category Groups excluding duplicate groups at the end in Bar Chart

5
votes

Consider the following table

enter image description here

I need to generate a bar chart with the Category Group = "Country". The chart should only display the top 3 Groups based on the count of records for a country. I have already applied a filter for the Category Group specifying the Top N Condition as 3 for Count(Country). The chart generated, applies the filter as expected based on count, but i need only 3 bars to be shown even if there are bars with duplicate values.

Below is the chart that I get.

enter image description here

Expected Result

enter image description here

Now I know that, i can create an additional column in my dataset with ranking values and then apply a filter on this column to get the expected result (i have tried this, and it works)

Is there a way to achieve the expected result without changing the underlying Dataset?

Note: The dataset shown above is a highly simplified version of my dataset. In reality i have a huge dataset with a lot of columns. The same dataset has been used for various charts (with groupings on different columns).

1
sql-serverreporting-servicesbusiness-intelligencesql-server-2016ssrs-2016

1 Answers

3
votes

This was an interesting question as I've always just "solved" the tiebreaker in the dataset without much thought. However, I do see a fairly easy way to use the rnd() function to dissolve the ties as long as you don't care which of the tied countries is shown:

=(Count(Fields!Country.Value) * 1000) + (Rnd() * 100)

Which essentially just weights the count per country into the thousands and then tiebreaks with a random small value:

New York: 30XX

France: 20XX

China: 10XX

Italy: 10XX

Singapore: 10XX

If you wanted to actually solve the tiebreaker with an alphabetical preference, you could do something similar but incorporate the numeric value for the first letter of the country etc...