Share via

  • Article

Tutorial: Adding a Pie Chart to a Report (Report Designer)

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. For more information on Shape charts, see Shape Charts (Report Builder 3.0 and SSRS).

If there are too many data points on a pie chart, your data points may become crowded which reduces the readability of your chart. In this scenario, you may want to consider using a line chart. Consider using pie charts only once you have aggregated your information to several data points or less.

Requirements

To use this tutorial, the system must have the following installed:

  • SQL Server Business Intelligence Development Studio.

    Note

    Business Intelligence (BI) Development Studio is not supported on Itanium-based computers. However, support for BI Development Studio is available for x64-based computers. If the SQL Server sample databases have been deployed on an Itanium-based computer, use BI Development Studio on either an x86-based or x64-based computer to modify and run the samples.

  • SQL Server with the AdventureWorks2008 sample database.

Estimated time to complete this tutorial: 10 minutes.

To define a data source for the pie chart

  1. On the View menu, click Report Data. The Report Data pane appears in your Business Intelligence Development Studio environment.

  2. In the Report Data pane, click New, and then click Data Source.

  3. In the Data Source Properties dialog box, verify that Embedded connection is selected.

  4. Click Edit.

  5. In the Connection Properties dialog box, enter the name of your server in the Server name text box.

  6. Click Use Windows Authentication or click SQL Server Authentication and type the name and password of a user that has permission to access the AdventureWorks2008 database.

  7. In the Select or enter a database name box, type or select the AdventureWorks2008 database.

  8. Click OK. The new data source (DataSource1 by default) is displayed in the Report Data pane.

To define a data set for your report

  1. In the Report Data pane, right-click the data source you created in the previous step, and click Add Dataset.

  2. In the Dataset Properties dialog box, enter the following query in the Query textbox:

    SELECT [FullName] as Name
      ,[2002] as SalesBeforeLast
      ,[2003] as SalesLastYear
      ,[2004] as SalesYTD
  FROM [Sales].[vSalesPersonSalesByFiscalYears]

  3. Click OK. 

To add a pie chart to your report

  1. On the View menu, click Toolbox.

  2. Double-click the Chart icon or drag-and-drop it from the Toolbox to the design surface.

  3. In the Select Chart Type dialog box, click Shape. Select the first pie chart icon in the list.

  4. Drag the Name field from the Report Data pane and drop it in the category fields drop zone.

  5. Drag the SalesYTD field from the Report Data pane and drop it in the data fields drop zone.

  6. Click Preview on the ribbon.

To display percentages in each slice of the pie chart

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

  2. Right-click any label and click Series Label Properties.

  3. In the Series Label Properties dialog box, select #PERCENT in the Label data box.

  4. (Optional) To specify how many decimal places the label should show, specify "#PERECENT{Pn}" where n is the number of decimal places to display. For example, if you want to format the keyword to two decimal places, use the string "#PERCENT{P2}".

    Note

    Number Format in the Series Label properties dialog box will have no effect when you format percentages. This will only format the labels as percentages, but it will not calculate what percentage of the pie chart each slice represents.

To add a drawing effect to the pie chart

  1. On the View tab, choose the Property Pane option.

  2. Click on the pie chart. The series properties for the pie chart are shown in the Property pane.

  3. In the Property pane, expand the CustomAttributes node.

  4. Set the PieDrawingStyle to SoftEdge.

    Note

    If we set the chart to be 3-D the drawing effects are no longer applicable.

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

  1. Open the Properties pane.

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

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

  4. Set the CollectedStyle property to SingleSlice.

  5. Set the CollectedThresholdUsePercent property to True.

  6. Set the CollectedThreshold property to 5.