Group small values into an “other” category in a Reporting Services chart

Group small values into an “other” category in a Reporting Services chart

The following chart is a simple pie chart from the Adventure Works SSAS cube (Analysis Services) where I show the reseller’s gross profit by product in the United States.

As you can see, it’s trying to squeeze in all the products into the pie chart with very small numbers for a lot of products. But what to do when you are only interested in the products that are selling the best? So basically I would like to show the top x products and the rest grouped in a category called “other”.

This option is available in SQL Server Reporting Services, although in my opinion there’s a little extra that could be added to this functionality in SSRS to make it even better. I’ll show you:

In the chart, click on the colored bit to make the series properties active. In the title bar of the property window, you’ll see the name of the object as well as the type, which should say “Chart Series”. In the list of properties, expand the option CustomAttributes. Change the attribute CollectedStyle to for example SingleSlice. With the attribute CollectedThreshold you define the level of the grouping. For example, when you set this option to 5, every piece of the pie that is 5 percent or lower of the total value, will be collected in the group “Other”.

I have set my properties to the following values:

and here is the result when I run the report with these settings:

That looks a lot better, right?

Here are some variations to the different options in the attributes:
– Attribute CollectedStyle to CollectedPie: This creates another pie chart next to the original pie chart, where the details of the categories that are grouped in “Other” are shown.
– Attribute CollectedThresholdUsePercent: With this attribute, you define if you are going to use the threshold as a percentage of the total value, or an absolute value.

Most of the other options in the custom attributes are layout-related, therefore I won’t go into any of those.

I promised you before that I would share my thought about how this functionality could be improved a little better…well, what if I want to show the details of the top 5 selling products, and the rest of the products categorized in “other”? I can’t, because I have to set a threshold to whether a product is shown separately or if it’s included into “other”. It would be nice to have the option that let’s you choose a number of the top x you want to show and the rest in “other”.

5 thoughts on “Group small values into an “other” category in a Reporting Services chart

  1. Hi, I have not tested this with SQL 2005, currently I don’t have access to a 2005 instance, but if the properties are there, I would like to believe that it works then.
    Thanks, Erik

  2. For the CollectedStyle, SinglePie works great but some reason CollectedPie does not work and that’s really what I need. I tried in both 2008 and 2008 R2 with no luck. What I am doing wrong?

  3. Hi,
    So I have requirements to display the top X categories in the pie chart but you mentioned at time of posting SSRS does not have this feature. Just want to ask if anything has changed? I’m using SSRS 2008.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.