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. |
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 instead of =Sheet1!$B$2
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 |
Publish and View your report and chart. The data from the query will look like this:
The chart should look like this: