Learn more about SQL Server tools

 

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

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server Reporting Services Multi-Layer Pie Charts


By:   |   Read Comments (7)   |   Related Tips: > Reporting Services Charts

Problem

I have been using pie charts for SSRS, but they are hard to read and don't really give a good picture of the data.  What options are there to make pie charts more visually appealing as well as more useful?

Solution

Plain old pie charts, similar to the one below, often get a bad rap in the data visualization world. Many folks discount their use in dashboards and explain they are poor for comparing datasets and can be used to "cover up" negative data points. These points are valid, but when used correctly, pie charts offer report developers with another tool in their visualization arsenal. In this tip we will cover ways to make a pie chart stand out as being both visually appealing and appropriately proportioned.

pie chart

We will use the Adventure Works databases as the basis for our SSRS report development. The 2014 versions of the regular and data warehouse databases are available on Codeplex: https://msftdbprodsamples.codeplex.com/releases/view/125550. Once you download and install the SQL Server databases, we will subsequently use SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to develop reports. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.

Pie Chart Pizazz

Creating your first pie chart is rather easy. From the toolbox, you will drag the chart object onto your design panel, and select pie chart as the chart type. Finally, you specify the value data point and the category group, SUMAMT and SalesTerritory, respectively in the below screen print. Note you can add multiple category groups and even add series groups, but each combination of multiple categories or category / series combinations are just considered another pie slice.

Pie Chart 2

Shown next, the resulting pie chart is, rightly so, really not terribly informative or clear. These situations, of course, are what give pie charts a bad name.

basic pie chart

So what can we do to make the pie chart clearer? First, we should show data labels and second, we need to show the percent of the pie total for each category group. To turn on Show Data Labels, simply right click on the chart, and then click on Show Data Labels. Next, to show the percent of total instead of the raw number of each slice, we again right mouse click on the labels, select Series Label Properties. In the Label Data Field, we add the #PERCENT preset, which tells SSRS to show the percent of the total pie for each label.

show data labels

label prpperties

show percent

These changes result in a better, but definitely not a perfect pie chart. The labels help to more equally compare each of the regions; however we can make it better.

Pie Chart with Label

To make this pie chart better, we can begin to utilize some of the pie chart custom properties. First, as shown next, we click on the data label, and go to properties and change the position to Outside. This change puts the labels outside the pie slices. Next, we adjust the label expression to show both the category, shown in step 2 below, plus the percent. This expression looks like: =Fields!SalesTerritoryRegion.Value & "-" & "#PERCENT{P2}"; notice how we still use the #PERCENT data label, but put it within double quotes. We also tell SSRS to format it for P2, or percent with two decimal places.

outside legend

Finally, we click on the pie chart itself, go to properties, and then CustomAttributes. We adjust the PieLabelStyle to Outside and select a PieLineColor which defines the color going from the pie slice to the label.

Chart Properties for Label

We now have a much better looking pie chart, illustrated below, which addresses many of the issues with a basic pie chart.

labes with lines

The final result of our pie chart is displayed next.

Pie Chart with Outside Label

Make a Pie Chart Even Better

Wow, what an improvement, but we can make it even better. Notice how the Northeast, Central, and Southeast regions are all less than 1%. It would be beneficial to combine these under one "slice of pie". To complete this process, we need to adjust some additional CustomAttributes properties for the pie chart as follows:

  • CollectStyle needs to be set to SingleSlice
  • CollectedThreshold is the value below which all the slices will be combined
  • CollectedThresholdUsePercent determines whether the value comparison is a raw number or percentage
  • CollectedThresholdExploded tells SSRS to exploded out the rollup slice (see red slice in below screen print)
  • CollectedColor sets the color of our rollup slice
  • CollectedLabel and CollectedLegendText sets the text which will be shown for the collected slice for the label and legend
Exploded Other

You can see in the first screen print below that we collect all the values less than 8% under the All Other pie slice. To see the CollectedStyle better, in the second illustration, the threshold is set to less than 10%).

