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

A bar chart can be useful for showing category data horizontally in order to:

• Improve readability of long category names.

• Improve understandability of times plotted as values.

• Compare the relative value of multiple series.

For more information, see Bar Charts (Report Builder 3.0 and SSRS).

What You Will Learn

In this tutorial, you will learn how to:

• Define a data source and add a dataset to the report.

• Add a bar chart to the report.

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

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: 15 minutes

To create a report server project

1. Click Start, point to Programs, point to Microsoft SQL Server 2008 R2, and then click Business Intelligence Development Studio.

2. On the File menu, point to New, and then click Project.

3. In the Project Types list, click Business Intelligence Projects.

4. In the Templates list, click Report Server Project.

5. In Name, type Bar Chart Tutorial.

6. Click OK to create the project.

   The Tutorial project is displayed in Solution Explorer.

To define a data source for the bar chart

1. Click View and select Report Data. The Report Data pane appears in your Business Intelligence Development Studio environment.

2. In the Report Data pane, click the New drop down list, and click Data Source.

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

4. In the Type box, select Microsoft SQL Server.

5. In the connection string text box, type a valid connection string or click the Edit button to open the Connection Properties dialog box.

6. In the Connection Properties dialog box, type or select the name of your server in the Server name text box.

7. Select Use Windows Authentication or SQL Server Authentication and type a user name and password that has permissions to access the AdventureWorks2008 database.

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

9. Click Text Connection.

10. Click OK to close the Connection Properties dialog box. In the Data Source Properties dialog box you will see the default data source name, DataSource1, and the connection string.

11. Click OK. The new data source 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 and click Add Data Set.

2. In the Data Set Properties dialog box, verify that Text is selected for Query type.

3. In the Query text box, enter the following query:

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

4. Click OK. The dataset fields are displayed in the Report Data pane.

To add a bar chart with sales information to your report

1. On the View menu, click Toolbox.

2. Double-click or drag a Chart to the design surface. The Select Chart Type dialog appears.

3. Click Bar to display a list of bar charts. Select the first bar chart icon in the list.

4. Click on the chart to bring up the chart handles. Drag the bottom-right corner of the chart to increase the size of the chart.

5. Drag the Name field from the Report Data pane to the chart. The Chart Data pane will appear next to the chart data. Drop the Name field in the Category Groups area.

6. Drag the SalesYTD and SalesLastYear fields from the Report Data pane and drop them to the Values area.

7. Click Preview to run the report. Your report should look similar to the following example:

You will notice that the chart does not label every category on the y-axis. This is due to an automatic algorithm that calculates an interval at which to display labels in order to prevent label collisions. If you want to display all categories on the chart, you must specify an axis interval of 1.

To display all sales persons along the category axis of a bar chart

1. Switch to Design view.

2. Right-click the y-axis and click Axis Properties.

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

4. Click OK.

   Note

   If the names on the axis are unreadable, consider increasing the size of your chart, or changing formatting options for the axis labels. For more information, see Formatting Axis Labels on a Chart (Report Builder 3.0 and SSRS).

5. Click Preview. Your report should look similar to the following example:

To improve the readability of the chart values, you might want to move the chart legend. For example, in a bar chart, where bars are shown horizontally, you can change the position of the legend so that it is above or below the chart area. This gives more horizontal space to the bars.

To display the legend below the chart area of a bar 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.

   Note

   You can also drag the legend to a new location directly on the design surface.

   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.

   Note

   If the names in the legend are unreadable, you may want to consider increasing the size of your chart, or changing formatting options for the legend. For more information, see Formatting the Legend on a Chart (Report Builder 3.0 and SSRS).

Next, you can change the chart title and axis titles and add formatting.

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

1. Switch to Design view.

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

3. Replace the Title text field with the following text: "Sales Person YTD vs. Last Year".

4. Click anywhere outside the text.

5. Click Preview. Your report should look similar to the following example:

   

Lastly, you can format the sales figures as currencies instead of regular numbers.

To format as currency the numbers on the horizontal axis

1. Switch to Design view.

2. Right-click the horizontal axis along the bottom of the chart and click Axis Properties.

3. In Label Format, under Category, click Currency. You can also specify a variety of options related to how the currency value is formatted.

4. Click Preview.

To filter the top 5 values on the bar chart

1. Switch to Design view.

2. Click the chart once to bring up the Chart Data pane.

3. Right-click the Name field that is in the Category Groups area.

4. Select Category Group Properties.

5. Click Filters. This brings up a list of filter expressions. By default, this list is empty.

6. Click Add. A new blank filter appears.

7. In the Expression box, type the following expression:

   =Sum(Fields!SalesYTD.Value)

8. In the Operator list box, select Top N.

9. In the Value box, type =5

10. Click Preview. The chart shows five names, the top 5 salespeople from the sales data.

To sort the names in alphabetical order on the bar chart

1. Switch to Design view.

2. Click the chart once to bring up the Chart Data pane.

3. Right-click the Name field that is in the Category Groups area.

4. Select Category Group Properties.

5. Click Sorting. This brings up a list of sort expressions. By default, this list is empty.

6. Click Add. A new sort expression appears.

7. In the Sort by box, select the [Name] expression. This is equivalent to the expression =Fields!Name.Value

8. In the Order box, select Z to A.

9. Click Preview. The chart shows the names in alphabetical order from top to bottom.

See Also

Tasks

How to: Specify an Axis Interval (Report Builder 3.0 and SSRS)

Other Resources

Bar Charts

Tutorials (SSRS)