Problem solve Get help with specific problems with your technologies, process and projects.

Enter the matrix with Report Builder 3.0

Want to know what the matrix is? With SQL Server, just like the movie, it's best to see it for yourself.

Part 1 | Part 2 | Part 3

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.

What else can you do with Report Builder 3.0?

Check out Robert Sheldon's three previous series on adding chartsgauges and maps to SSRS reports.

 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:

SELECT
  t.SalesTerritoryCountry SalesCountry,
  t.SalesTerritoryGroup SalesGroup,
  d.MonthNumberOfYear MonthNumber,
  d.EnglishMonthName OrderMonth,
  d.CalendarQuarter OrderQuarter,
  d.CalendarYear OrderYear,
  s.SalesAmount
FROM
  FactInternetSales s
  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.

Continue to part two

 


THE MATRIX IN REPORT BUILDER 3.0


 Part 1: Enter the matrix
 Part 2: Creating matrix reports
 Part 3: Matrix configuration techniques

 

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.

This was last published in March 2010

Dig Deeper on Microsoft SQL Server Reporting Services (SSRS)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close