Pie Chart Techniques

A while ago I wrote an article called Chart Optimization Tips.  This article explained how to optimize a Column Chart.  Today I have returned to show you some Pie Chart implementation techniques.

As usual, I will be using the AdventureWorks2008 database, available at CodePlex.  The chart itself will be implemented using SQL Server 2008 Reporting Services.

Retrieving The Data

The dataset in our report uses the following query:

select SWD.*, SWA.City, SWA.StateProvinceName,
    SWA.PostalCode, SWA.CountryRegionName, SWA.AddressType
from Sales.vStoreWithDemographics SWD
inner join Sales.vStoreWithAddresses SWA
    on SWA.BusinessEntityID = SWD.BusinessEntityID

This query illustrates a bad coding practice: never use “SELECT *”.  Ideally you should only retrieve the columns that you need for the report.  That will optimize performance when generating the report.  But that is not the goal of this article so I’ll leave the query as it is.

A Basic Pie Chart

To get started with our Pie Chart I have selected the third icon in the list of Shape charts.  This adds a regular 3D pie chart to the report.

Select Chart Type window

To set up the chart I dragged AnnualSales from the Report Data pane into the “Drop data fields here” area and StateProvinceName into the “Drop category fields here”.

In case you don’t see the Report Data view (it has a tendency to disappear now and then), you can open it through the main menu: View > Report Data.

Report Data Pane

This is what our report looks like in Preview:

Basic Pie Chart

Wow, we’ve still got some work to do, this looks like a kids color book!  You wouldn’t say that this chart is showing the annual sales, would you?  There aren’t even any numbers on it!  Let’s get started on improving this.

Sorting The Numbers

A good implementation practice is to sort the slices from large to small.  If the slices are not sorted, it’s difficult to tell which state is performing better than another.  Just take a look at the previous image and compare the following two slices:

  • the pie shown in grey at 12:00
  • the pie shown in yellow at 03:00

Which one is the larger of the two?  Indeed, “I don’t know” is the right answer.

To implement the sorting you need to think about what you want to achieve.  What is it that we want to sort?  The states.  And these are shown as categories on the chart, so we should take a look at its properties.  As shown in the following screenshot, right-clicking on the [StateProvinceName] button gives a pop-up menu.  Select Category Group Properties.

Context menu for Category Group

Then we need to think about how we want the states to get sorted.  For that we should look at what is being shown as data of the chart.  In our case that is the sum of the AnnualSales field.

In the Category Group Properties, select the page called Sorting.  Clicking the Add button will add a line in the sorting options list.  Use the following expression for the “Sort by” field:

=Sum(Fields!AnnualSales.Value)

As we want to sort the largest values first, select “Z to A” for the Order dropdown.

Category Group Properties

Right, time to have another look at our report in Preview.

Sorted Pie Chart

So, we went from a colorful mess to an ordered colorful mess.  Chaos has been reduced a bit, but this is still one difficult-to-read report.

On to the next improvement!

Limiting The Pies

As you have noticed, a pie chart is not suitable to show that many categories.  We need to find a way to reduce the slices.  One way to do that is by adding a filter.  Another way is to add the smallest slices together into one slice.  This can be interesting in cases where we want to use all the data but we’re only interested in the larger slices.  Luckily, this can be done using standard pie chart properties.

Click on the pie itself, this will select the Chart Series.  One way to tell if you’ve selected the correct part of the chart is by looking at the Properties pane.  Its selection should show something like “AnnualSales Chart Series”, where AnnualSales is the name of the chart series.  Another way to tell is by the small white selector bulbs: they should be surrounding the pie.

Now, among the properties of the Chart Series you will find a property group called CustomAttributes.  Open this one by clicking the plus icon in front of it.  Change the CollectedStyle property to SingleSlice.  This tells the chart that we want to group the smallest slices into one slice.

Other interesting properties here are CollectedThreshold and CollectedThresholdUsePercent.  I’ve put CollectedThreshold to 2 and CollectedThresholdUsePercent to True (which is its default).  This means that any slice smaller than 2 percent of the pie will be added into the “collected slice”.

