Advanced Report Design - Professional SQL Server 2005 Reporting Services - page 222

SELECT DimSalesTerritory.SalesTerritoryCountry AS Country
, DimTime.CalendarYear AS Calendar_Year
, DimTime.CalendarQuarter AS Calendar_Quarter
, DimTime.EnglishMonthName AS Month
, DimCustomer.EnglishOccupation AS Occupation
, SUM(FactInternetSales.OrderQuantity) AS Order_Quantity
, AVG(FactInternetSales.SalesAmount) AS Avg_Sales_Amount
, SUM((FactInternetSales.SalesAmount - FactInternetSales.TotalProductCost)
* FactInternetSales.OrderQuantity) AS Gross_Profit_Margin
FROM DimSalesTerritory INNER JOIN FactInternetSales
ON DimSalesTerritory.SalesTerritoryKey =
FactInternetSales.SalesTerritoryKey
INNER JOIN DimTime ON FactInternetSales.ShipDateKey = DimTime.TimeKey
INNER JOIN DimCustomer
ON FactInternetSales.CustomerKey = DimCustomer.CustomerKey
GROUP BY DimSalesTerritory.SalesTerritoryCountry
, DimTime.CalendarYear, DimTime.CalendarQuarter
, DimTime.EnglishMonthName, DimCustomer.EnglishOccupation
, DimTime.MonthNumberOfYear
ORDER BY Country, Calendar_Year, Calendar_Quarter
, DimTime.MonthNumberOfYear, Occupation
For simplicity, I've left the filter parameters out of the query. You'll see references to the data range
parameter to show you what a finished report might look like. This query returns sorted values on three
separate axes: Country, Time (Year, Quarter, and Month), and Occupation. These are used to define row
and column groups. Figure 6-49 shows the basic report in the designer with the available fields in the
Dataset window. When the matrix is placed in the report body, it has three drop zones.
Figure 6-49