Working with Matrix Data Regions
In a matrix data region, data is arranged into columns and rows. Matrices provide functionality similar to crosstabs and pivot tables. Unlike a table, which has a static set of columns, matrix columns can be dynamic. Using Report Designer, you can define matrices that contain static and dynamic rows and columns.
You can add a matrix to a new or existing report. You can create a matrix report using the Report Wizard. For more information, see Creating a Report Using Report Wizard.
Creating a Matrix Data Region
When you first create a matrix in Report Designer, the matrix displays four cells. The upper-left cell is the corner cell. You can use the corner cell to display a label for the matrix, or you can leave it empty. The upper-right cell is a column header, which can contain a field or expression by which to group the data. The lower-left cell is a row header, which also can contain a field or expression by which to group the data. The lower-right cell contains an aggregate expression for the detail data.
.gif "Basic Matrix data region")
When the report runs, dynamic column headers expand right (or left, if the Direction property of the matrix is set to RTL) for as many columns as there are groups. Dynamic rows expand down the page. The data that appears in the detail cells are aggregates based on the intersections of columns and rows.
To view instructions about working with matrices, see How to: Add, Move, or Delete a Matrix (Report Designer).
Adding Data to a Matrix
After you add a matrix, you can add fields to the matrix. Each cell in the matrix contains a text box by default. You can type any expression into any cell, or you can change the item within the cell to another item (for example, change a text box in a cell to an image).
To view instructions about adding data to a report item, see:
- How to: Add, Edit, or Delete a Field in the Datasets Window (Report Designer)
- How to: Add an Expression (Report Designer)
- How to: Change an Item Within a Cell (Report Designer)
Each matrix on a report is associated with a dataset. If the report contains a single dataset, the matrix is automatically associated with that dataset when you place it on the report. If the report contains multiple datasets, you must associate the matrix with the correct dataset. To view instructions about changing the dataset for a matrix, see How to: Associate a Data Region with a Dataset (Report Designer).
Dynamic Columns and Rows (Groups)
You can add additional dynamic columns and rows to the default matrix. Dynamic columns and rows are used to group data by field. When you add a new dynamic column by creating a new column or row header, the new header is nested within the original header. When the report runs, the new header is repeated within the original header. For example, a nested dynamic column or row can have a header containing a field for region, and within that header, another header that contains a field for city.
You can add dynamic columns and rows by dragging fields from the fields list onto the matrix. When you drag a field onto a matrix that has an existing column or row header, you can choose to place the field on the inside or outside of the header. Report Designer displays a bar on the top or bottom of an existing column header, or to the left or right of an existing row header, depending on where you drag the field. For example, to create a new dynamic column that contains an existing dynamic column, you would drag the field to the existing column header, position it so that a bar is displayed on the top border of the header cell, and then drop the field.
For more information, see How to: Add a Dynamic Column or Row to a Matrix (Report Designer).
Static Columns and Rows
You can also add static rows and columns to display additional detail data. When you add a static column or row, Report Designer divides the header in two, but instead of arranging the headers so that one header resides within the other, each detail cell is displayed side-by-side with headers that contain a static label. For example, a static column or row can be a detail cell with a field for projected revenue, next to another detail cell with a field for actual revenue.
For more information, see How to: Add a Static Column or Row to a Matrix (Report Designer).
Sorting
You can sort data within a matrix by any expression. For more information, see How to: Sort Data in a Matrix (Report Designer).
Adding Subtotals
To add a subtotal to a matrix, add a subtotal to an individual group within the matrix. Groups do not have subtotals by default. To add a subtotal to a group, right-click the group column or row header and then click Subtotal. This will open a new header for the subtotal. Reporting Services will calculate the subtotal based on the aggregate in the data cell for the group. For information about aggregate functions, see Using Report Functions in Expressions (Reporting Services).
Displaying Data on Either Side of Row Headers
You are not limited to displaying row headers on the side of the matrix. You can move the row headers between columns, so that columns of data appear before the row headers. To do this, modify the GroupsBeforeRowHeaders property for the matrix. You can access this property through the Properties window or the General tab of the Matrix Properties dialog box. The value for this property is an integer; for example, a value of 2 will display two groups of matrix data before displaying the column containing the row headers.