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:
You have finished adding a chart to your Excel template for OfficeWriter.
Deploy and View your report and chart. The data from the query will look like this:
The chart should look like this: