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

A column chart displays a series as a set of vertical bars that are grouped by category. A column chart can be useful to:

• Show data changes over a period of time.

• Compare the relative value of multiple series.

• Display a moving average to show trends.

What You Will Learn

In this tutorial, you will learn how to:

• Add a column chart to the report that you created in Tutorial: Creating a Basic Table Report (Report Builder 2.0). The column displays data from the existing dataset.

• Format the axis labels, title, and legend of the chart.

• Add a moving average for sales.

Estimated time to complete this tutorial: 15 minutes

Requirements

You must have the following prerequisites to complete this tutorial:

• You must have access to the Sales Order report that you created in Tutorial: Creating a Basic Table Report (Report Builder 2.0).

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 column chart

1. On the Ribbon, on the Insert tab, click Chart, and then Chart Wizard, thenclick and drag in the design surface to specify the chart outlines. The Choose a Dataset page opens.

2. In Choose an existing dataset in this report, verify that DataSet1 is selected.

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

   The column chart is the default chart type.

4. Click Next. The Arrange chart fields page opens.

5. Drag OrderDate to Categories. Categories display on the horizontal axis.

6. Drag LineTotal to Values. The Values pane displays Sum(LineTotal) because the sum of the line total value is aggregated for each sales order date. Values display on the vertical axis.

7. Click Finish.

   The chart is added to the design surface. Drag the chart next to the table. Use the snap lines to align the top of the chart with the top of the table.

8. Click the chart to display the chart handles. Drag the bottom-right corner of the chart to increase the size of the chart.

9. Click Run to preview the report.

   The report displays both the table and the chart next to each other. The chart format makes it hard to read. Next, you can change the default format for the dates that display on the horizontal axis to improve how easy it is to read the chart.

To format a date on the horizontal axis

1. Switch to Design view.

2. Right-click the horizontal axis, and then click Axis Properties.

3. Click Number.

4. In Category, select Date.

5. In the Type pane, select January 31, 2000.

6. Click OK. 

7. Click Run to preview the report.

   The date displays in the date format that you selected. Notice that the chart does not label every category on the horizontal axis. By default, only labels that fit next to the axis are included.

   You can customize the label display by rotating the labels and specifying the interval.

To rotate the axis labels and change the display interval along the horizontal axis

1. Switch to Design view.

2. Right-click the horizontal axis and then click Axis Properties.

3. In the Category Axis Properties dialog box, on the Axis Options page, type 3 for Interval.

4. Click Labels.

5. In the Change axis label auto-fit options, select Disable auto-fit.

6. In Label rotation angle, select 90.

7. Click OK.

   The sample text for the horizontal axis rotates by 90 degrees.

8. Click Run to preview the report.

   In the AdventureWorks2008 sample database, store sales are report on the first of every month. On the chart, the labels are rotated and every third month is shown.

   Next, you will learn how to change the position or display for the chart legend.

To move the legend below the chart area of a column chart

1. Switch to Design view.

2. Right-click the legend on the chart.

3. Select Legend Properties.

4. For Legend position, select a different position. For example, set the position to the middle bottom option.

   When the legend is placed at the top or bottom of a chart, the layout of the legend changes from vertical to horizontal. You can select a different layout from the Layout drop-down list.

5. Click OK.

6. (Optional) Because there is only one category in this tutorial, the legend is not needed. To remove the legend, right-click the legend and then click Delete Legend.

7. Click Run to preview the report.

   Next, you can change the chart title.

To change the chart title above the chart area of a column chart

1. Switch to Design view.

2. Right-click the chart title at the top of the chart and click Title Properties.

3. Replace the Title text field with the following text: Store Sales Order Totals.

4. Click Run to preview the report.

   Next, you can format the sales total as a currency on the vertical axis.

To format as currency the numbers on the vertical axis

1. Switch to Design view.

2. Double-click the labels on the vertical axis along the side of the chart to select it

3. On the Ribbon, on the Home tab, in the Number group, click the Currency button. The axis labels change to show the currency format.

4. Click Run to preview the report.

   Next, you can change the default axis labels.

To change the axis labels

1. Switch to Design view.

2. Right-click the vertical axis title along the side of the chart and click Axis Title Properties.

3. Replace the Title text field with the following text: Sales Total. You can also specify a variety of options related to how the title is formatted.

4. Click OK.

5. Right-click the horizontal axis title, and then click Show Axis Title to toggle off the title. Because the horizontal axis displays dates, the title is not needed.

6. Click Run to preview the report.

   Next, you can add a moving average to the chart to display the sales trend.

To add a moving average

1. Switch to Design view.

2. Double-click the chart to display the category, series, and data drop zones.

3. Right-click the [Sum(LineTotal)] field that is in the data fields drop zone, and then click Add Calculated Series.

4. In Formula, verify that Moving average is selected.

5. In Set Formula Parameters, for Period, select 4.

6. Click Border.

7. In Line width, select 3pt.

8. Click OK.

9. Click Run to preview the report.

   The chart displays a line that shows the moving average for total sales by date, averaged over every four dates.

   Lastly, you can save your report.

To save the report

1. Switch to Design view.

2. From the Report Builder button, click Save As.

3. In Name, type Sales Order with Column Chart.

4. Click Save.

   Your report is saved on the report server.

Next Steps

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

See Also

Concepts

Getting Started with Report Builder 2.0

Other Resources

Tutorials (Report Builder 2.0)