Adding gauges to reports with Report Builder 3.0 for SQL Server

Report Builder 3.0 comes with several new data visualization features, including gauges for creating more effective reports with SQL Server Reporting Services.

Part 1 | Part 2 | Part 3

Report Builder 3.0 is a stand-alone report authoring tool that lets you design and customize reports and post them to a reporting server. Although the tool is part of the SQL Server 2008 R2 suite of tools, you can download Report Builder separately and point it to the SQL Server Reporting Services (SSRS) report server, where the report is rendered just like any other SSRS report.

Report Builder 3.0 includes a number of new data visualization features that let you produce reports that go far beyond standard tables and charts. One of these features is the gauge, which displays key performance indicators (KPIs) in a radial or linear format.

In this article, I will demonstrate how to add a gauge to a report and configure the gauge's properties in order to display a KPI that adds meaningful information.

Setting up the report

To demonstrate how to add a gauge to a Report Builder report, I set up a local instance of the November 2009 community technical preview (CTP) of SQL Server 2008 R2. The installation included the database engine and Reporting Services. I also installed the November CTP release of Report Builder 3.0 on the same server. Next, I configured Report Builder's options to so that it pointed at the SSRS reporting server, as shown in Figure 1.

Figure 1 (click to enlarge)

Note that, once Report Builder is pointing to a report server, you can start building your reports. Any reports you build (and save) are automatically posted on the report server.

After I set up SQL Server and Report Builder, I installed the AdventureWorks sample databases available for the R2 release of SQL Server (you can download the database installation file from CodePlex). Next, I configured a data source in Report Builder that points to the AdventureWorksDW2008R2 database. Figure 2 shows the properties for the AdventureWorksDW2008R2 data source.

Figure 2 (click to enlarge)

Once the data source was configured, I set up the InternetSales dataset, which retrieves the amount of each Internet sale. Figure 3 shows the Dataset Properties dialog box for the InternetSales dataset, which includes the Transact-SQL statement used to retrieve the data from the data source.

Figure 3 (click to enlarge)

Notice that the SELECT statement shown in Figure 3 includes a WHERE clause that sets the CalendarYear column to the @CalendarYear variable. Because this clause is included, users will be able to specify a year when viewing the report. The variable is added automatically to the Parameters page of the Dataset Properties dialog box, as shown in Figure 4.

Figure 4 (click to enlarge)

Note that the parameter value might be blank when you first view the Parameters page, but if you close the dialog box and then reopen it, the value will be inserted.

Once the data source and dataset are set up, we are ready to add a gauge to a report. Note, that a report would normally contain many more features. For the purposes of this article, however, I will add only the gauge. The gauge will provide a KPI that reflects total Internet sales as they're stored in the AdventureWorks data warehouse.

Note: For details about how to configure the Report Builder options and how to set up a data source and dataset, see Report Builder 3.0 Help, which ships with Report Builder.

Adding the gauge to the report

To add a gauge to a report, select the Insert ribbon from the menu bar, and then double-click the Gauge icon. The Select Gauge Type dialog box appears, as shown in Figure 5.

Figure 5 (click to enlarge)

The Select Gauge Type dialog box is where you select the type of gauge you want to insert into your report. Report Builder 3.0 supports two types of gauges: radial and linear. The difference between the two types basically has to do with the shapes, as you can see by the gauge thumbnails within the dialog box. To add a gauge to your report, simply double-click one of the thumbnails. The gauge is then added to the report's design surface, where you can resize and reposition it as necessary.

For this exercise, I chose the first gauge in the Select Gauge Type dialog box. After I added the gauge to the design surface, I resized and repositioned it, as shown in Figure 6.

Figure 6 (click to enlarge)

After you add the gauge to your report, you can configure its properties. The properties are broken into categories that coincide to the components that make up the gauge. The three components that you'll most likely want to configure are the pointer, scale and range, which are shown in Figure 7.

Figure 7

In part two, we'll take a closer look at each of these components so you can better understand how they work and how to configure their properties.



 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 http://www.rhsheldon.com.

Dig Deeper on Data Visualization Techniques for SQL Server