• Article

Lesson 4: Adding Column and Databar Charts (SSRS)

In this lesson, you will learn to add report items to the Employee_Sales_Summary_2008R2 report that help answer the following question for an AdventureWorks salesperson:

  • How do the sales for each product category for this month compare to seasonal sales in previous years?

You will add a column chart to and databar chart to visualize data from the EmpSalesMonth2008R2 dataset.

What You Will Learn

In this lesson you will do the following tasks:

  • Display sales for each category for one month for each of several years on a column chart and also in a table with a databar chart.

  • Place both charts as peers on the report design surface to control where the charts appear on the rendered report.

Estimated time to complete this tutorial: 20 minutes.

Requirements

For information about requirements, see Prerequisites for AdventureWorks 2008R2 Sample Reports (SSRS).

This lesson assumes that you have completed Creating the Report Server Project and the AdventureWorks2008R2_Base Report (SSRS) to create the report project, shared data source, and shared datasets, and Lesson 3: Adding a Matrix, Variables, and Indicators as KPIs (SSRS).

Tips

Review the tips about viewing the hierarchical relationships among report items by using Document Outline pane. For more information, see Report Design Tips (Report Builder 3.0 and SSRS).

To open the project and report

  1. In Business Intelligence Development Studio, open the report server project AdventureWorks 2008R2 Sample Reports that you created in the previous lesson.

  2. Open the Employee_Sales_Summary_2008R2 report.

To add and configure a column chart

  1. Add a Column chart to the report under the rectangle named rectCharts.

  2. From the Report Data pane, from the dataset EmpSalesMonth2008R2, do the following:

    1. Drag Sales to Values.

    2. Drag Category to Category Groups.

    3. Drag Year to Series Groups.

  3. In the Properties pane, set Bookmark to CategoriesColumnChart.

To configure the chart title and axis

  1. In Title Properties, replace the chart title with the following expression: =Parameters!ReportMonth.Label & " Sales Comparison"

  2. Change the Vertical Axis Title to In Thousands.

  3. Open the Vertical Axis Properties.

    1. On the General page, in Scale options, select Enable scale breaks.

    2. On the Number page, format the values as Currency.

    3. Set Decimal places to 0.

    4. Select Use 1000 separator.

    5. Select Show values in, and click Thousands.

    6. In the Properties pane, verify that Chart Axis properties are displayed.

    7. Expand ScaleBreakStyle, and set BorderColor to Silver.

  4. Delete the Horizontal Axis Title.

  5. Open the Horizontal Axis Properties. On the Labels page, select Disable auto-fit and set Label rotation angle (degrees) to 45.

  6. Move the legend to below the horizontal axis.

To add a matrix with a databar

  1. Add a matrix next to the column chart. Use snaplines to align the top of the matrix and the chart.

  2. From the Report Data pane, from the dataset EmpSalesMonth2008R2, drag Category to Rows.

  3. Right-click in the Data cell, click Insert, click Data Bar, and then click Stacked Bar.

  4. In the Chart Data pane, do the following:

    1. In the Values pane, click Add field (+), and then click Sales.

    2. In the Series Groups pane, click Add field (+), and then click Year.

  5. In the matrix, in Columns, create a Placeholder named Title and set its value to =Parameters!ReportMonth.Label & " Sales Comparison for Each Year".

  6. Expand the width of the column that contains the data bar.

  7. Select the matrix and in the Properties pane, set Bookmark to MatrixwithNestedDatabarChart.

  8. Format the matrix as needed.

Add a Rectangle Container for the Matrix

Add a rectangle container for the matrix and the text that explains the purpose of the matrix.

To add a rectangle container

  1. In the report Body, insert a Rectangle named rectSeasonalSales.

  2. To the rectangle, add a text box with the following text: Compare seasonal sales for [@ReportMonth.Label] for each category.

  3. Format as needed.

  4. Drag the chart to the rectangle. The rectangle expands as needed.

  5. Drag the matrix with the databar chart to the rectangle and position it next to the column chart.

  6. Move this rectangle below the existing rectangles, and use snaplines to align the sides of the rectangle and its report items to the other report items on the page.

To add a link from text to a bookmark

  1. Select the text in the text box tbScenarioText.

  2. With the mouse, highlight the word column.

  3. Right-click and open the Text Properties dialog box.

  4. On the Font page, set Color to Blue and Effects to Underline.

  5. On the Action page, click Go to bookmark.

  6. In Select bookmark, type CategoriesColumnChart.

  7. Repeat steps 3 through 5 for the word databar.

  8. In Select bookmark, type MatrixwithNestedDatabarChart.

To preview and verify the report

  1. Preview the report.

  2. Verify the following:

    1. The column chart and the databar display the same information.

    2. In the scenario text, the column link jumps to the page that contains the column chart.

    3. In the scenario text, the databar link jumps to the page that contains the databar chart.

  3. Deploy the report.

Next Steps

You have completed building this report. To build other AdventureWorks sample reports, see Tutorials: Creating AdventureWorks 2008R2 Sample Reports (SSRS).

See Also

Other Resources

Tutorials (SSRS)