All Other 1

All Other 2

We round out the CollectedThreshold features by adjusting the CollectedStyle to CollectedPie. This change will create what I call the double pie effect which allows for drilling into the "All Other" data point.

Collected Pie

The CollectedPie property results in the below screen prints which gives us both the high level details and drills into the values that make up the All Other category.

Double Pie

One other way we can use colors and multiple categories, is to use the same slice color to reference the main category group while at the same time still breaking up the slices. To help clarify this situation, please review the below screen print. You will notice there are a large number of slices broken out by the major categories of Bikes, Clothing, and Accessories; notice how the slices are ordered so all the main product category slices are together. These categories are further refined by the product models. This chart is a little busy with all the colors. As opposed to using the above technique of creating an "All Other" category, we will instead use some color techniques to achieve beneficial results.

Expanded Pie 1

Our first step is to select the pie chart, as shown below, and then edit the Color Property. This property sets the color of the pie slice.

Color Setting

Subsequently, we "hard" code the color property of the pie slices based on the Major Product Category, as shown below. To assist with this color coding in large data sets, you could actually have a color table which join with your query.

Color Switch

This results in pie slices being all the same color, as illustrated next; now we need to add a prominent border between slices to distinguish between slices

Pie all one colory

Adding a border color of Dark Green is shown next.

Border for Pie

This change results in a much more defined pie chart (barring my poor color selections!!) when you want to display many values as shown next.

Pie Chart with Borders

One item that is beyond the scope of this particular tip is to code the slice color palette to adjust the shade of a color based on the percentage within the pie chart as described in this MSDN article: http://blogs.msdn.com/b/davidlean/archive/2009/02/17/sql-reporting-how-to-conditional-color-3-4-tint-shade-functions.aspx.

Conclusion

In this tip we covered some of the various ways to liven up a pie chart to enhance its clarity and effectiveness. Two particularly helpful enhancements utilize the CollectedStyle attribute to either rollup small values into a "single slice" or to rollup a group of values into a "secondary pie" chart. Furthermore using clear labels and the #PERCENT function provide additional precision to your pie charts.

Next Steps


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

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    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, March 22, 2016 - 11:20:53 AM - Jacque Back To Top

 No I hadn't, I'll give that a try.  Thanks  :-)

 


Monday, March 21, 2016 - 7:50:20 PM - Scott Murray Back To Top

Have you tried putting the pies within a rectangle to maintain the size ratios?


Monday, March 21, 2016 - 4:04:23 PM - Jacque Back To Top

 Hi Scott, Is there a way to lock in the size of a pie?  For my report, I have two pies, same dataset, one pie showing number of Accounts and the other showing Sum of Balances and the category group for both is the same.  I use the legend from one pie chart.   I want them to be consistent in size but that's not what I am seeing.  I have the MinimumRelativePieSize set to 70 and the data labels are set for outside.  When I run the report, the pies are sometimes sized differently - even on the same page, one pie will be bigger than the other.    

Any ideas? 

I appreciate your help and these articles are awesome!

Thanks, Jacque

 


Wednesday, August 19, 2015 - 9:29:18 AM - Scott Murray Back To Top

Jacque... I would start with tyour Chart Series Label properties.. specifically, I would review the Font, position, and rotation.


Tuesday, August 18, 2015 - 12:06:46 PM - Jacque Back To Top

Hi Scott, I'm having an issue retaining legibility in my labels, I don't have even close to the number of labels on my chart as you have on your examples and yet mine over lap and are illegible.  What settings should I be looking at?

Thanks,

 

 


Thursday, May 07, 2015 - 9:21:08 AM - Scott Murray Back To Top

Great ideas Koen.


Thursday, May 07, 2015 - 7:32:21 AM - Koen Verbeeck Back To Top

To make the pie charts even better, you should sort the slices from big to small, and start the biggest slice at 12 o'clock (which can be configured).


Learn more about SQL Server tools