Lesson 2: Adding Line and Sparkline 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 my seasonal sales compare with sales in previous years?

You will add a line chart and sparkline chart embedded in a matrix to visualize year over year sales data from the EmployeeSalesYearOverYear2008R2 dataset.

In this lesson you will do the following tasks:

  • Display sales for each month for several years on a line chart.

  • Visualize the same data in a matrix by adding a nested sparkline chart.

  • Add custom code to control the colors of the lines in each chart so that the same color represents the same year.

  • Add a rectangle container for explanatory text, the line chart, and the matrix.

  • Add a bookmark from words in the scenario text to the corresponding data region.

Estimated time to complete this tutorial: 15 minutes.

Review the tips about configuring data regions first on the design surface, and then adding them to rectangles. 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 expand the report height to make room for the charts

  1. Select the rectangle that contains informational text about parameters, data sources, and datasets.

  2. Press the down arrow key to move the rectangle down the page. The report automatically increases in height.

  3. If necessary, use Zoom on the Report toolbar to adjust the view of the report design area.

To add and configure a line chart

  1. Insert a Chart below the scenario text boxes.

  2. In Select Chart Type, use the Tooltip to choose the Line chart.

  3. Drag the chart to the left side of the report, under the text box with the toggle. Use CTRL + arrow keys to nudge the chart to the correct location.

  4. Verify that the chart is selected and that the chart properties appear in the Properties pane.

  5. Use SHIFTL + arrow keys to expand width and height of the chart to approximately 3.75in. In the Properties pane, expand Size to verify the height and width.

  6. From the EmployeeSalesYearOverYear2008R2 dataset, do the following:

    1. Drag Sales to Values in the Chart Data pane. This defines the vertical axis.

    2. Drag Month to Category Groups. This defines the horizontal axis.

    3. In the Chart Data pane, right-click Month to configure Category Group Properties. On the Sorting page, sort by MonthNumber. This controls the sort order on the horizontal axis.

    4. Drag Year to Series Groups. This adds one line for each year.

  7. Right-click the chart until the the series is selected, and open Series Properties.

  8. On the Border page, do the following:

    1. Set the Line style to Solid.

    2. Set the Line width to 2pt.

Line colors are assigned automatically from a default color palette. Colors are assigned based on the sort order of the series group.

To configure the chart title and axes

  1. Change the chart title to Yearly Sales Comparison.

  2. Change the location of the series legend to below the horizontal axis.

  3. Delete the Horizontal Axis Title.

  4. Change the vertical axis title to In Thousands.

  5. Open Vertical Axis Properties. Do the following:

    1. On the Number page, in Category, click Currency.

    2. Set Decimal places to 0.

    3. Select Use 1000 separator.

    4. Select Show values in and choose Thousands.

  6. Open Horizontal Axis Properties. On the Axis Options page, do the following:

    1. In Axis type, select Scalar (Number/Dates).

    2. Change Interval to 1.

  7. On the Labels page, select Disable auto-fit and set Label rotation angle (degrees) to 45.

  8. On the Number page, set Category to Date, and Type to *1/31/2000.

To add a matrix

  1. Insert a Matrix beneath the line chart.

  2. Align the left side of the matrix with the left side of the line chart.

  3. From the EmployeeSalesYearOverYear dataset, do the following:

    1. Drag Year to Rows.

    2. Drag Sales to Data. Format the text box as Currency.

    3. Format the header row as needed.

  4. Select the matrix. In the Properties pane, set Bookmark to MatrixwithNestedSparkline.

  5. Format the matrix header as needed.

To add a nested sparkline to a tablix cell

  1. In the matrix, right-click the Sales column, point to Insert Column, and click Outside Group - Right.

  2. Grab the column handle and expand the width to align to the right side of the chart.

  3. Right-click the last cell in the second row, point to Insert, click Sparkline, and use the Tooltip to add Line with Markers.

  4. To configure the sparkline chart, do the following:

    1. From the EmployeeSalesYearOverYear dataset, drag Sales to Values.

    2. Drag Month to Category Groups.

    3. Right-click Month to open the Category Group Properties dialog box.

    4. On the General page, in Synchronize groups in, select Tablix1. The category axis for each sparkline will use the same minimum and maxiumum values based on all group data in the matrix instead of the group data for each row independently.

    5. On the Sorting page, set the Sort by expression to [MonthNumber].

For a nested sparkline, there is only one series group in scope, so the color for each line is based on the first color from the default color palette.

In the next step, you will add custom code to control the order of colors assigned to a series group based on the group expression. In this way, the line for each year will be the same color in both the line chart and the sparkline chart.

To match line colors in the line chart and sparkline

  1. In Report properties, on the Code page, add the following custom code to the report:

    Private colorPalette As String() = {"Blue", "Orange", "Green"}
        Private count As Integer = 0
        Private mapping As New System.Collections.Hashtable()
        Public Function GetColor(ByVal groupingValue As String) As String
           If groupingValue = Nothing Then 
              Return "Black" 
           End If
            If mapping.ContainsKey(groupingValue) Then
                Return mapping(groupingValue)
            End If
            Dim c As String = colorPalette(count Mod colorPalette.Length)
            count = count + 1
            mapping.Add(groupingValue, c)
            Return c
        End Function

    In this example, there are only three years of data, so only three colors are defined.

  2. On the line chart, right-click the lines until the chart series appears in the Properties pane, and then click Series Properties.

  3. On the Fill page, in Color, enter the following expression:

    =IIF(Fields!Year.Value IS NOTHING,"Black",Code.GetColor(Fields!Year.Value))

  4. Repeat steps 2 and 3 for the sparkline chart.

  5. Preview the report.

The line color for each year is the same in the line chart and the sparkline.

To add a rectangle container

  1. Right-click the report Body, point to Insert, and then click Rectangle.

  2. In Rectangle Properties, change Name to rectCharts.

  3. To the rectangle, add a text box named tbChartPurpose with the following text: Compare seasonal sales by year.

  4. Format as needed.

  5. Select the line chart. Drag the upper left hand corner of the chart into the rectangle. The rectangle expands to include the chart.

  6. Grab the bottom handle of the rectangle and drag it down. To select the rectangle, click the chart, and then press ESC until the Properties pane toolbar displays Rectangle1 as the selected object.

  7. Drag the upper left hand corner of the matrix into the rectangle below the chart.

  8. Position the chart and matrix inside the rectangle.

  9. In the Properties pane for the rectangle, set Bookmark to YearlySalesRectangle.

  10. Move the rectangle under the toggle text box.

Use rectangles as containers to make it easier to position multiple related report items.

To add a link from text to a bookmark

  1. Click the text box tbScenario until all text appears.

  2. Highlight the word line, right-click and open the Text Properties dialog box.

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

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

  5. In Select bookmark, enter YearlySalesRectangle.

  6. Repeat steps 2 through 4 for the word sparkline.

  7. In Select bookmark, type MatrixwithNestedSparkline.

To preview and verify the report

  1. Preview the report.

  2. Verify the following:

    1. The label, line chart and matrix with the nested sparkline are the same width and aligned with each other in a rectangle container.

    2. Both the line and sparkline charts display the same data. For example, sales for 2005 occur only during the last half of the year.

    3. The line colors on the line chart and sparkline show the following display for sales data by year: Blue for 2005, Orange for 2006, Green for 2007.

    4. Toggle the scenario text, and then click line. The report jumps to the page that contains the rectangle with the line chart.

    5. Scroll to the top of the report. In the scenario text, click sparkline. The report jumps to the page that contains the matrix with the nested sparkline.

Community Additions