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.
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
- Go to the Charts section on the Insert tab in the Excel ribbon
- From the Pie chart drop-down, select the Exploded 3D pie chart.
- Right click on the chart and select Select Data
- Under Legend Entries (Series) click Add to add a new series to the chart.
- 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.
- Under Horizontal (Category) Axis Labels, click Edit
- 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.
- When you are done, your Select Data Source dialog should look like the image below. Click OK when you are done.
- (optional) Edit the chart title
You have finished adding a chart to your Excel template for OfficeWriter.
Viewing the Chart
The chart should look like this: