Browsed by
Month: June 2010

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”.

Welcome to my blog!

Welcome to my blog!

Welcome to my blog! The purpose of this blog is to publish information that I have been collected over the past years to make life easier in the world of Microsoft’s BI platform. It has occurred to me many times that I asked myself the question “Now how did I do this-and-this again?”. After some extensive searches on Google I usually found an answer, but I thought to myself why not collect this information and share it with the rest of the world? The posts will be a mixture of information in the area of Integration Services (SSIS), Analysis Services (SSAS) and Reporting Services (SSRS).

Now that release 243 of Exact Synergy is available, I hope that a lot of people are going to use the integration of Reporting Services. Therefore I will be posting tips and tricks about the integration of Exact Synergy and Reporting Services as well.
Other than that, I’ll be posting stuff that is indirectly related to the MS BI platform, for example on the topic of SQL.
Happy reading!
Erik