Part one explained how gauges work in Report Builder 3.0 to create more effective reports with SQL Server Reporting Services, as well as how to set them up. Part two takes a look at three key components of gauges – pointers, scales and ranges – and how they can be used to refine your reports.
Configuring the pointer
The pointer -- as the name suggests -- points to the number on the scale that coincides with the value configured for that pointer. For example, the gauge I'm adding to this report is for tracking Internet sales. As a result, the pointer will point to the number on the scale that matches the total amount of Internet sales.
To configure the pointer, click on it in the design surface to ensure that it's selected (you might have to click on it twice.) When the pointer, or any component, is selected, a dotted line surrounds it. Figure 8 shows a selected pointer. Notice that, when you select the pointer, the Gauge Data box appears and the pointer's properties are displayed in the Properties pane to the right of the design surface.
When you configure the pointer properties, you must select the field from the dataset that will determine the value of the pointer. In this case, the field is SalesAmount. If you plan to aggregate the values in the source column as is (that is, either add them together or count the rows), you can simply select the field from the drop-down list in the bottom row of the Gauge Data box. Report Builder 3.0 will then automatically create the expression necessary to aggregate the data. For example, if you were to select the SalesAmount field, Report Builder would create the following expression:
You can find this expression under Value in the Properties pane. Because the SalesAmount field contains numeric values, the Sum function is used to add the values together. However, if the field were a nonnumeric value -- such as a string -- Report Builder would instead use the Count function to return the number of rows.
The expression that is generated for SalesAmount will return the total amount of Internet sales, which is millions of dollars. To make these figures more readable, however, it would be simpler to use a base value. For example, it would be easier to show $6.5 million rather than $6,530,343.52. When you work with the aggregated totals in your gauges, you want to be certain that the numbers can be quickly and easily understood.
In the case of our Internet Sales report, the simplest approach is to divide the total amount of sales by 1 million, and then adjust the scale accordingly. So let's start by modifying the Value expression. Because this modification is so simple, you can do it directly in the Properties pane and change the expression to the following:
=Sum(Fields!SalesAmount.Value) / 1000000
Another way to view and modify a pointer's properties, rather than using the Properties pane, is to right-click the pointer and then click Pointer Properties to open the Radial Pointer Properties dialog box, as shown in Figure 9.
Notice that the Value property includes the <<Expr>> value, which is the value used whenever an expression (other than the default) is defined on a property. Since I've already modified the value, the <<Expr>> value shows up here. To view the expression, click the expression button to the right of the text box. This launches the Expression dialog box, which is shown in Figure 10.
Notice that the modified expression is displayed. The Expression dialog box is a useful tool when you need to create more complex expressions. From here, you can view the available fields, datasets, variables, functions, and other language elements. For more information about building expressions, see the Report Builder Help.
The only other pointer property I modified is FillGradiantEndColor, which I set to Gold, rather than the default Orange. You can use either the Radial Pointer Properties dialog box or the Properties pane to set any of the pointer's properties. You can even add another pointer. I recommend that you experiment with the various properties to ensure that you're adding a gauge that is consistent with the look and feel you want your report to have.
Configuring the scale
After you've configured the pointer, you're ready to configure the scale. (Note that you're not obligated to configure each component in the order I've specified here. Configure them in whatever order works best for you.)
The scale represents the standard that is used to compare against the pointer's value. For example, the scale on a gauge runs, by default, from 0 to 100. If there are $20.2 million dollars in Internet sales, the pointer will point to the 20.2 position on the scale.
As it turns out, the Internet sales for each year in the AdventureWorks data warehouse run between about $3.2 million and $9.8 million. So for the purpose of this exercise, let's set the scale at 0 to 10. To set the scale, click the scale to select it and modify the properties in the Properties pane. You can also right-click the scale and click Scale Properties to open the Radial Scale Properties dialog box. Figure 11 shows the properties as they appear in the Properties pane.
Notice that the MaximumValue property is highlighted in the Properties pane. By default, this value is set to 100, but I've changed it to 10 so that the scale's maximum value is 10. You can leave the MinimumValue property at its default value of 0. Notice that the scale in the gauge now has a range of 0 to 10.
If you refer again to the Properties pane in Figure 11, you'll see that the MinorTickMark grouping is expanded so that all the properties in that grouping are displayed. (This grouping corresponds to the Minor Tick Marks page of the Radial Scale Properties dialog box.) The minor ticks on a scale are those marks that appear between the numbers, which are the major tick marks. For example, the scale in the gauge in Figure 11 has major tick marks for the numbers 0 through 10 and minor tick marks in between those numbers.
Like other components in the gauge, Report Builder lets you control how the tick marks appear. In this case, I've set the Interval property for the minor ticks to 0.1 so that there's a minor tick for each tenth of a million dollars in sales.
The MaximumValue and Interval properties are the only properties I modified for the scale. The rest of the properties I left with their default values. You can modify whatever properties you think are necessary in order to provide a consistent look and feel to your reports. You can also add additional scales. Again, I encourage you to experiment with these properties so you understand their use and their potential.
Configuring the ranges
A range marks a specific area on the scale that helps to highlight certain values. For example, on a scale that has an interval of 1 to 10, you might consider adding a range to the 8 through 10 end of the interval to highlight those as target numbers.
Keep in mind, however, that ranges are specifically tied to the scale's interval values. That means if you change your interval, you might impact any ranges you've defined. For instance, when I changed the MaximumValue property of the scale interval to 10, the default range that had been defined on the gauge disappeared. You can handle this situation by configuring the range before you configure the scale, first deleting the range, or not worrying about it—but know that the original range is still defined on the gauge. It just doesn't appear.
For the purposes of this exercise, I left the original range alone and added two new ranges. The first range marks the low-end of the scale, and the second range marks the high-end. To add a range, right-click the gauge and select New Range. When the new range is added to the gauge, you can then configure the range's properties in the Properties pane or in the Radial Scale Range Properties dialog box. Figure 12 shows the new range I added and the properties in the Properties pane.
For the new range, I set the values of the following three properties:
- FillGradientEndColor: Red
- EndValue: 0
- StartValue: 3.3
I chose 3.3 as the start value because I knew what values would be returned by the data warehouse. The amounts I chose for this exercise help me demonstrate certain Report Builder 3.0 features. You, of course, are free to set the range values at whatever works best for you.
After I added the first range, I added a second, as shown in Figure 13.
For the second range, I set the property values as follows:
- FillGradientEndColor: DarkGreen
- EndValue: 10
- StartValue: 6.7
For both ranges, these were the only property values I configured. For all other properties, I stuck with the default values. You should configure the range properties as necessary for your specific reports.
While the gauge is now complete, there is still more you can do to give your reports a better look and feel. In part three we'll take a look at some of these finishing touches.
USING GAUGES WITH REPORT BUILDER 3.0
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.