Background

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 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? You will need to set the data source of the chart 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 instead of =Sheet1!$B$2

As OfficeWriter inserts new rows data, it will automatically update the ranges to account for the additional rows, so the chart will reference all the imported data.

Set Up

The example in this section uses the AdventureWorks sample database, 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.

This query returns sales data from the AdventureWorks database, broken down by Product Category and will be the data imported into this report.

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

Below is a screenshot of the Excel template with data markers that will be populated with the data from the above query.

After running the report, the imported data looks like this:

Adding a Chart

  1. Go to the Charts section on the Insert tab in the Excel ribbon

  2. From the Pie chart drop-down, select the Exploded 3D pie chart.



  3. Right click on the chart and select Select Data



  4. Under Legend Entries (Series) click Add to add a new series to the chart.



  5. In the Edit Series dialog, set the series values to point to the cell B2, but make sure that the cell is referenced as a range (see image below). Click OK when finished.



  6. Under Horizontal (Category) Axis Labels, click Edit



  7. In the Axis Labels dialog, set the label range to point to the cell A2, but also reference it at as a range. Click OK when finished.



  8. When you are done, your Select Data Source dialog should look like the image below. Click OK when you are done.



  9. (optional) Edit the chart title

You have finished adding a chart to your Excel template for OfficeWriter.

Viewing the Chart

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

The chart should look like this: