Tutorial: Add a column chart to your report (Report Builder)
In this tutorial, you create a Reporting Services paginated report with a column chart displaying a series as a set of vertical bars grouped by category.
Column charts are useful to:
- Show data changes over a period of time.
- Compare the relative value of multiple series.
- Display a moving average to show trends.
The following illustration shows the column chart you create in this tutorial, with a moving average.
Note
In this tutorial, the steps for the wizard are consolidated into one procedure. For step-by-step instructions about how to browse to a report server, choose a data source, and create a dataset, see the first tutorial in this series: Tutorial: Create a basic table report (Report Builder).
Estimated time to complete this tutorial: 15 minutes.
Requirements
For information about requirements, see Prerequisites for tutorials (Report Builder).
1. Create a chart report from the Chart Wizard
In this section, you go through the Chart Wizard to create an embedded dataset, choose a shared data source, and create a column chart.
Note
The query in this tutorial contains the data values, so it doesn't need an external data source. This makes the query quite long. In a business environment, a query would not contain the data. This is for learning purposes only.
Create a chart report
Start Report Builder either from your computer, the Reporting Services web portal, or SharePoint integrated mode.
The New Report or Dataset dialog box opens.
If you don't see the New Report or Dataset dialog box, on the File menu > New.
In the left pane, verify that New Report is selected.
In the right pane, select Chart Wizard.
On the Choose a dataset page, select Create a dataset, and then choose Next.
On the Choose a connection to a data source page, select an existing data source. Or browse to the report server, and choose a data source. Then select Next. You might need to enter a user name and password.
Note
The data source you choose is unimportant, as long as you have adequate permissions. You don't get data from the data source. For more information, see Alternative ways to get a data connection (Report Builder).
On the Design a query page, select Edit as Text.
Paste the following query into the query pane:
SELECT CAST('2015-01-01' AS date) AS SalesDate, CAST(54995.21 AS money) AS Sales UNION SELECT CAST('2015-01-05' AS date) AS SalesDate, CAST(64499.04 AS money) AS Sales UNION SELECT CAST('2015-02-11' AS date) AS SalesDate, CAST(37821.79 AS money) AS Sales UNION SELECT CAST('2015-03-18' AS date) AS SalesDate, CAST(53633.08 AS money) AS Sales UNION SELECT CAST('2015-04-23' AS date) AS SalesDate, CAST(24019.3 AS money) AS Sales UNION SELECT CAST('2015-05-01' AS date) AS SalesDate, CAST(93245.5 AS money) AS Sales UNION SELECT CAST('2015-06-06' AS date) AS SalesDate, CAST(55288.0 AS money) AS Sales UNION SELECT CAST('2015-06-16' AS date) AS SalesDate, CAST(68733.5 AS money) AS Sales UNION SELECT CAST('2015-07-16' AS date) AS SalesDate, CAST(24750.85 AS money) AS Sales UNION SELECT CAST('2015-08-23' AS date) AS SalesDate, CAST(43452.3 AS money) AS Sales UNION SELECT CAST('2015-09-24' AS date) AS SalesDate, CAST(58656. AS money) AS Sales UNION SELECT CAST('2015-10-15' AS date) AS SalesDate, CAST(44583. AS money) AS Sales UNION SELECT CAST('2015-11-21' AS date) AS SalesDate, CAST(81568. AS money) AS Sales UNION SELECT CAST('2015-12-15' AS date) AS SalesDate, CAST(45973. AS money) AS Sales UNION SELECT CAST('2015-12-26' AS date) AS SalesDate, CAST(96357. AS money) AS Sales UNION SELECT CAST('2015-12-31' AS date) AS SalesDate, CAST(81946. AS money) AS Sales(Optional) Select the Run button (!) to see the data your chart is based on.
Select Next.
2. Choose the chart type
You can choose from several predefined chart types, and then modify the chart after you complete the wizard.
Add a column chart
On the Choose a chart type page, the column chart is the default chart type. Select Next.
On the Arrange chart fields page, drag the SalesDate field to Categories. Categories display on the horizontal axis.
Drag the Sales field to Values. The Values box displays Sum(Sales) because the sum of the sales total value is aggregated for each date. Values display on the vertical axis.
Select Next.
Select Finish.
The chart is added to the design surface. The new column chart just shows representational data. The legend reads Sales Date A through F, just to give an idea of what your report should like.
Select the chart to display the chart handles. Drag the bottom-right corner of the chart to increase the size of the chart. The report design surface increases in size to accommodate the chart size.
Select Run to preview the report.
The chart doesn't label every category on the horizontal axis. By default, only labels that fit next to the axis are included.
3. Format a date on the horizontal axis
By default, the horizontal axis displays values in a general format that is automatically scaled to fit the size of the chart.
Switch to report design view.
Right-click the horizontal axis > Horizontal Axis Properties.
On the Number tab, in Category, select Date.
In the Type box, select 31 Jan 2000.
Select OK.
On the Home tab, select Run to preview the report.
The date displays in the date format that you selected. The chart still doesn't label every category on the horizontal axis.
You can customize the label display by rotating the labels and specifying the interval.
4. Rotate the axis labels on the horizontal axis
Switch to report design view.
Right-click the horizontal axis title, then select Show Axis Title to remove the title. Because the horizontal axis displays dates, the title isn't needed.
Right-click the horizontal axis > Horizontal Axis Properties.
On the Labels tab, under Change axis label auto-fit options, select Disable auto-fit.
In Label rotation angle, select -90.
Select OK.
The sample text for the horizontal axis rotates by 90 degrees.
Select Run to preview the report.
On the chart, the labels are rotated.
5. Move the legend
The legend is automatically created from category and series data. You can move the legend below the chart area of a column chart.
Switch to report design view.
Right-click the legend on the chart > Legend Properties.
Under Layout and Position, select a different position. For example, select the bottom middle 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 in the Layout box.
Select OK.
(Optional) Because there's only one category in this tutorial, the chart doesn't need a legend. To remove it, right-click the legend > Delete Legend.
Select Run to preview the report.
6. Title the chart
Switch to report design view.
Select the words Chart Title at the top of the chart, then enter Store Sales Order Totals.
Select Run to preview the report.
7. Format and label the vertical axis
By default, the vertical axis displays values in a general format that is automatically scaled to fit the size of the chart.
Switch to report design view.
Select the labels on the vertical axis on the left side of the chart to select them.
On the Home tab > Number group, select the Currency button. The axis labels change to show the currency format.
Select the Decrease Decimal button two times, to show the number rounded to the nearest dollar.
Right-click the vertical axis > Vertical Axis Properties.
On the Number tab, Currency is already selected in the Category box, and Decimal places is already 0 (zero).
Check Show Values in. Thousands is already selected.
Select OK.
Right-click the vertical axis > Show Axis Title.
Right-click the vertical axis title > Axis Title Properties.
Replace the text in the Title text field with Sales Total (in Thousands). You can also specify various options related to how the title is formatted.
Select OK.
Select Run to preview the report.
8. Show all the labels on the horizontal (x) axis
You notice that only some of the labels on the x axis are showing. In this section, you set a property in the Properties pane to show them all.
Switch to report design view.
Select the chart, then choose the horizontal axis labels.
In the Properties pane, set LabelInterval to 1.
The chart looks the same in design view.
Select Run to preview the report.
Now the chart displays all its labels.
9. Add a moving average with a calculated series
A moving average is an average of the data in your series, calculated over time. The moving average can identify trends.
Switch to report design view.
Double-click the chart to display the Chart Data pane.
Right-click the [Sum(Sales)] field in the Values area, then select Add Calculated Series.
In Formula, verify that Moving average is selected.
In Set Formula Parameters, for Period, select 4.
On the Border tab, in Line width, select 3pt.
Select OK.
Select 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. For more information, see Add a moving average to a chart in a paginated report (Report Builder).
10. Add a report title
Switch to report design view.
On the design surface, select Click to add title.
Enter Sales Chart. Press ENTER, and then enter January to December 2015, so it looks like this:
Sales Chart
January to December 2015
Select Sales Chart, and on the Home tab > Font section > Bold.
Select January to December 2015, and on the Home tab > Font section > set font size to 10.
(Optional) You might need to make the Title text box taller to accommodate the two lines of text. Pull down on the double-headed arrows when you select in the middle of the bottom edge. And you might need to drag the top of the chart so the title doesn't overlap.
This title appears at the top of the report. When there's no page header defined, items at the top of the report body are the equivalent of a report header.
Select Run to preview the report.
11. Save the report
Switch to report design view.
From the Report Builder button, select Save As.
You can save it either to your computer or to the report server.
In Name, enter Sales Order Column Chart.
Select Save.