Wrox Home  
Search
Professional SQL Server 2005 Reporting Services
by Paul Turley, Todd Bryant, James Counihan, Dave DuVarney
March 2006, Paperback
US $39.99 Add To Cart


Excerpt from Professional SQL Server 2005 Reporting Services

SQL Server Reporting Services: Chart Reports

The charting capabilities in SQL Server Reporting Services are quite impressive and as easy to use as those in Excel or Access, and, in many ways, they are more powerful. The charting components are based on Dundas Charts, developed by Dundas Software. Dundas provides a suite of ASP.NET charting components that have been available for .NET developers for several years. A chart item is based on a data set just like any data range and can use groups, query parameters, and filters in much the same way as a table, list, or matrix.

So, why use a chart to present data? After all, isn't a chart simply a graphical representation of a group of numbers? Wouldn't rows and columns of values be just as effective? To fully understand the impact and perhaps the importance of presenting information graphically, it's important to understand the needs and objective of the report reader and how the information will be used. For now, just consider some basic observations about report usage and the people who read these reports.

Analyzing information is usually a process rather than a single event. Regardless of the type of business or industry, users typically approach business information in stages. First it's important to consider the different roles of users in order to understand their respective stages of information discovery. Some may have a specific task they perform and the information they use will be focused on that task. Other users may be leaders and decision makers in various capacities, whose objectives are more broad and complex.

Consider the CEO whose first objective is often to find out whether there are any disasters to address. This executive isn't concerned with specific details or even short-term trends but in getting a meter reading on the business. After the CEO learns that there are no fires to put out, the next objective is to get a broad view of sales and productivity trends for different areas of the business. Typically, one of the most important questions addressed by effective business reporting solutions is How are we doing? Depending on the size and type of business, a high-level leader may also be interested in understanding some of the lower-level details regarding operations, production, sales, and other business specifics. Executives typically benefit from dashboard-type reports that provide high-level status information they use to take periodic business meter readings. Executives also need access to more detailed information to be used for occasional follow-up but will usually get their information from others.

Contrast the perspective of the CEO with the operational business leaders: the sales manager, marketing director, or production manager. These people need to have their finger on the pulse of specific business areas. They will be concerned with short- and long-term trends in their respective areas of responsibility. Questions to be answered for operational leaders might be What products or campaigns are successful and which are not? and Who are my top (and bottom) producers? Unlike the CEO, these individuals must be connected with every aspect of their microorganization and must be armed with detailed, accurate information so that they can make proactive decisions.

Consider that some users may need to have information spoon-fed to them in a specific format, while others may want to explore data, pivoting, sorting, and grouping it themselves.

Chart Types

Some of the more common chart types (like Column, Bar, Line, and Area) can be used for different views of the same data. Pie and doughnut charts present a more simplified view and work well with fewer dimensions. Other charts are more specialized and may be appropriate for multi-value data points, range values, and variances.

When a report is viewed, the chart output is rendered to a bitmap and streamed to a PNG-type image. This image is then linked or embedded in the report. There are nine general chart types available, and these are described in the following table.

Chart Type Description
Column This is a classic vertical bar chart with columns representing values along the Y-axis. Like-valued items along the X-axis are grouped together, and bars representing the same X-axis values in each group have the same colors or patterns. Series values may also be grouped and subgrouped. Columns can have point labels and the colored bars may be labeled using a legend. Columns may be arranged side by side (along the X-axis) or in front of one another (along the Z-axis.) Columns may appear to be extruded from their base using a rectangular or circular (cylindrical) shape.
Bar This functionality is the same as a column chart turned 90 degrees. It has the advantage of more accurately depicting value comparisons for layouts where you have more available horizontal space.
Area Like a column chart with a trend line drawn from one point to the next in the series. This type of chart is appropriate for a series of values that tend to progress over a relatively even plane that describes a "level," "up," or "down" trend. It is not at all appropriate for series values that tend to jump around. The solid shading of the charted area depicts a volume of data values.
Line Like the area chart, but the area of the charted area isn't filled. This type of chart is useful for comparing multiple series (along the Z-axis) without obscuring trend lines behind a series.
Pie The classic pie chart is an excellent tool for comparing relative values. Unlike bar, column, line, and area charts, the aggregate value isn't quantified. Users understand pie charts because they put comparative values into a proportional context and can drive quick decision support at a glance. Pie chart views can be exploded to visually separate each slide.
Doughnut A doughnut is a pie with a hole in the middle. A three-dimensional doughnut rendering may expose smaller slices more clearly than a pie chart since each slice has four sides rather than three.
Scatter Plots several points in a range (both X and Y) to show trends and variations in value. The result is more like a cloudy band of points rather than a specific aggregated point or line.
Bubble This chart is a technique for charting points on three dimensions. Values are plotted using different-sized points, or bubbles, on a two-dimensional grid. The size of the bubble indicates the related value along the Z-axis.
Stock This chart plots values vertically like a column chart. For each item along the Y-axis series, a vertical line indicates a start and end value for the range. A tick mark in the line can indicate a significant value in that range or an aggregation of the range. This type of chart is useful for showing trading stocks with opening, closing, and purchase values; wholesale, retail, and discount prices; and the like.

Column Charts

Figure 1 is an example of a simple column chart. The X-axis series values are product categories, and the Y-axis values represent annual sales revenue. In this view, the legend at the bottom indicates the X-axis series values. Several visual elements can be modified to alter the color, shading, borders, text, formatting, labeling, and value placement. This figure shows generally default property settings.

Figure 1
Figure 1

Figure 2 shows the same chart with 3-D modeling.

Figure 2
Figure 2

