Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

Building a Matrix Report

In the previous lessons you built reports using a table. This type of report is referred to as tabular.

Tabular reports are good for displaying data that is not grouped or that may only have column

groups. What if your reports need to be grouped by columns also? This is where the matrix report

will be used. The matrix report allows row groups and column groups.

The matrix report is created in the same way as the table reports in the previous lessons. You

will drag a matrix tool onto the report body instead of the table. The query will need to have

enough data to allow grouping on columns and rows. If you are pulling sales data you might

want to group the sales by store on the rows and by year on the columns.

Viewing the data in row and column groups like this is very typical for those querying a data

warehouse or a cube. If you use the following query from the AdventureWorks data warehouse

it will give you enough data to group on the columns and rows:

Select e.EmployeeKey

,e.SalesTerritoryKey

,e.FirstName + ‘ ‘

+ e.LastName AS FullName

,d.CalendarYear as Year

,SUM(s.OrderQuantity) AS TotalQuantity

,SUM(s.SalesAmount) AS TotalSales

From DimEmployee e

Join FactResellerSales s on

s.EmployeeKey = e.EmployeeKey

Join DimDate d on

d.DateKey = s.OrderDateKey

Where e.EndDate is null

Group By e.EmployeeKey

,e.FirstName

,e.LastName

,e.SalesTerritoryKey

,d.CalendarYear