Lesson 2: Adding Line and Sparkline Charts (SSRS)

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.