I recently wrote about one of the latest business intelligence offerings from Microsoft—Report Builder 3.0, a stand-alone report authoring tool that lets you design and customize reports and post them to a reporting server. The article describes how to add gauges to reports in order to provide key performance indicators (KPIs) that enhance their impact.
In this article, I'd like to take a look at another important data visualization tool — charts. Like gauges, charts enhance Report Builder 3.0 reports by providing meaningful data in a visual format that can be quickly understood. Charts can relate far more complex information than gauges, however, and can be used in a variety of ways to ensure that the data you want to communicate is properly understood.
Report Builder 3.0 supports many types of charts, each of which displays ratio, linear, or multi-value data. The following table lists the eight categories of charts that Report Builder 3.0 supports and the types of data they display:
Each chart type supports numerous charts that are similar in nature. For the most part, you can swap charts within types based on your specific needs (this process will become a clearer as we look at more charts). For details about each chart type and the charts they support, see Report Builder 3.0 Help, which is installed when you install Report Builder.
Setting up the data source and dataset
For the examples used in this article, I installed a local instance of the November 2009 CTP release of SQL Server 2008 R2. The installation includes the database engine and SQL Server Reporting Services. I also installed the AdventureWorks sample databases available for the SQL Server 2008 R2 release. In addition to SQL Server, I installed the November CTP release of Report Builder 3.0 on the same server.
After I installed SQL Server and Report Builder, I created a new report and set up a data source that connected to the AdventureWorksDW2008R2 sample database on the local instance of SQL Server 2008 R2. I named the data source AdventureWorksDW2008R2. Next, I set up a dataset called InternetSales. The dataset uses the following query to retrieve data through the AdventureWorksDW2008R2 data source:
c.FirstName + ' ' + c.LastName FullName,
INNER JOIN DimCustomer c
ON s.CustomerKey = c.CustomerKey
INNER JOIN DimProduct p
ON s.ProductKey = p.ProductKey
INNER JOIN DimSalesTerritory t
ON s.SalesTerritoryKey = t.SalesTerritoryKey
INNER JOIN DimDate d
ON s.OrderDateKey = d.DateKey
d.CalendarYear = @OrderYear
Notice that the WHERE clause sets the CalendarYear column to the @OrderYear variable so that uses can specify a year when viewing the report. The variable is automatically added as a parameter to the report. You can view the parameter on the Parameters page of the Dataset Properties dialog box or beneath the Parameters node in the Report Data pane.
The dataset query retrieves more data than we'll use in our chart samples. If you decide to try the examples, however, the extra data will be useful to you if you want work with some of the more advanced features of charts that we won't be covering in this article.
Note: For details about these advanced features, as well as how to set up a data source and dataset, refer to Report Builder 3.0 Help.
After you set up a data source and dataset, you can add charts to your reports. Be aware that a report often contains more features than a single chart, and for this article, I'm only focusing on adding and configuring charts. In this case, the charts will reflect Internet sales as they're stored in the AdventureWorks data warehouse.
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.