Problem solve Get help with specific problems with your technologies, process and projects.

Utilizing shape charts with Report Builder 3.0

Shape charts such as pies of funnels are perhaps the simplest chart types available in Report Builder, so it's important to know when (and when not) to use them.

Part 1 | Part 2 | Part 3 | Part 4 | Part 5

First, you saw how to set up a bar chart in Report Builder 3.0. Then you learned how bars were similar to column and line charts. The next step involved range charts and when to use them. Finally, we've gotten to the simplest charts of all – shape charts, specifically pie and funnel formats.

Adding a pie chart to a report

A pie chart is a much simpler type of chart than any we've looked at so far. In fact, it should be simpler. The purpose of shape charts is to show simple comparisons (usually as percentages) between groups of data. If these charts get too complicated, they can lose their impact. Figure 17 shows the pie chart I've added to my report. This specific type of chart is 3-D Exploded Pie.

Figure 17 (click to enlarge)

Notice, first, that the series value field, SalesAmount, simply uses the Sum function to aggregate the data. This is the default aggregation for numerical fields, so when you add the field to the chart data, Report Builder 3.0 automatically uses the Sum function to create the aggregation. In addition, I included the SalesGroup field in the series groups, but I added no fields to the category groups. The chart will simply show the percentage of sales per group for a specified year.

The only significant change I made to the chart after I added fields to the chart data was to add labels to the pie segments. To add labels, right-click the chart and select Show Data Labels. The labels will then be added, though,by default, the they won't show the percentage signs. You can override this behavior by configuring the label properties.

To access the properties, right-click one of the labels that appears in a pie segment, then click Series Label Properties. On the General tab of the Series Label Properties dialog box, enter the following expression in the Label Data text box:


That's all there is to setting up a pie chart. When the chart is rendered, it breaks the pie into segments, one for each sales territory, as shown in Figure 18.

Figure 18 (click to enlarge)

Notice that a percentage value appears on each segment. As you can see, if you were to add too much information to a pie chart, it would become cluttered very quickly.

Adding a funnel chart to a report

Another type of shape chart is the funnel. You can easily switch from a pie chart to a funnel chart without making any configuration changes. Figure 19 shows the funnel chart that I inserted when I switched from the pie chart.

Figure 19 (click to enlarge)

As you can see, the chart data looks identical to the pie chart. There is one change I made, however, that can improve the display of a funnel chart. By default, the sections of the funnel are ordered by the SalesGroup values, from A to Z. For a pie chart, this makes no difference. For a funnel chart, however, it might be better to sort the sections by the aggregated series values, SalesAmount.

To sort the funnel sections by the sales amounts, I modified the series group properties, as shown in Figure 20.

Figure 20 (click to enlarge)

Notice that the data is now sorted by the aggregated sales amount from high to low (Z to A). This way, the group with the most sales is at the top of the funnel, and the group with the least sales is at the bottom, as shown in Figure 21.

Figure 21 (click to enlarge)

Clearly, shape charts are easy to implement and provide a quick, basic look at important statistics. No doubt, you'll want to choose different charts for more complex data.

Report Builder 3.0 supports many varieties of charts, far more than what I've described here. For each chart, there exists an assortment of properties that offer a great deal of flexibility in how the chart is rendered in the report. Certainly, I've only scratched the surface with what you can do with charts. Even so, the examples provided here should give you a starting point for adding charts to your reports. Just remember that you can take the charts a lot further when you're ready to make the leap.

 Part 1: adding charts
 Part 2: Creating bar charts
 Part 3: Generating column and line charts
 Part 4: Using range charts
 Part 5: Utilizing shape charts

Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at

Dig Deeper on Microsoft SQL Server Reporting Services (SSRS)