Using data label expressions to create SSRS 2005 Pie Charts with intuitive and helpful pie slice

by Joe Toscano, Senior Software Engineer, RDA Corporation

If you’ve created PIE charts in SQL Server Reporting Services you’ve noticed that you are able to drag and drop category, series and data fields. The setup is pretty straightforward, but by working a bit with the data label values you are able to display more than a single column value in each slice of the pie. This may be helpful in allowing your reports more contain more useful information.

In our example we are looking at the total sales for bike products from the AdventureWorks database. We are looking at 3 categories: Touring Bikes, Road Bikes and Mountain Bikes. Each bike category has both a Total Sales and a Percentage of Total Sales that is part of the reports data set. Most pie chart reports display a single value present in each slice of the pie; however, in our report we are displaying both values. Below is a snapshot of this sample report:

How did we improve our pie slice labels?

This blog entry focuses on how we are able to display both the total sales and the percentage of total in each pie slice. The answer lies in modifying the data label for the Total Sales column that we dropped to the Data Field area while in Report Layout / Design Mode.

The screen snapshot below was taken from the Report Design / Report Layout mode. Notice that we drug the LineItemTotal column to the Data Field area. Once there, we right-clicked on the data label and chose properties.

Let’s focus on the Point Labels tab. Notice the Data Label dropdown and the little “Fx” to its right. This tells us that this value can be much more than a simple column in your dataset. The Data Label value can actually be the results of an expression! In our case, we used the several built-in functions to convert data types of more than a single column, strip out trailing spaces and concatenate formatted strings. Below is the expression that was used in our Data label:

cstr(left(Fields!LineItemTotal.Value / 1000000, 5)) & vbcrlf & " (" & cstr(left(Fields!PercentageOfTotal.Value,5)) & "%)"

In summary, by working with the Data Label expressions, your pie slices can contain much more information that a single column value. The only limit is really the size of each pie slice, but as was done with our report you are able to specify that the slice values be placed on the outside of the pie.