Laurence Fishburne once uttered the phrase "No one can be told what the Matrix is – you have to see it for yourself." Fortunately, these types of vague proclamations don't carry over to SQL Server. This three-part series breaks down the benefits of using matrix tables for SSRS reports and explains how to set them up with Microsoft Report Builder 3.0.
When creating a report in Report Builder 3.0, you might want to display summarized information based on grouped data. For example, say you plan to create a report that provides sales totals aggregated along product lines and their individual products, as well as where those products were sold. To support your ability to display summarized data like this, Report Builder 3.0 includes the matrix.
A matrix is a type of table that displays aggregated data summarized by row, column or both -- similar to how a crosstab or pivot table works. The number of unique rows in the column group or row group determines the number of rows and columns returned by the matrix report. A matrix can include multiple row and column groups, and the groups can be arranged hierarchically to provide meaningful categories and subcategories of data.
So let's take a look at how to add a matrix to a Report Builder 3.0 report. In this example, the matrix will retrieve sales data from the AdventureWorks sample data warehouse. Column groups will be based on sales groups and countries. Row groups will be based on the year, quarter, and month when the orders were placed. Aggregated data will be based on the actual sales amounts.
Setting up the data source and dataset
Before you add a matrix to a report, you should set up a data source and dataset. For the matrix being demonstrated in this article, I created a data source that connects to the AdventureWorks2008R2 sample database on a local instance of SQL Server 2008 R2. I named the data source the same as the database.
Note: I installed a local instance of the November 2009 community technical preview (CTP) of SQL Server 2008 R2 on a Windows Server 2008 machine. The installation includes the database engine and SQL Server Reporting Services (SSRS). I also installed the AdventureWorks sample databases provided for the SQL Server 2008 R2 release. The November CTP release of Report Builder 3.0 was installed on the same server.
Next, I created a dataset named InternetSales. The dataset uses the following Transact-SQL statement to retrieve data through the AdventureWorks2008R2 data source:
INNER JOIN DimSalesTerritory t
ON s.SalesTerritoryKey = t.SalesTerritoryKey
INNER JOIN DimDate d
ON s.OrderDateKey = d.DateKey
The statement retrieves sales data based on sales territory, sales group, and the date the order was placed. After you've set up the dataset, you're ready to add a matrix to your report. Note, however, that a matrix is often only one element among other elements in a report, but for this article, I focus only on adding the matrix. To learn about adding other elements to a report or for details about setting up a data source or dataset, see the Report Builder 3.0 Help.
THE MATRIX IN REPORT BUILDER 3.0
ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at http://www.rhsheldon.com.