Tip

Using range charts for visualization with Report Builder 3.0

Part 1 | Part 2 | Part

    Requires Free Membership to View

3 | Part 4 | Part 5

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.

Figure 14 (click to enlarge)

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.

Figure 15 (click to enlarge)

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.

Figure 16 (click to enlarge)

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.)

Continue to part five

 


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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.