Adding charts to reports with Report Builder 3.0

Adding charts to reports with Report Builder 3.0

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in January 2010

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.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top