Search code examples
ssrs-2008ssrs-2008-r2

SSRS - How To Display Top N on Column chart

I have a column chart that has 40+ categories, what I want to accomplish is to display the top N on the column chart and combine the rest of the categories as "Other Category" so that it will fit in the column chart. I can set-up the top N in the filter properties of the data set, but I can't figure out how to combine the categories after the top 12.

enter image description here

Solution

  • You can combine the other categories if you add another field where you combine them, in a calculated field for example. Go to datasets (left on the report) and Right click on your dataset > Field properties > Fields > Add > Calculated Field. Now enter a field name (for example CategoriesUnder12) and in the expression write something like

    =IFF(Fields!Categorie.Value = "Sales" OrElse Fields!Categorie.Value = "Marketing", "CombindedCategories", Fields!Categorie.Value)
'Everything you want to combine comes in the OrElse clause

    Now you have all categories unter 12 combined in the categorie name CombindedCategories. If you group your chart now after CategoriesUnder12 you have the 12 top with their categorie names and everything below combined.