How to Create Excel Reports > Working with Charts

Working with Charts

Excel charts can play an important part in data presentation. The ability to visually present data is one of Excel's strong points. This section shows you how to include Excel charts in the reports you create with OfficeWriter Designer. Each time you execute your report, the chart will be populated with the most recent data.

The example in this section uses the AdventureWorks sample database for SQL Server 2005, an optional component in the Reporting Services installation. This section assumes you have knowledge of creating reports with OfficeWriter Designer. If you are not familiar with creating reports with OfficeWriter, please refer to the Quick Start.

Creating a Chart

The source data for an Excel chart is a range of cell values. But, OfficeWriter Designer places data markers in your worksheet which are later populated with a set of values from a database. How do you specify that the database values should be used as the source data for a chart series? In Excel's Chart Wizard, set the source data to be the cell that contains the data marker, but specify the cell as a range, not as an individual cell address. For example, use =Sheet1!$B$2:$B$2 insead of =Sheet1!$B$2

1. Open Microsoft Excel, click Add Query on the OfficeWriter toolbar, and create the following query.
   
   

   SELECT ProductCategory.ProductCategoryID,
      Max(ProductCategory.Name) AS 'Category',
      Sum(SalesOrderDetail.LineTotal) AS 'Total'
   FROM Production.Product Product,
      Production.ProductCategory ProductCategory,
      Production.ProductSubCategory ProductSubCategory,
      Sales.SalesOrderDetail SalesOrderDetail
   WHERE Product.ProductID = SalesOrderDetail.ProductID
      AND ProductSubCategory.ProductSubCategoryID = Product.ProductSubCategoryID   
      AND ProductSubCategory.ProductCategoryID = ProductCategory.ProductCategoryID
   GROUP BY ProductCategory.ProductCategoryID
   ORDER BY Category

   This query returns the sales from the AdventureWorks database broken down by Product Category.
   
   
2. Using OfficeWriter's Insert Field button, insert the fields in the worksheet, as shown:
   
   
   
   
3. From the Excel's Insert menu, select Chart. The Chart Wizard will open.
   
   
4. Select the chart type Exploded pie with a 3-D visual effect.
   
   
   
   
5. Click Next.
   
   
6. Select the Series tab.
   
   
   
   
7. Click Add add a series.
   
   
8. In the Values text box, enter the address of the cell containing the query's Total field, but enter it as a range, not as an individual cell address:
   
   
   =Sheet1!$B$2:$B$2
   
   
   
   
   OfficeWriter will take this range and populate it with the query's data. If you click the icon to the right of the Values box and select the cell with your mouse, your cell will be selected, but not as a range.
   
   
   
   Add :$B$2 to make sure the cell is selected as a range.
   
   
   
   
9. Do the same for the cell containing the query's Category field.
   
   
   
   
10. Click Next.
    
    
11. In the Chart Title textbox, enter Sales by Product Category, and click Next.
    
    
    
    
12. In the Chart Location dialog, select either As new sheet or As object in, and click Finish.

Viewing the Chart

Publish and View your report and chart. The data from the query will look like this:

The chart should look like this:

Copyright 2005 © SoftArtisans, Inc. All Rights Reserved.