• Article

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

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.

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

You must have the following prerequisites to complete this tutorial:

Estimated time to complete this tutorial: 15 minutes

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 bar chart to the report.

To add a bar chart with the Chart wizard

  1. On the Insert ribbon, click Chart, and then Chart Wizard. Click and drag in the design area to specify the chart outline.

    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.

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

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

    The result set shows 14 rows and 3 columns. There is one row for each sales person. Each row displays a name and the sales totals for the years 2002, 2003, and 2004.

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

  5. Click Bar, and then click Next. The Arrange chart fields page opens.

    In the Available fields pane, there are 3 fields: FullName, ID2003, 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.

  6. Drag FullName to the Categories pane. Categories define the number of possible labels along the vertical axis.

  7. Drag ID2003 and ID2004 to the Values pane. These fields represent the all sales for the years 2003 and 2004. By default, each numeric field in the Values pane is summarized by using the Sum function.

  8. Click Finish.

  9. The chart is added to the design surface.

  10. Click Run to preview the report.

The chart does not label every category on the vertical axis. By default, an automatic algorithm calculates an interval in order to prevent overlapping labels. To control the number of labels, you can specify an interval. For example, to show all labels, set the interval to 1.

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

  1. Switch to Design view.

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

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

  4. Click OK.

  5. Click Run to preview the report.

    Note

    If you cannot read the salesperson names on the vertical axis, consider increasing the size of your chart or changing the formatting options for the axis labels.

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.

    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. Click Run to preview the report.

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. Double-click the chart title at the top of the chart, and select Title Properties.

  3. Replace the title text with the following text: Sales for 2003 and 2004.

  4. Click anywhere outside the text.

  5. Click Run to preview the report.

Next, you can display the sales amounts as currency.

To format as currency the numbers on the horizontal axis

  1. Switch to Design view.

  2. Click the horizontal axis along the bottom of the chart to select it.

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

  3. Click Run to preview the report.

    The report displays the sales amount on the horizontal axis as currency.

Next, you can set a filter to display the top five values.

To filter the top five values on the bar chart

  1. Switch to Design view.

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

  3. In the category drop zone, right-click the [FullName] field, and then click Category Group Properties.

  4. Click Filters. The Change filters page displays a list of filter expressions. By default, this list is empty.

  5. Click Add. A new blank filter appears.

  6. In Expression, type [Sum(ID2004)]. This creates the underlying expression =Sum(Fields!ID2004.Value).

  7. Verify the data type is Text.

  8. In Operator, select Top N.

  9. In Value, type the following expression: =5

  10. Click OK.

  11. Click Run to preview the report.

The chart shows the top five salesperson names from the sales data.

Next, you can sort the names on the vertical axis.

To sort the names in alphabetical order on the bar chart

  1. Switch to Design view.

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

  3. In the category drop zone, right-click [FullName], and then click Category Group Properties.

  4. Click Sorting. This Change sorting options page displays a list of sort expressions. By default, this list is empty.

  5. Click Add. A new sort expression appears.

  6. In Sort by, select [FullName]

  7. In Order, select Z to A. Select reverse alphabetical order so that the names appear in order from top to bottom.

  8. Click OK.

  9. Click Run to preview the report.

The names on the horizontal axis are sorted.

Next Steps

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