In the last of this three-part SQL Server Reporting Services recipe series, Wrox author Paul Turley shows how to create the graphics for word-size graphics that visualization pioneer Edward Tufte dubbed sparklines.
This is the third in a series of three articles highlighting the Report Solution Patterns and Recipes from Chapter 7 of Professional SQL Server 2005 Reporting Services.
As we have endeavored to solve various business problems, we've learned to do some interesting things with Reporting Services. On consulting engagements, I often find myself in front of a client who is asking questions like "can you do this or that?" Almost inevitably, the answer is "yes," but the question becomes what the best method would be to meet the requirement. With a little outside-the-box thinking, a lot of interesting things are possible. This may involve some custom programming, embedding report items or using customer application components in concert with Reporting Services.
In the following section, I've compiled a description of reporting challenges and solutions we've encountered, developing reports for our clients. For each "solution recipe," I provide a brief list of skills, techniques, and resources needed to apply the report feature. This should give you a good idea about how prepared you may be to use the techniques based on your skill set and the level of complexity. Some of these are easy to duplicate and others require more advanced skills, which may include Transact-SQL and Visual Basic programming. These are not intended to be exercises or step-by-step instructions. I have made a point to provide enough information to demonstrate the concepts and techniques. However, to implement these solutions you will need to apply the skills you learned in the previous chapters.
Edward Tufte, one of the more recognized experts on the subject of data visualization, presents the idea of sparklines. These are simple, word-sized graphics that are an alternative to large, busy charts used to communicate a simple trend or series of measurements. In order to be meaningful, charts sometimes need to have annotated gridlines, point labels and legends. However, some charts can effectively serve their purpose without the use of supporting text labels. To illustrate observations like "sales are improving," "a product is profitable," or that a trend is cyclical, a simple trend chart needs little or no labeling. Sparklines are best used when embedded in text or other report formats.
What you'll need:
- A query expression used to return trend data
- A small, simplified chart item
- A table item to display master rows
Column and line charts are best suited for this type of presentation. In the first of two examples, I'll use a column chart to show sports games scores for a team throughout the season. The first example uses data I had on-hand from a project. The second example will use sample data from the AdventureWorks database.
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 2 shows the category group. This plots columns along the X axis, one for each game.
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.
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.
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.
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.
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.
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.
Sparklines - Sales Trends
This example shows product category sales on each row and sales by year in an associated line chart, plotting sales totals by month. This report's dataset is based on a simple query that returns aggregated sales by year, month, and then by product category.
In Figure 9, I've added and setup the table and chart in separate areas of the report body. They're both bound to the same dataset. After the table is configured, I'll add it to the table. Like the previous example, a group header row is used in-place of the detail row.
I've configured the chart with no gridlines or labels at all. Its purpose is to show relative sales trends, not specific values. In a production reporting solution, I might create separate chart report, similar to the sparkline chart but with more detail. Figure 10 shows this report in design view.
Finally, Figure 11 shows the finished report. The trend line shows sales total over the course of the year. Whether data point represented days, weeks or months, the effect would be the same.
This article is adapted from Professional SQL Server 2005 Reporting Services by Paul Turley, Dave DuVarney, James Counihan and Todd Bryant (Wrox, 2006, ISBN: 0-7645-8497-9), from Chapter 7, "Report Solution Patterns and Recipes," by Paul Turley.
Copyright 2006 by WROX. All rights reserved. Reproduced here by permission of the publisher.