3-D modeling may be used to show data in a more interesting presentation, but this can also be distracting and less effective for analysis. Figure 3 shows a more extreme 3-D view of the same data with perspective. This chart was up with a fairly extreme 3-D and perspective view, just to show you what can be done. This type of view tends to distort the values, and the clustering (stacking the columns along the Z-axis) can hide some columns from view.

You have control over several 3-D properties to generate more realistic representation of the chart data. Be careful to maintain the appropriate balance between artistry and accuracy. Notice that it's difficult to quantify and distinguish the difference in height between the front-right column and the rightmost column in the back. The degree to which it makes sense to use these features will depend largely on the purpose of the chart. Is it sufficient to demonstrate that one data point is less than or greater than another, or do these points need to be strictly measurable? This type of view can be effective for making an impact, but a flatter view is usually more appropriate to maintain accuracy.

Figure 3
Figure 3

Figure 4 is a 3-D view with cylindrical columns arranged in a clustered formation. When used correctly and in appropriate moderation, this 3-D chart adds a sense of realism while remaining readable.

Figure 4
Figure 4

Stacked Charts

Column and bar charts may have their bars stacked. This appends the different-colored bars (for a like series value) into one bar with multiple colored bands. This may be an appropriate method for showing the accumulation of all values within the series point. The individual values are displayed in a different color as a percentage of the bar. In essence, each bar becomes like a linear pie chart (see Figure 5).

Figure 5
Figure 5

To emphasize the proportion of like values rather than the comparative accumulation, the 100% stacked view (not pictured) will make all of the bars in the chart the same length rather than depicting the sum of all the values in the bar.

Area and Line Charts

An area chart plots the values of each point and then draws a line from point to point to show the progression of values along the series. This is an effective method for analyzing trends and works well when values tend to climb, decline, or remain level in the series. This type of chart is accurate when data exists for all category values on the X-axis. It typically doesn't work well to express a series of values that are not in a relatively uniform plane. Figure 6 is an example of an area chart.

Figure 6
Figure 6

The line chart is a variation of an area chart using a line or ribbon rather than a solid area. The line chart works better than the area chart for comparing multiple categories for a series of values because one layer may obscure another in the area view. In the preceding example, the area chart works because values are sorted in a way that larger values are in the background and other points in the foreground are smaller; the trend increases back to front.

Pie Charts

A pie chart is an excellent tool for comparing proportional values. Display options for a pie chart include exploded and 3-D views. The 3-D pie chart in Figure 7 clearly shows that Touring Bike sales are a small percentage, around 10% of total Bike Sales, and that Road Bike sales account for about half of the total sales. (This piece could be called "PacMan Gets a Root Canal.")

Figure 7
Figure 7

A doughnut chart is a pie chart with a hole in it. This is a rather profound concept, isn't it? Actually, in cases where there may be several smaller slices, the donut chart can be a little easier to read and provides a little variation on an age-old chart theme. The chart shown in Figure 8 is the same as the previous chart without the exploded view and a legend showing the series labels.

Figure 8
Figure 8

Pie charts are traditionally used to show multiple slices representing their data point percentage of the whole. In the usual form, data values grouped on another axis will result in slices automatically generated with the same style settings and contrasting colors from a standard color palette. There are eight color palettes provided in the designer. Sometimes, data may need to be presented as a percentage value or you may simply have two values and need to express one as a percentage of the other. This is possible by adding multiple Value groups to the chart with each representing a specific slice. In Figure 9, only two values are presented. In this example, values in the data set exist for Bike Sales and Total Sales. Using an expression or a calculation in the query, subtracting Bike Sales from the total provides a value for Other Sales.

Figure 9
Figure 9

Figure 10 shows a specific group that was created for these two values. Another advantage of using this approach is that you can set the color and styles for each slice independently.

Figure 10
Figure 10

Bubble Charts

Bubble charts are essentially a point plotted in a grid representing three dimensions. The value of the Z-axis is expressed by the size of the bubble. Imagine that the bubble exists in a 3-D plane and will appear large if it is closer to you. Actually the "bubble" can be a circle, square, triangle, diamond, or cross shape. This also means that a combination of shapes may be used to represent different data elements in the same chart space.

In Figure 11, employees' vacation and sick hours are plotted above their names. The number of vacation hours is represented by the bubble's vertical distance from the 0 baseline, and the number of sick hours is represented by the size of the bubble.

Figure 11
Figure 11

The chart shown in Figure 12 is a stock chart. As you see, for each product, a line is plotted to span a range of values and has a large tick mark to indicate the position of a value within the high-low range. In this example, the beginning (lowest point of the line) of the range is the standard cost of the product. The tick mark represents the last receipt cost, and the high range of the line is the list price.

Figure 12
Figure 12

The Anatomy of a Chart

There is typically a lot of detail work involved in chart design and many properties to manage. Figure 13 shows the major property groups for charts. Although some charts have a few unique properties and some may not support all, generally these are shared across all chart types.

Figure 13
Figure 13

After the chart is placed in the report body, fields may be dragged from the Dataset window directly onto the chart design surface. At minimum, a chart should have one aggregated field for the value and one grouped field for the category. The category and series groups represent the X- and Y-axis in bar, column, line, area, and point charts.

Figure 14 shows four fields dropped onto this line chart in the designer. The ExtendedAmountSum field will provide the data point values. Distinct ProductCategory values will group data along the series. Two fields were dropped onto the chart category area. This creates two related groups on this axis.

Figure 14
Figure 14

Figure 15 shows the same chart configuration on the Data tab of the Chart Properties dialog. The chart groups created using the previous method may be modified here. Specific properties related to the category group(s) are accessible from the Grouping and Sorting dialog after you click the Edit button in this section.

Figure 15
Figure 15