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

Adding charts to reports with Report Builder 3.0

Charts are one of the best data visualization tools at your disposal. Learn how the chart functionality in Report Builder 3.0 can improve the effectiveness of SSRS reports.

Part 1 | Part 2 | Part 3 | Part 4 | Part 5

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:

Chart Type Ratio Linear Multi-Value
Column   

X

  
Line

X

     
Shape   

X

  
Bar   

X

  
Area   

X

  
Range   

X

X

Scatter

X

X

  
Polar

X

     

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

What else can you do with Report Builder 3.0?

Check out Robert Sheldon's previous series on adding gauges to SQL Server reports.

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:

SELECT
    c.FirstName + ' ' + c.LastName FullName,
    p.EnglishProductName ProductName,
    t.SalesTerritoryRegion SalesRegion,
    t.SalesTerritoryCountry SalesCountry,
    t.SalesTerritoryGroup SalesGroup,
    d.MonthNumberOfYear MonthNumber,
    d.EnglishMonthName OrderMonth,
    d.CalendarQuarter OrderQuarter,
    d.CalendarYear OrderYear,
    s.SalesAmount
FROM
    FactInternetSales s
    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
WHERE
    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.

Continue to part two


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.

Dig Deeper on Microsoft SQL Server Reporting Services (SSRS)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close