Report Recipes
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.
Creating Sparklines
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.