Wrox Home  
Professional SQL Server 2005 Reporting Services
by Paul Turley, Todd Bryant, James Counihan, Dave DuVarney
March 2006, Paperback

Sparklines - Team Standings

The purpose of the chart, shown in Figure 1, is to quantify the team's relative position and win/loss trend, rather than to show specific scores. For this I use a no-frills column chart. The dataset returns a team name, game number and score for each team. The column value will represent the number of points that won or lost the game. For example, a team that wins with a score of 5 to 3 would have a winning score of 2. If the team losses 3 to 4, their score would be -1.

Figure 1

Figure 2 shows the category group. This plots columns along the X axis, one for each game.

Figure 2

The value group uses the calculated score to plot the column above or below the line to indicate a win or loss. Figure 3 shows this calculation in the Edit Chart Value dialog.

Figure 3

Figures 4 and 5 show the X axis and Y axis configuration. The X axis grid lines, tick marks and labels are removed to keep the chart simple.

Figure 4

On the Y axis, lines are displayed at the zero cross point, dividing wins and losses at 10 and -10 to render all chart instances at the same scale.

Figure 5

Since the chart will be quite small, border lines should be thin and subtle. Click the corresponding Style button to show the Style Properties dialog, shown in Figure 6. I'm using Silver color (50% gray) and .5 point lines. These lines may not be displayed in the designer but should render correctly when the report is previewed or deployed.

Figure 6

The chart is placed in a group header row within a table grouped on the team field. This will serve as the detail row since a chart can't reside in a detail row. Figure 7 shows the finished report in design view.

Figure 7

The finished report is shown in Figure 8. For each team, their calculated league standing, average score variance, and the win/loss trend sparkline chart is displayed in table rows.

Figure 8