Now that you've seen how to work with bar, column and line charts in Report Builder 3.0, it's time to take a look at one more popular chart type – ranges. This article explains when range charts are appropriate in SQL Server Reporting Services, and how to set them up.
Adding a range chart to a report
Now let's look at a slightly different type of chart — the range chart. This type of chart displays ranges of data based on the fields you include in the Values section of the Chart Data pane. For example, Figure 14 shows a Range Column chart that tracks the minimum and maximum sales amounts by month.
For each month, the chart will determine the highest and lowest sales amount, and plot it on the chart with a column for each set of data points. Figure 15 shows the Top and Bottom value properties in the Series Properties dialog box. Notice that the top value uses the Max function, and the bottom value uses the Min function. This, of course, is different from the default Sum function used in most reports.
In addition to the difference in the value fields, I've also used a different expression for the Tooltip property than I used in the previous charts:
="Min/" & FormatCurrency(Min(Fields!SalesAmount.Value)) & " " & "Max/" & FormatCurrency(Max(Fields!SalesAmount.Value))
The tooltip will now display the minimum and maximum values when a user hovers over one of the columns.
One other series property that's set differently from the previous reports is the Color property (found on the Fill page of the Series Properties dialog box). Instead of using the default values, I played with the colors a bit by defining the following expression:
=Iif(Fields!SalesGroup.Value = "Pacific", "Aqua", Iif(Fields!SalesGroup.Value = "Europe", "Yellow", "Blue"))
If the sales group is Pacific, the column will be aqua. If the group is Europe, the color will be yellow. Otherwise, the color will be blue.
Another difference from previous charts is the category groups. For the range chart, I specified two category groups, OrderQuarter followed by OrderMonth. This way, the horizontal axis provides an additional subcategory, although this doesn't change the displayed columns and their values. Also, because the OrderQuarter values are stored as 1, 2, 3, and 4, I modified the Label property with the following expression:
="Quarter " & Fields!OrderQuarter.Value
In this expression, I simply concatenated the text with the value from the OrderQuarter field. Now the label text will include the word "Quarter". Figure 16 shows what the chart will look like when the user views the report for 2007.
As you can see, each month shows three columns that reflect the range between minimum and maximum sales amount. The chart also reflects my color settings, and the quarters listed beneath the horizontal axis. (Note that the sample data in the AdventureWorks warehouse is not the best, but it still gives you an idea of how the chart works.)
USING CHARTS WITH REPORT BUILDER 3.0
Part 1: Adding charts
Part 2: Creating bar charts
Part 3: Generating column and line charts
Part 4: Using range charts
Part 5: Utilizing shape charts
ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at http://www.rhsheldon.com.
This was first published in January 2010