More useful properties are CollectedLabel, that’s the text that is shown on the slice itself, and CollectedLegendText, the text shown in the legend.

Chart Series - collected slice properties

The CollectedStyle property has another option besides the one I’ve shown, called CollectedPie.  Choosing that will generate a second pie next to the main one to represent all the small slices.  See the following screenshot for what it looks like.  In some cases this may be an interesting option but not in our example here.

Pie Chart showing a CollectedPie

The collected pie can also show labels by setting the CollectedChartShowLabels property to True, and the categories shown on the collected pie can be shown in the pie’s legend by setting CollectedChartShowLegend to True.

If you’d like the collected slice to jump out, there’s a property called CollectedSliceExploded.  Setting it to True will produce something like the following:

Exploded Collected Slice

As you have noticed, the previous screenshots have started to show text on the slices.  This can be easily activated by right-clicking the pie and selecting the Show Data Labels menu item.

Context menu on pie chart - Show Data Labels

And the next screenshot shows what our chart currently looks like.

Pie chart with collected slice

The small slices have been replaced by a really large one, and the text on the large slice is our customized version.  The other slices are showing some rather large numbers, so we still have some work to do.

Displaying Percentages

Let’s customize the label shown on the slices.  As the numbers are really large, I recommend to divide them by 1,000.  As long as it’s clearly mentioned on the report, it will make everything more readable.

Furthermore I’ll show you how to use built-in chart keywords (only available to ToolTips, custom legend text, and data point label properties), such as #PERCENT.

Right-click on one of the data labels and select Series Label Properties.

Context menu of data labels - Series Label Properties

Click the expression (fx) button on the General page and enter the following expression:

=FormatCurrency(Sum(Fields!AnnualSales.Value) / 1000, 0) & " (#PERCENT{P1})"

The first part divides the sum of AnnualSales by 1,000 and then applies the FormatCurrency function to the result.  The second parameter for FormatCurrency tells the function that we don’t want any decimals.  The result of this function call is concatenated with the second part using Visual Basic string concatenation (&).

The second part looks like a regular string but it contains a built-in keyword: #PERCENT.  This will show the percentage that the slice represents.  Furthermore, there’s a custom string formatter appended: P1.  By default the percentage would show 2 decimals.  This way it will only use one digit for the decimal fraction.

See here for a list of all built-in keywords and this page for more information on the available formatting options.

And following screenshot shows what our chart now looks like.

Pie chart showing percentages on slices

I’ve also given it a clear title, decreased the Data Label font size to 8 and moved the legend down.

To move the legend: right-click it, select Legend Properties and play with the radio buttons for the Legend Position.

Legend Properties

So, we’ve now got a fairly readable chart.  It’s not perfect, some labels are overlapping, but it’s doable.  However, we won’t rest here.  On to the next tip.

Rotating The Pie

Some people may ask you, “Why on earth does the first slice (the blue one representing 10.6% in our example) start at this weird angle at 4 o’clock?  Why can’t it start at 12:00?”.

Again we’re lucky because this can be controlled using a standard property.  Among the Chart Series CustomAttributes property group there are still some properties which haven’t been mentioned earlier.  One of them is called PieStartAngle.  By default it is set to zero.  Funny enough, zero stands for 30°.  Try it out and enter 30 for the property value.  Did you see the effect?  Indeed, nothing happens!  Now enter 90.  Did you see the chart rotate, even in Design mode?  Switch to Preview to get a better view of what the impact is.  As you can see, setting it to 90 will cause the first slice to start at 06:00.  To make it start at 12:00, we thus need to set the property to 270 degrees.

Pie Chart with customized rotation angle

Labels Outside Pie Chart

Other people may tell you, “But I don’t want all these labels on the pie itself, I want them next to it.”.

We’re still lucky because again this can be achieved using standard properties.  Still in the Chart Series CustomAttributes, there’s a property named PieLabelStyle.  Its default value is Inside.  Switching it to Outside will render the labels outside the pie, with lines attaching them to their respective slice.

