Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Handling a Large Number of Categories in a SQL Server Reporting Services Pie Chart


By:   |   Last Updated: 2012-09-28   |   Comments (18)   |   Related Tips: > Reporting Services Charts

Problem

My client has an existing report used for displaying Prospective Buyers statistics and few other related details. In the current report, a Pie Chart is being used to display the Number/Percentage of Prospective Buyers in each of the states. Initially when the number of states being surveyed was limited, the Pie Chart was working well for the scenario. However, now they are surveying a lot more states and as a result, the number of categories (State Provinces) in the Pie Chart has increased to a large number leaving the chart in a non-usable state. I want to know what are the best options to make the chart usable and more intuitive to the users.

Solution

In this tip we will see some of the options to make the chart more intuitive to the end users by using different chart types and also by making some customizations in Pie Chart itself.

Note: This tip assumes that you have prior knowledge of working with SQL Server Reporting Services (SSRS) and are familiar with Developing a Pie Chart. If you are not familiar with SSRS, refer to the SSRS Tutorial. The demonstration in this tip is based on SQL Server 2012. However, the steps are pretty much same for previous version of SSRS also.

First let us simulate the scenario given in the Problem Statement. To simulate the scenario, let us consider a sample data set from AdventureWorksDW sample database using the below query. AdventureWorks sample database can be downloaded from CodePlex site.

Building the Sample Dataset

SELECT
    [PB].[ProspectiveBuyerKey]
    , [SP].[StateProvinceName]
FROM
    [dbo].[ProspectiveBuyer] AS [PB]
    INNER JOIN (
                SELECT
                    [StateProvinceCode]
                    , [StateProvinceName]
                FROM [dbo].[DimGeography]
                GROUP BY [StateProvinceCode], [StateProvinceName]
    ) AS SP
        ON [PB].[StateProvinceCode] = [SP].[StateProvinceCode]
WHERE [PB].[StateProvinceCode] NOT IN ('CA','WA','OR')

Creating the Pie Chart with above Scenario (Large number of categories)

Let us create a Pie Chart using the above dataset by following these steps:

  • Open SQL Server Data Tools (SSDT) or Business Intelligence Development Studio (BIDS) if you are working with previous version of SSRS.
  • Create a Report Server Project.
  • Create a Data Source (You can either create a Report Data Source directly or create a Shared Data Source first and then use this to create a Report Data Source).
  • Create a dataset using the above query.
  • Add a new Pie Chart ("3-D Pie") to the Report Design surface.
  • Set the Chart Values to "ProspectiveBuyerKey" and "Aggregate Function" to "Count".
  • Set the Category Group to "StateProvinceName" and Sort by property to "StateProvinceName".
  • With a little bit of formatting around Colors, Fonts etc., the completed Pie Chart should look as shown below.
    Creating the Pie Chart

As we can see in the above Pie Chart, it is difficult to identify which item on  the legend corresponds to which slice on the Pie and the value of Prospective Buyers for each of the states.

The above chart can be improved and made more intuitive and usable for the users by using the following two approaches:

  • Approach 1: Changing the Chart Type
  • Approach 2: Customizing the Pie Chart

We will see two flavors of each of the above two approaches.

Approach 1(a): Convert Pie Chart to Column Chart

Let us convert the above Pie Chart into a Column Chart using the following steps:

  • Right Click on the Chart and Change the Chart Type to Column Chart.
  • Go to Horizontal Axis Properties and set Interval to "1". This will ensure that, all the State Provinces are displayed on the Chart.
  • Set the Vertical Axis Title as "# of Prospective Buyers"
  • Set the Horizontal Axis Title as "State Province"
  • Delete the Legend. We don't need it since we have only one metric, which is usually indicated using the Chart Title in a Real-Time scenario.
  • Go to Chart Properties and set the Width to 8in and Height to 4in.
  • Right Click on any of the Columns and set "Show Data Labels" if it is not already set.
  • Right Click on a Data Label, select "Series Label Properties", and set "Label data" to "#VALY".
  • With some formatting around Color, Font, and Border etc. the Line Chart should look as shown below.
    Convert Pie Chart to Column Chart

The chart above is much cleaner and easier to understand. In the above chart, if there is a huge gap between the largest and smallest values, then Scale Break can be added. Next let us see a second flavor of this approach.

Approach 1(b): Convert Pie Chart to Line Chart

Let us convert the Column Chart from Approach 1(a) to a Line Chart using the following steps:

  • Right Click on Vertical Axis and set "Show Major Gridlines" (if it is not already set).
  • Right Click on Horizontal Axis and set "Show Major Gridlines" (if it is not already set).
  • Right Click on the Line and set "Show Data Labels" if it is not already set.
  • Right Click on a Data Label, select "Series Label Properties", and set "Label data" to "#VALY".
  • Right Click on the Line, go to "Series Properties", click on "Markers" in the left navigation pane, and set "Marker type" to "Square".
  • With some formatting around Color, Font, and Border etc. the Line Chart should look as shown below.
    Convert Pie Chart to Line Chart

The chart above is much cleaner and easier to understand.

Now let us see the 2nd approach using Pie Chart with some customization.

Approach 2(a): Group/Consolidate Smaller Slices in Pie Chart

  • Right click on the initial Pie Chart and set "Show Data Labels" (if it is not already set).
  • Right Click on a Data Label, select "Series Label Properties", and set "Label data" to "#PERCENT" (If you get a warning, select "Yes").
  • Right Click on a Data Label, go to properties window, and set "Position" to "Outside".
  • Preview the Report and you can see that the report looks even more cluttered with Data Labels on it.
  • Go to Chart Series Properties --> Expand "General" --> "CustomAttributes" and make the following settings as shown below:
    • "CollectedStyle" to "Single". This will group the specific set of slices (defined by "CollectedThreshold") into single slice.
    • "CollectedThreshold" to "4". This will collect all the slices which contribute to less than 4% of total Prospective Buyers and put it into combined Pie Slice.
    • "CollectedThresholdUsePercent" to "True" if it is not already set. This is used to indicate that the threshold is in Percent.
    Group/Consolidate Smaller Slices in Pie Chart
  • With some formatting around Color, Font, and Border etc. the modified Pie Chart should look as shown below.
     
    the modified Pie Chart should look as shown below.

As we can see the above chart is very clean and is suitable for most of the scenarios. In this kind of scenario, it is most likely that the users are interested in those states where the greatest number of Prospective Buyers are located and hence it is fine to combine those states where Prospective Buyers is less than a specified percentage (4% in above chart) into one slice in a Pie Chart.

There are various other Custom Attributes, as shown in the above settings window, which can be modified to customize the chart further. For instance, "CollectedLegendText" can be set to something else (default is "Other") to make it more meaningful as shown below by setting it to "Other States".

There are various other Custom Attributes

Approach 2(b): Separate Smaller Slices into Separate Pie

  • Right Click on the Pie in the above Pie Chart in Approach 2(a), go to Properties, expand "CustomAttributes", and make the following settings:
    • "CollectedStyle" to "CollectedPie"
  • Relocate the Legend to bottom.
  • Increase the size of the chart.
  • Right click on a data label and set the "Label data" to "#VALY".
  • With some formatting around Color, Font, and Border etc. the Pie Chart should look as shown below.
    Separate Smaller Slices into Separate Pie

Notice that the chart automatically gets displayed as 2-D even though we started with 3-D. This is because, when we split the Pie, it automatically gets transformed to 2-D.

This chart can be further customized using few other properties under "CustomAttributes".

  • Right Click on the above Pie Chart in Approach 2(a) and change the "Chart Type" to "Pie" (2-D Pie).
  • Right Click on the Pie, go to Properties, expand "CustomAttributes", and make the following settings:
    • "CollectedChartShowLegend" to "True". This will enable the display of Legends for the separated Pie as well, which in the above Pie is collected marked as "Other" in Legends section.
    • "CollectedLabel" to "Other States". This will change the text displayed on the slice of Pie in the Main Pie which is being split into separate pie.
    • "PieDrawingStyle" to "Concave". This will change the Pie Style.
  • With some formatting around Color, Font, and Border etc. the Pie Chart should look as shown below.
    This chart can be further customized using few other properties under "CustomAttributes".

As we can see from the above demonstration, there are many different ways in which we can address the issue outlined in the Problem Statement using the capabilities of SSRS.

Next Steps


Last Updated: 2012-09-28


next webcast button


next tip button



About the author
MSSQLTips author Dattatrey Sindol Datta has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, April 11, 2015 - 12:54:11 AM - Prabhakar vang Back To Top

Hi Guys,

 

In SSRS Bar Chart how to hide 0%


Friday, June 21, 2013 - 5:02:26 PM - rajesh Back To Top

Hi

 

Just i want to know Is  it possible to apply different colours to single slice?

Example : i have a pie chart with three different values like country uk,usa,others data like 45,25,30.basically each slice will get different colurs in this case each state represents will their own colurs.i want to fill two different colours for USA like from center point to middle one colour and from middile to pie end another colour.

 

Is this possible,please advise me

Regards

Rajesh
 


Friday, May 03, 2013 - 8:51:24 AM - Dattatrey Sindol (Datta) Back To Top

Hi Yasmin,

 

Good to know that you got the solution.

For any questions related to SQL Server (Which includes SSIS, SSRS etc.), please post it here: http://sqlserverquestions.mssqltips.com/questionnew.asp.

 

Best Regards,

Dattatrey Sindol (Datta)


Friday, May 03, 2013 - 2:22:43 AM - Yasmin Back To Top

Hi Datta,

Thanks for ur Reply.. Yesterday i tried one concept of bar chart with range.. and showing different colors based on Data.. and i successfully finished that one .. anyway thanks for ur valuable reply.. Nobody here to help in ssrs reports.. i try with the help of google and this sites.. i will mail u further if i have any doubts.. Thank u..


Thursday, May 02, 2013 - 9:44:32 AM - Dattatrey Sindol (Datta) Back To Top

Hi Yasmin,

 

Please post your query and if possible a screenshot of your report (With Actual and Expected Output), so that I can look into it and try to provide you with some inputs.

 

Best Regards,

Dattatrey Sindol (Datta)


Wednesday, May 01, 2013 - 9:29:24 AM - Yasmin Back To Top

Hi datta,

i am using SSRS 2008. normal Barchart .. i want y Axis values as STATIC.. that is not from DATABASE.. category groups.. i want caption like this.. (ThisMonth,Nextmonth, Past) and the values are nextmonthcount,thismonthcount,lastmonthcount ... values are shown properly.. label display only Problem.. if i include CATEGORY , 2 or more times values are Shown... Will u please Guide me ..


Thursday, March 28, 2013 - 1:25:23 AM - Dattatrey Sindol (Datta) Back To Top

Hi Udaya,

 

For your scenario, consider using Trellis Chart, which might help you solve the problem. Take a look at this tip:

http://www.mssqltips.com/sqlservertip/2462/creating-trellis-charts-to-make-ssrs-reports-more-readable/

 

Best Regards,

Dattatrey Sindol (Datta)


Wednesday, March 27, 2013 - 7:34:43 PM - Udaya Nalla Back To Top

Hi i have a similar problem, but pie, column or line chart does not solve my problem as i need to show trending data over a 5 month/week period. I had tried line chart and stacked area chart but they too do not give the desired results, any tips on how i can improve that.


Wednesday, March 20, 2013 - 10:10:23 AM - jonmcrawford Back To Top

Pie charts are terrible, 3D Pie Charts are even worse, they skew the data and as you have pointed out, determining which slices of pies are bigger than others is something humans are terrible at. The bar chart is a much more suitable graphic, the line chart does not make sense because they are different items, not related, and not tracking over time.

See http://www.perceptualedge.com/articles/08-21-07.pdf for more info

As data professionals, we should be helping our users to better comprehend their data by avoiding worst practices and giving them better options.


Tuesday, December 04, 2012 - 2:59:08 PM - Dattatrey Sindol Back To Top

Hi Nagi,

 

Can you please post a picture of what exactly your current chart looks like and what is the output your are expecting? This will help me understand the problem better and provide you a possible solution. You can upload the image on to SkyDrive or any other online location and send me the link, so that I can take a look.

 

Best Regards,

Dattatrey Sindol (Datta)


Tuesday, December 04, 2012 - 12:49:37 PM - Nagi Back To Top

Hi Sindol,

Thanks for your reply!!

Actually i need ,in 360 deg pie chart, the %values are appear in counterclock wise,but the % values are appeared in jig zag .

 

Regards,

Nagi


Monday, December 03, 2012 - 9:19:11 AM - Dattatrey Sindol Back To Top

Hi Nagi,

 

To get the slices in ascending/descending order based on the percentage, please follow these steps:

1. Go to Category Group Properties

2. Click on Sorting in the left navigation pane

3. Set the expression for Sort By as the same as your series expression. For instance, let us say you are displaying the Sales Amount in your pie chart then your series data expression will be "=Sum(Fields!SalesAmount.Value)". Set the Sort by Column for Category Group also with the same expression "=Sum(Fields!SalesAmount.Value)" and preview the report.

 

Hope that helps.

Best Regards,

Dattatrey Sindol (Datta)


Monday, December 03, 2012 - 2:59:29 AM - Nagi Back To Top

Hi,

how can i sort percentage values in pie charts?

is there any option to sort the percentage values in pie charts?

ex: red is 2 %,green is 10 %,yellow is 30%,gray is 25 blue % etc..

but in the chart the values come in jig jag way means 30% yellow then 2% red then 25% blue then 10% green values comes ,but i want to generate the percentage values in sorting order mens 2% red first then 10% green values next then 25% gray values ..like this

Plz reply sir,

regards

nagi

 


Wednesday, October 03, 2012 - 7:47:02 AM - Debbie Edwards Back To Top

Thank you for the really easy to follow guide.

I was having issues where I had a pie chart with 3 values. When showing data lavels I could only even see one. Consequently I couldnt change the data label to number like I wanted.

I turned it into a pbar chart first. Did the data labels and then converted to a pie chart. problem solved.

Debbie

 

 


Friday, September 28, 2012 - 12:13:23 PM - Dattatrey Sindol (Datta) Back To Top

Thank you for the feedback Bob and Ranga.

 

@Scott - Yes you are right. Grouping it on Region is another option. However, since grouping is driven by business requirements, in many scenarios, we may not have control over the grouping and we need to provide alternate options to the business users. Thank you for thoughts/suggestions.


Friday, September 28, 2012 - 9:55:19 AM - Scott Thompson Back To Top

You may also think about the business use.  It could be that users may not care about the individual states and you could group by region of the country.


Friday, September 28, 2012 - 9:20:23 AM - Ranga Back To Top

Excellent! very well presented!


Friday, September 28, 2012 - 8:10:58 AM - BobC Back To Top

Great tips.   I find sorting in order of size makes these charts easier to digest. 


Learn more about SQL Server tools