Practical Application of Chart and Matrix

Let's apply the knowledge from this chapter to create a report.

Create a report that displays sales by country and by year, including graphical presentation of sales data using the following steps.

1. Add a new report with data set, based on the following query:

    SELECT SUM(SOH.TotalDue) AS Sales, DATENAME(yyyy, SOH.OrderDate) AS Year, 'Q' + DATENAME(qq, SOH.OrderDate) AS Quarter, Person.CountryRegion.Name AS CountryName FROM Sales.SalesOrderHeader AS SOH INNER JOIN Sales.SalesTerritory AS ST ON SOH.TerritoryID = ST.TerritoryID INNER JOIN Person.CountryRegion ON ST.CountryRegionCode = Person.CountryRegion.CountryRegionCode GROUP BY ST.Name, DATENAME(yyyy, SOH.OrderDate), 'Q' + DATENAME(qq, SOH.OrderDate), Person.CountryRegion.Name ORDER BY ST.Name, DATENAME(yyyy, SOH.OrderDate), 'Q' + DATENAME(qq, SOH.OrderDate) 

2. Include the AdventureWorks logo and title of the report (Sales By Country).

3. Drag and drop a Chart item onto a report. Note the drop areas: Drop Data Fields Here, Drop Category Fields Here, and Drop Series Fields Here. Drag and drop the Sales field on the data area, CountryName on the category area, and Year on the series area.

4. Drag and drop a Matrix item on the report. Drag and drop the Sales field on the data area of the matrix, CountryName on the columns area, and Quarter and Year field on the row area.

Figure 11.11 provides resulting design and runtime views of the report. Final report can be found in the samples for this book at CodeSamples\part2.relationaldbsamplereports\part2_simple_samples\ Matrix and Chart -- SalesByCountry.rdl .

Figure 11.11. Chart and matrix items at work.