Other interesting properties for the outside labeling are 3DLabelLineSize and MinimumRelativePieSize.

3DLabelLineSize defines the amount of space used for drawing the line between the label and its corresponding slice and is a percentage of its default size.  Values range from 30 to 200.  I’ve put it to 30 to get as much space as possible for the pie itself and the labels.

MinimumRelativePieSize represents a percentage of the chart area size and defines the minimum acceptable pie size.  Values range from 10 to 70.  I’ve put this one to 70 to maximize the size of the pie.

Chart Series properties with properties for outside labeling highlighted

With these modifications we’ve actually gotten some extra space for the labels.  Let’s take advantage of that and add extra information in the labels.  Change the Data Label expression to the following:

="#LEGENDTEXT" & vbcrlf &
FormatCurrency(Sum(Fields!AnnualSales.Value) / 1000, 0) & " (#PERCENT{P1})"

Our expression uses another built-in keyword: #LEGENDTEXT.  This will add the legend text to the label itself, which means the legend becomes obsolete.  So I’ve removed it.

And this is what our chart now looks like:

Pie chart showing labels on the outside

With Halloween coming up I thought it would be nice to create a spidery chart :-)

One More Custom Attribute

I’ve already mentioned several CustomAttribute properties of the Chart Series and I’d like to mention one more.  This property is called PieDrawingStyle and it will only appear in the list of properties when 3D is not enabled.  After disabling 3D I could set it to either SoftEdge or Concave.  I also noticed that labels outside of a pie chart will only have lines attached to them when rendered in 3D, so I’ve switched back to Inside for the PieLabelStyle property.

This is what SoftEdge looks like.  I think it’s rather nice.

Pie chart using SoftEdge drawing style

Custom Coloring

To conclude, there may be some people who tell you, “I don’t like those colours, and I don’t like any of the predefined sets.  I want to specify custom colours.”.

So again we’re lucky because even that is supported by default.

To get started with our color customization, select the chart object.  To know if you’ve made the correct selection, the Property pane should show “Chart” as non-bold part of the dropdown.  Alternatively you can just use that dropdown to select the Chart.  As Chart is a main object on the report, it is shown in the list (whereas parts of a Chart, such as Chart Series and Chart Area, are not shown in that list).

With the Chart selected, locate the Palette property.  By default it is set to BrightPastel.  In case you’re happy with one of the predefined palettes you can just select it here.  But we go for Custom, located at the bottom.

Next, locate the CustomPaletteColors property.  Selecting the property will show a button with an ellipsis as button text.  Click this button to get to the ReportColorExpression Collection Editor (what a name for a popup window!).  This window allows you to specify a list of colors.  I’ve specified the following 10 colors:

ReportColorExpression Collection Editor

And finally this is what our report looks like.  To stay in the theme, I’ve specified some colors which are suitable for Halloween-time charting.

Pie chart with custom colors - Halloween-style!

Coloring Consistency Using Dynamic Colors

And now to really conclude this article I’d like to mention one additional tip related to chart coloring.  In some occasions it may be interesting to have coloring consistency between different charting periods.  With that I mean that California would always show in grey, no matter whether it came first or not, Washington as brown, and so on.  This is currently not the case.  With the current implementation it’s the first pie that gets the grey color, the second pie is brown, and so on.

The best way to achieve that is to store the colors in the database and then fetch them in the same dataset that is used to retrieve the chart data.  The AdventureWorks database hasn’t got any color codes stored so I’ll just illustrate what I mean using a little cheat.

In order to get our dynamic coloring working, we will override the colors from the palette.  This is how it’s done.  Right-click on the pie and select Series Properties.  Select the Fill page and click the Expression (fx) button to define the color.  In the case where you’re selecting the color code as one of the database fields, your expression would look similar to this (assuming that colors are stored using their 6-digit hexadecimal representation with 000000 being black and FFFFFF being white):

="#" & Fields!ColourCode.Value

To imitate dynamic coloring I’ve used the following expression:

=Switch
(
    Fields!StateProvinceName.Value = "California", "Blue",
    Fields!StateProvinceName.Value = "Washington", "Red",
    Fields!StateProvinceName.Value = "Florida", "Green",
    True , "#888888"
)

The expression gives three states their own color and all the others will be colored a kind of grey.

This is what it looks like:

Pie Chart using dynamic colors

So, I hope you’ve enjoyed reading this article.  Feel free to post any comments should you wish to do so, and… happy charting!

References

SQL Server 2008 Books Online: Pie Charts

How to: Collect Small Slices on a Pie Chart

Formatting Data Points on a Chart

How to: Define Colors on a Chart Using a Palette

Share

Tags: , , , ,

  1. chris’s avatar

    great article. thanks for posting it!

    one question – when i use the dynamic coloring, the legend only picks up the first color I’ve defined in my switch (mocking something up). the other colors display as black. Any ideas?

    thanks again for the article.
    -chris

    Reply

  2. nojetlag’s avatar

    Very interesting and helpful article, I was wondering if you have experiance with grouping and page breaks for charts. Lets say you have a chart that contains monthly data for one year, however you would like to have the same chart for each month on a seperate page. Is that possible with SSRS 2008 ? I couldn’t find a property that gives me the option to specifiy that. The reason is that I have a matrix table and I want to visualize the monthly data with a small chart next to it.

    Reply

  3. Valentino Vranken’s avatar

    @chris: I tried it out and in my case the legend is working fine, using the expression that renders the blue, red, green, grey slices mentioned in my article above.
    So, if your expression is working fine for your slices but the legend is showing this strange behaviour, mayby this is a bug? Have you got the latest SP for SQL Server installed – SP1?

    @nojetlag: If I understand you correct, page 1 should show the chart for January, page 2 the chart for February and so on? I would use subreports for that. Create a new report that contains your chart. Add a month parameter to this report so that it retrieves only the data for that month. This is your subreport.
    In your main report, add the subreport item 12 times (if you need all 12 months) and configure each subreport to call your new report while passing in the correct month.
    That’s just one way to get to what you want.

    More info on subreports with params: http://msdn.microsoft.com/en-us/library/ms160348.aspx

    Thank you both for your comments!

    Valentino.

    Reply

  4. David laplante’s avatar

    Very interesting and usefull! Thanks for the tip on how to format chart labels like #PERCENT{P}

    I have a question though. I have this pie chart with a collectedstyle of “slice” to collect averything smaller than 3% into a “Others” bucket. On that chart, I have an action to open a new report based on the slice clicked. This works fine for all slices but it does not work for the collected slice. Is there something I am missing or is this not implemented ?

    Reply

  5. Valentino Vranken’s avatar

    Hello David,

    While I was trying out the drill-through action as you’ve described it, I indeed came to the same conclusion. And I think I know the reason. All the slices represent just one item of the category or series group that you’ve specified in your pie. When clicking on a slice, you’re probably passing some data from that particular group to the subreport. But the collected slice groups several items of your category/series group together into one slice. As a result SSRS does not know which one it is supposed to pass down to the subreport. In other words, it doesn’t make sense to have a drillthrough action on that collected pie.

    Hope that makes sense?

    And thanks for your nice comment btw!

    Regards,
    Valentino.

    Reply

  6. Soontaree Stallings’s avatar

    Thank you very much. Very useful articles.

    Reply

  7. Wendy Ware’s avatar

    Great article! I don’t have much experience working with pie charts in any technology and I am brand new user of SSRS 2008 (have used many other reporting tools over the years.) I was able to accomplish everything I needed thanks to your clearly-written explanations and excellent illustrations. Thank you for publishing this terrific information (originally found it on Experts Exchange.) Wendy Ware

    Reply

    1. Valentino Vranken’s avatar

      Hi Wendy,

      Thanks for taking the time to add a comment here! It’s comments like yours that keep me going :-)

      Ow, and congrats on getting started with SSRS!

      Best regards,
      Valentino.

      Reply

  8. Sanjeev’s avatar

    Really helpful & detailed info.

    thank you

    Reply

  9. John L’s avatar

    Labels actually do work when outside and softedge. The line color is the same as the BorderColor in that case. Set it to black and you’ll notice the labels then work as expected.

    Reply

    1. Valentino Vranken’s avatar

      Interesting, thanks for posting this!

      Reply

  10. Dario’s avatar

    Nice work! It’s very helpful so Thanks a lot! Cheers

    Reply

  11. Thierry’s avatar

    Hi,

    Great article! I’ve got a quick question which I hope you will be able to help me with. My vertical access needs to display time in hh:mm:ss. The data in my query can be obtained in seconds or already formatted in hh:mm:ss. Is there a way to use the seconds but generate the y axis min/max based on the seconds but display it as hh:mm:ss

    Any help would be appreciated.

    Regards

    T

    Reply

    1. Valentino Vranken’s avatar

      Hi Thierry,

      I assume you’re using a bar chart or something similar? So basically you’re asking if you can use a different field from the dataset as label, right? The label of both the Category and the Series can be customized through the Category (or Series) Group Properties window. In that window there’s a Label property which can be based on a different dataset field, or even through an expression. So in your case you’d set the Label property to the field containing the hh:mm:ss while the Group On property would remain your “seconds” field.

      Regards,
      Valentino.

      Reply

      1. Thierry’s avatar

        Hi Valentino,

        Thanks for taking the time to reply… Yes I’m using a bar chart… As for your suggestion, this is exactly what I want, but it’s on the wrong axis… After making the changes you suggested, I managed to display my pre-formated time which is already stored in SQL Server, but instead of displaying it on the x-axis, I want to display it on the y-axis… Is this possible?

        Thanks.

        T.

        Reply

        1. Valentino Vranken’s avatar

          Hi Thierry,

          To change the label on the Y-axis of a Bar chart, you should modify the Label property through the Category Group Properties page. Is that what you did? Or are you using a Column chart? A Bar chart has the bars going from left to right while a Column chart has got columns starting at the X-axis and going up (or down).

          Based on your comment I think you’re using a Column chart. In that case, I don’t think it’s possible to change the labels on the Y-axis because they are based on the Values series and there’s no property that makes this possible. What is possible though (but it’s probably not what you want) is to put a dataset field-based label on top of the column. This is done by selecting the Values series, then in Properties pane set a value for the Label property. The UseValueAsLabel property needs to be set to False as well.

          Regards,
          Valentino.

          Reply

  12. Yogita’s avatar

    Thanks a ton.
    This article was really helpful and was exactly what I needed :)
    Especially the part where I had to display the legend in the label (#LEGENDTEXT)

    Reply

    1. Valentino Vranken’s avatar

      Cool, nice to hear! :)

      Reply

  13. Don Harding’s avatar

    Pie chart has 3 values. Item total = 3. items red = 0, items blue = 1. Pie labes outside the pie. I know how to remove the “0″ from showing in the pie chart, but I can’t seem to make the “spider leg” – the line pointer that runs from the pie to the label to not show when the value is 0? It seems like you can have only one value for the PieLineColor. Doing an iif on the item total to change the color to no color, or white, on 0 does not seem to work.

    Reply

  14. Priyanka S’s avatar

    Hi Valentino,

    You post is great.. I followed it to create charts in my report. I am creating 3 charts in my report, but all the charts get different sizes during preview. Moreover, the series data label values are not displayed properly. Some values are displayed whereas some values just disappear! Is it something to do with the available chart area?
    Is there any way I can attach the report result clip to make it clearer?

    Thanks,
    Priyanka

    Reply

    1. Priyanka S’s avatar

      Hi … Just as an update, I was able to solve it… I increased the chart area box & all the labels were displayed correctly.

      Reply

  15. Gang’s avatar

    Thanks a lot………
    Nice Article…

    Reply

  16. Jit’s avatar

    Hi Valentino,

    Very descriptive article on pie chart in ssrs. I’m having an issue where I want the display the pielines at 90 degree. Is there a way to change the alignment/orientation of the pie lines?

    Reply

    1. Valentino Vranken’s avatar

      What do you mean with pie lines?

      Reply

      1. Jit’s avatar

        I mean when we keep the labels outside the pie chart a line connects the value and the pie section. Can we align these connecting lines at a specific angle. My requirement is to align these connecting line at 90 degree. Is this possible in ssrs?

        Reply

        1. Jit’s avatar

          Hi Valentino,

          I need this feature to be implemented in one of project. Please let me know if this is possible.

          Reply

        2. Valentino Vranken’s avatar

          I’m sorry, as far as I know that’s not possible. You can’t control how those connectors are generated…

          Reply

  17. Arletha’s avatar

    I love your blog.. very nice colors & theme.
    Did you make this website yourself or did
    you hire someone to do it for you? Plz answer back as I’m looking to create my own blog and would like to find out where u got this from. many thanks

    Reply

    1. Valentino Vranken’s avatar

      Have a look at the “Powered by” line at the bottom of the page…

      Reply

  18. satish’s avatar

    Very good article.

    Reply

  19. Ruta’s avatar

    Great Article! Just what needed! Thanks

    Reply

  20. DevBio’s avatar

    Hy,

    Thanks for this article.
    Do you know, why when you enable 3D, color are pastel?

    Reply

  21. Uma’s avatar

    supper one!, Thanks somuch

    Reply

  22. cheshire farmington hartford’s avatar

    I have been exploring for a little for any high-quality articles
    or weblog posts on this kind of space . Exploring in Yahoo I ultimately stumbled
    upon this website. Reading this info So i’m
    satisfied to show that I have an incredibly just
    right uncanny feeling I came upon just what I needed.

    I most certainly will make sure to don?t overlook this site and give it a glance regularly.

    Reply

  23. DUI ATTORNEY SCOTTSDALE: WHY DO YOU NEED IT’s avatar

    Since the admin of this site is working, no doubt very rapidly it
    will be renowned, due to its feature contents.

    Reply

  24. carpet cleaning Sunnyvale Ca’s avatar

    In addition to using the vacuum, you can use attachments that are included with the carpet steam cleaning products.
    Have you ever wished you could order food from a popular
    local restaurant and had it delivered. It’s
    easy to remove the adult fleas and eggs but the larva tend
    to cling onto the carpets tightly and that’s why you need a vacuum cleaner that is powerful.

    Reply

  25. top ten money making businesses Phoenix’s avatar

    I was wondering if you ever thought of changing the layout of your website?
    Its very well written; I love what youve got to say.
    But maybe you could a little more in the way of content so people could connect with it better.
    Youve got an awful lot of text for only having 1 or 2 pictures.
    Maybe you could space it out better?

    Reply

  26. daily bible quotes’s avatar

    Hi there every one, here every person is sharing such know-how,
    so it’s pleasant to read this website, and I used to pay a visit this website all the time.

    Reply

  27. make money online from home Phoenix’s avatar

    My brother suggested I might like this web
    site. He was totally right. This post truly made my day.
    You can not imagine simply how much time I had spent for this info!
    Thanks!

    Reply

  28. kliknij tutaj’s avatar

    Creating two or three profile is inconvenient for you as it require you to log out and log in again and again to manage your
    account. ) Share your last link (at least for now) – On the website, you can add in your name and email address and create
    a countdown for your 99 days. The doctor used the results to confirm that humans have
    souls and an article was published called “Soul has weight, physician thinks” in 1907.

    Reply

  29. air conditioning repair services’s avatar

    When I originally commented I clicked the “Notify me when new comments are added” checkbox
    and now each time a comment is added I get four e-mails with the same comment.
    Is there any way you can remove me from that service?

    Thank you!

    Reply

  30. accommodation in plettenberg bay’s avatar

    Do you have any video of that? I’d love to
    find out some additional information.

    Reply

  31. bail bondsman numbers’s avatar

    I loved as much as you will receive carried out right here.
    The sketch is attractive, your authored material
    stylish. nonetheless, you command get got an edginess over that you wish be delivering the following.

    unwell unquestionably come further formerly again as exactly the same
    nearly very often inside case you shield this increase.

    Reply

© 2008-2014 BI: Beer Intelligence? All Rights Reserved