BLOG

Pie chart with ‘others’ category (collected data)

25.04.2010 Hilmar Buchta

SQL Server 2005 | SQL Server 2008

Pie chart with too many categories don’t make much sense. The following screenshot shows the order count from the AdventureWorks OLAP database by subcategory (no selection on date here for this example) as a pie chart:

image

Fortunately, SQL Server 2008 Reporting Services adds a feature to collect all slices below a certain threshold (either as a fixed value or as a percentage) as shown below:

image

To get this result you have to check the custom attributes (properties) of the chart series (either by selecting the pie itself or by choosing the chart series in the property box picker)

image

In our example I collected all slices with a value of less than 3% to one single slice with the name ‘Other’. You can even show the other values as an exploded pie chart (although I think it’s more confusing).

In cases where you want to show just a certain number of slices (instead of using a threshold) or if you are using Reporting Services 2005 which doesn’t support the collected slice, you may do the collection by MDX:

 

  1. WITH
  2.   SET SelectedSubCategories AS
  3.     TopCount
  4.     (
  5.       Order
  6.       (
  7.         [Product].[Subcategory].[Subcategory]
  8.        ,[Measures].[Order Count]
  9.        ,DESC
  10.       )
  11.      ,10
  12.     )
  13.   SET OtherSubCategories AS
  14.     [Product].[Subcategory].[Subcategory] SelectedSubCategories
  15.   MEMBER [Product].[Subcategory].[Other] AS
  16.     Aggregate(OtherSubCategories)
  17. SELECT
  18.   [Measures].[Order Count] ON 0
  19. ,NON EMPTY
  20.     {
  21.       SelectedSubCategories
  22.      ,[Product].[Subcategory].[Other]
  23.     } ON 1
  24. FROM [Adventure Works]
  25. CELL PROPERTIES VALUE;

The idea is quite simple. First, you create a set with the number of slices you want to see (I called it SelectedSubCategories here). Then you can simply get all other categories using a set minus operation (I called it OtherSubCategories here). Finally you create the ‘Other’ member in the dimension as an aggregate of the last set.

image

Of course, you can even make the parameter for the number of slices a report parameter so the user can choose how many slices are shown in the diagram.

jetzt Teilen auf

Your email address will not be published.

Join #teamoraylispeople

Gestalte mit uns
die Welt der Daten