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:
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:
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)
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:
WITH
SET SelectedSubCategories AS
TopCount
(
Order
(
[Product].[Subcategory].[Subcategory]
,[Measures].[Order Count]
,DESC
)
,10
)
SET OtherSubCategories AS
[Product].[Subcategory].[Subcategory] – SelectedSubCategories
MEMBER [Product].[Subcategory].[Other] AS
Aggregate(OtherSubCategories)
SELECT
[Measures].[Order Count] ON 0
,NON EMPTY
{
SelectedSubCategories
,[Product].[Subcategory].[Other]
} ON 1
FROM [Adventure Works]
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.
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.
August 25th, 2010 on 15:52
Hi,
I’m trying to extend your solution and build a model where the dimension ‘[Product].[Subcategory]’ is chosen by another parameter.
Unfortunately, I’m facing a problem:
I’ve 2 parameters:
@TopN: Value: 5
@RowDimension: Value: [Product].[Subcategory]
The code look like this:
WITH
SET [DynamicDimension] as strtoSet(@RowDimension + ‘.MEMBERS’)
MEMBER [Measures].[RowCaption] as strtoMember(@RowDimension).MEMBER_CAPTION
SET [TopItem] as TopCount(DynamicDimension,@TopN,[Measures].[Order Count])
SET [BottomItem] as {[DynamicDimension]} – {[TopItem]}
MEMBER [Product].[Subcategory].[Other] as aggregate(BottomItem)
SELECT
{[Measures].[Order Count]} ON COLUMNS,
Non Empty {
[TopItem] ,[Product].[Subcategory].[Other]
} ON ROWS
FROM [Adventure Works]
Unfortunately this solution doesn’t work when I change the value of parameter @RowDimension (ie: [Customer].[Customer Name]).
This is because calculated member “[Product].[Subcategory].[Other]” is related to Product dimension and is not possible parametrize the name of a member.
Any suggestions?
The alternative solution that I’ve found use Visualtotals, but have also some problems.
This is the code:
WITH
SET [DynamicDimension] as strtoSet(@RowDimension + ‘.MEMBERS’)
MEMBER [Measures].[RowCaption] as strtoMember(@RowDimension).MEMBER_CAPTION
SET [TopItem] as TopCount(DynamicDimension,@TopN,[Measures].[Order Count])
SET [BottomItem] as {[DynamicDimension]} – {[TopItem]}
SET [VisualT] as VisualTotals ({TopCount(DynamicDimension,1,[Measures].[Order Count]),BottomItem}, ‘ * Others’ )
SELECT
{[Measures].[Order Count]} ON COLUMNS,
{[TopItem]} ON ROWS
FROM [Adventure Works]
By adding the named set [VisualT], the ‘All’ member of set [DynamicDimension] is overwritten with the sum of BottomItem (my goal).
This solution have 2 problems:
1) I lose the real “Total” value of my row dimension.
2) If TopN paramenter is greater than the number of items, then the value for ‘All Other’ is wrong. Instead of be empty the value is ‘All’ !!!
Any idea?? I’m little bit frustrated
Thanks
Stefano
August 28th, 2010 on 08:34
Hi Stefano, You could use an expression for the query instead of writing it directly. For this you have to write something like the following in your query input box:
=”with member “+@RowDimension+”.[Other] as …”+ “select … ”
This is not so easy to read but allows you to insert your parameters at any position within your query.
For the second question, you could use the iif function to check, if your @TopCount parameter is too high and return and empty set in this case.