• Article

Tutorial: Adding a Pie Chart to Your Report (Report Builder 2.0)

Pie charts and doughnut charts display data as a proportion of the whole. Pie charts are most commonly used to make comparisons between groups. Pie and doughnut charts, along with pyramid and funnel charts, constitute a group of charts known as shape charts. Shape charts have no axes. When a numeric field is dropped on a shape chart, the chart calculates the percentage of each value to the total.

If there are too many data points on a pie chart, your data point labels might be too crowded to read. For this scenario, consider using a line chart. Consider using pie charts only after you have aggregated your data into a few data points.

What You Will Learn

In this tutorial, you will learn how to:

  • Create a new dataset and use the relational query designer to choose a view from the database. A view is a predefined object that you can use to retrieve related data from separate tables, for example, sales person and yearly sales.

  • Add a pie chart to display the data.

  • On each slice of the pie, display a percentage for this slice compared to the whole pie.

  • Combine multiple small slices into one larger slice that represents all of them.

  • Change the display options for the pie chart.

Estimated time to complete this tutorial: 10 minutes

Requirements

You must have the following prerequisites to complete this tutorial:

To open an existing report

  1. Click Start, point to Programs, point to Microsoft SQL Server 2008 Report Builder, and then click Report Builder 2.0.

  2. From the Report Builder button, click Open.

  3. Click Recent Sites and Servers, and then navigate to Sales Order on the report server.

Next, you will add a pie chart to the report.

To add a pie chart with the Chart wizard

  1. On the Ribbon, on the Insert tab, click Chart, and then Chart Wizard.

    Because a dataset already exists in the report, the Choose a dataset page opens. In this tutorial, you will create a new dataset.

  2. Click Create a dataset, and then click Next twice. The Design a query page opens.

    In some databases, tables are organized in schemas. The Database view displays the hierarchy of schemas. You can expand each schema to see tables and views.

  3. In the Database view pane, expand Sales, then expand Views, and then expand vSalesPersonSalesByFiscalYears. Select the following columns: FullName and 2004.

  4. Click Run (!) to see a result set.

    The result set shows 14 rows and 2 columns. There is one row for each sales person that displays a name and the sales totals for the year 2004.

  5. Click Next. The Choose a chart type page opens.

  6. Click Pie, and then click Next. The Arrange chart fields page opens.

    In the Available fields pane, there are 2 fields: FullName and ID2004. Column names that begin with a number are converted to dataset field names that begin with the letters "ID" followed by the number.

  7. Drag FullName to the Categories pane. Categories define the number of slices in the pie chart. In this example, there will be 14 slices, one for each salesperson.

  8. Drag ID2004 to the Values pane. ID2004 represents the all sales in the year 2004. The Values pane displays [Sum(ID2004)] because the chart displays the aggregate for each person.

  9. Click Finish.

  10. The chart is added to the design surface.

  11. Click Run to preview the report.

The report displays the pie chart with 14 slices, one for each sales person. The size of each slice represents the sales for 2004 for that sales person.

Next, you can display the sales amount as a percentage of the whole pie.

To display percentages in each slice of the pie chart

  1. Switch to Design view.

  2. Right-click the pie chart and click Show Data Labels. The data labels appear on the chart.

  3. Right-click a label and then click Series Label Properties.

  4. In Label data, from the drop-down box, select #PERCENT.

  5. (Optional) To specify how many decimal places the label shows, type "#PERCENT{Pn}" where n is the number of decimal places to display. For example, to display no decimal places, use "#PERCENT{P0}".

    Note

    Number Format in the Series Label properties dialog box has no effect when you format percentages. This formats the labels as percentages, but does not calculate the percentage of the pie that each slice represents.

  6. Click OK.

  7. Click Run to preview the report.

The report displays the percentage of the whole for each pie slice.

To combine any slices on the pie chart smaller than 5 percent into one slice

  1. Switch to Design view.

  2. On the View tab, in the Show/Hide group, select Properties.

  3. On the design surface, click on any slice of the pie chart. The properties for the series are displayed in the Properties pane.

  4. In the General section, expand the CustomAttributes node.

  5. Set the CollectedStyle property to SingleSlice.

  6. Verify that the CollectedThresholdUsePercent property is set to True.

  7. Verify that the CollectedThreshold property is set to 5.

  8. On the Ribbon, on the Home tab, click Run to preview the report.

In the legend, the category "Other" now exists. The new pie slice combines all the slices that were under 5% into one slice that is 21% of the whole pie.

To add a drawing effect to the pie chart

  1. Switch to Design view.

  2. If the Properties pane is not already opened, open it.

  3. Double-click the pie chart. The series properties for the pie chart are shown in the Properties pane.

  4. In the Properties pane, expand the CustomAttributes node.

  5. Set the PieDrawingStyle to SoftEdge.

    Note

    Drawing effects and 3D effects are exclusive options. If a chart has 3D effects applied, PieDrawingStyle is not available on the Properties pane.

  6. Click Run to preview the report.

Next Steps

You have successfully completed the Adding a Pie Chart to Your Report tutorial.