Problem solve Get help with specific problems with your technologies, process and projects.

Putting the final touches on gauges with Report Builder 3.0

Even when your gauge is complete, there are still a few tweaks to be made in the name of data visualization. Learn how to add the right finishing touches with Report Builder 3.0.

Part 1 | Part 2 | Part 3

First, you learned how to set up a gauge in Report Builder 3.0. Then part two explained the role that pointers, scales and ranges play in refining your reports. You're almost done, but first check out some extra tricks you can use to make your reports even more effective.

Once you've configured the pointer, scale and range in Report Builder 3.0, your gauge is ready to go. However, I added a few more elements to the report below just to give it a better look. Figure 14 shows the additional components.

Figure 14 (click to enlarge)

The first thing you might notice is that I've given the report a title, centered the text box, and resized the font. Naturally, you can give the report whatever title you like and display that title however it suits you. Report Builder 3.0 is extremely flexible in this regard and provides a great deal of latitude for refining your visualizations.

Another change I made to the report was to update the footer. I added the ReportName built-in field along with text. The expression in the footer text box now reads as follows:

[&ReportName] report: [&ExecutionTime]

You can find a list of built-in fields in the Report Data pane to the left of the design surface (shown in Figure 14). Now when the report is rendered, the name of the report and the timestamp are displayed. Finally, I added a text box to the report, gave it a border, and set the Color property to LemonChiffon, which is shown in the Properties pane in Figure 14. I then added the following expression to the font's Color property:

=Iif(Sum(Fields!SalesAmount.Value, "InternetSales") < 3300000, "Red", Iif(Sum(Fields!SalesAmount.Value, "InternetSales") < 6700000, "Black", "DarkGreen"))

The expression specifies that if the total sales are less than $3.3 million, the font should be red. If sales are less than $6.7 million (but not less than $3.3 million), the font should be black. Otherwise, the font should be dark green -- that is, if sales are not less than $6.7 million. Once again, for more details about using expressions, refer to Report Builder Help.

In addition to using an expression to determine the font's color, I used the following expression in place of the text:

="Total Internet sales in " & Parameters!CalendarYear.Value & ": " & FormatCurrency(Sum(Fields!SalesAmount.Value, "InternetSales"))

The expression inserts the value from the CalendarYear parameter and the total amount of sales into the text. For example, if the year is 2005, the report will displays information similar to the following:

Total Internet sales in 2005: $3,266,373.66

As you can see, because the sales are less than $3.3 million dollars, the font color is red.

The report is now ready to go. Be sure to save the report—something you should do regularly. To view the report in Report Builder 3.0, click the Run button on the Home ribbon of the menu bar. When the report appears, enter a year (in this case, one from 2005 through 2008), and then click View Report. The report should look similar to the one shown in Figure 15.

Figure 15 (click to enlarge)

In this case, the sales total nearly $9.8 million . As you would expect, the pointer falls within the green range and the font color of the text is green. Of course, your reports will most likely be more complex than this with the look and feel better refined, but you at least now have an idea of how to add a gauge to a report.

As you become more familiar with gauges, you'll want to try out different types and use them in different ways. For example, you can place a gauge inside a matrix or a table or use multiple gauges to compare data. What I've shown you here is just the beginning, but as you can see, gauges provide you with a valuable visualization tool, making your reports much richer and far more effective.

 Part 1: Adding gauges to reports
 Part 2: Configuring pointers, scales and ranges
 Part 3: The final touches

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

This was last published in January 2010

Dig Deeper on Data Visualization Techniques for SQL Server

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.