Adding Matrix Data Regions (Visual Studio Report Designer)

Article
07/13/2009

A matrix is a data-bound report item in which data is arranged into columns and rows that intersect at specific data points. Matrices provide functionality similar to crosstabs and pivot tables. Unlike a table, which has a static set of columns, matrix columns can be dynamic. You can define matrices that contain static and dynamic rows and columns.

Note

If you export a matrix report to Excel, all rows and columns are visible in the worksheet, regardless of how you set visibility properties.

Creating a Matrix Data Region

When you first create a matrix, 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.

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.

How to Add, Move, and Delete a Matrix

To add a matrix

Open the client report definition (.rdlc) file in graphical design mode.
In the Toolbox, click Matrix.
Click the design surface of the report.

After the matrix is on the report, you can move it by pointing to the shaded border of the matrix and dragging it to a new location (click anywhere on an empty area on the matrix to cause the shaded border to appear). To delete a matrix, right-click any empty space within it and press DELETE.

How to Bind 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 bind data to a matrix, drag a field from the Data Sources window onto a matrix cell. If you drag the field into a cell in a matrix column that does not have a header (for example, the Details row), the name of the field automatically appears in the column header.

Note

If the Data Sources window does not appear, in the Data menu, click Show Data Sources.

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.

Dynamic Columns and Rows (Groups)

You can add additional dynamic columns and rows to the default matrix 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.

To add a dynamic column or row to a matrix

Drag the field from the Data Sources window onto a column or row header in the matrix.
Repeat step 1 to add multiple dynamic columns or rows to a matrix.

The column or row splits to create another dynamic column or row. The position of the new column or row depends on which side the field was dropped. For a column, a bar appears above or below the existing cell; for a row, a bar appears to the left or right of the existing cell.

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.

To add a static column or row to a matrix

Right-click the data, or detail, cell of the matrix, and then click Add Column or Add Row. Alternatively, drag a field from the Data Sources window onto a populated data cell.
Repeat step 1 to add multiple static columns or rows to a matrix.

The cell splits to create another static column or row. If you add a column or row by dragging a field, a dark line is displayed on one side of the cell, indicating where the field will be positioned when it is dropped onto the matrix.

Note

When a matrix contains a single static column or row, the cell has no column or row header. When multiple static columns or rows are added to a matrix, a static header is created for each static column or row.

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. The ReportViewer control will calculate the subtotal for the group.

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.