SSRS - Show only top category groups, excluding duplicate groups at the end in the chart
Consider the following table
I need to create a bar chart with Category Group = "Country". The chart should only display the top 3 groups based on the number of records for the country. I have already applied a filter for the category group, defining the Top N condition as 3 for Count (Country). The generated chart applies the filter as expected based on the count, but I only need to show 3 bars, even if there are bars with duplicate values.
Below is the diagram I am getting.
Expected Result
Now I know that I can create an additional column in my dataset with ranked values ββand then apply a filter on that column to get the expected result (I 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 simplified version of my dataset. I actually have a huge dataset with a lot of columns. The same dataset was used for different charts (with groupings in different columns).
source to share
This was an interesting question as I always just "solved" a tiebreak in a dataset without thinking. However, I see a pretty simple way to use the rnd () function to unroll links, if you don't care which of the linked countries is shown:
=(Count(Fields!Country.Value) * 1000) + (Rnd() * 100)
Which essentially just weighs each country's count by thousands, and then tie-breaks with a random small value:
New York: 3 0XX
France: 2 0XX
China: 1 0XX
Italy: 1 0XX
Singapore: 1 0XX
If you want to actually solve the alphabetical preference tiebreak, you can do something like this, but include a numerical value for the first letter of the country, etc.
source to share