A matrix aggregates data (such as `SALES`) along two or more dimensions
(such as `PRODUCT` and `SALESPERSON`) and displays the results in
a grid format.
Matrices are similar to PivotTables in Excel, and crosstabs in Access.

Got a database? Use Airforms database form builder to build data entry forms.

Consider the following datasource. For each transaction that occured at a car dealership, details such as the sales person that sold the car, the product that was sold, and the sale price have been recorded. You want to create a report that easily answers questions such as: What is the total saleprice of all Corollas sold? What is the total saleprice of Camrys sold by Chris?

You can use a table with groupings to display summarized data. The first table shown below has been
grouped by `SALESPERSON` first and then by `PRODUCT`. In this table it is easy to compare
products for a given salesperson. However it is not easy to compare salespersons for a given product. Also
it does not answer questions such as, what is the total sales for all Camrys?

Alternatively, you can group by `PRODUCT` first and then by `SALESPERSON`, as seen
in the second table below. In this
case it is easy to compare salespersons for a given product, but it is not easy to compare products
for a given salesperson. Also, it does not answer questions such as, what is the total sales for
all products sold by Chris?

The matrix show below is more compact than either of the tables above, and yet contains all of the information in the two tables above. It is easy to compare products for a given salesperson. It is also easy to compare salespersons for a given product. Totals are available by product and by salesperson.

Notice the following about the matrix above:

Column labels of the matrix are not known at design time. They are unique values of the

`PRODUCT`field, and are obtained at runtime. Similarly, row labels are unique values of the`SALESPERSON`field.Number of columns is not known at design time. There are as many columns as there are unique values in the

`PRODUCT`field. Similarly there are as many rows as there are unique values in the`SALESPERSON`field.

In order to create a matrix report, your datasource should have at least
two fields that contain repeating values (for example, `PRODUCT` and `SALESPERSON`),
and a third, numerical field (for example, `SALES`) that you want to aggregate
for each combination of the first two fields.

At runtime, unique values from the first two fields are displayed as row
labels and column labels of the matrix. Thus, for example, unique values of
the `PRODUCT` field are displayed as column labels, and unique values of the
`SALESPERSON` field are displayed as row labels.

Then as each record in your datasource is processed, the values of `PRODUCT`
and `SALESPERSON` fields of the record are matched up with the column and row
labels of the matrix, and the value of the `SALES` field is added to whatever
number is in the cell at the intersection of the corresponding column and row.

Adding a matrix to your report is easy once you understand what a matrix is
and how it works. From the Toolbox drag and drop the Matrix icon to your report.
From the Data Sources window, drag and drop the field that will supply the
column labels to the cell marked "Columns". Drag and drop the field that will
supply the row labels to the cell marked "Rows". Drag and drop the field that
should be aggregated to the cell marked "Data". Notice that the field expression
is automatically wrapped in a `Sum()` function when dropped in the "Data" cell.
If you would like to see the average sale price instead of the total sale price you
can change this to the `Avg()` function instead. That's it! You can now run
the report.

To display totals, right-click on the "Rows" cell or the "Columns" cell (or both) and select Subtotal from the context menu.

Notice the little green triangles? They are used for formatting the numerical value of the total as opposed to the "Total" label. Clicking on the triangle makes the Properties panel display the properties of the numerical value.

In our matrix it would be nice to show how many units of each product were sold by each sales person, in addition to the aggregate sales price. Here's how you can do that:

Right-click in the "Data" cell and choose "Add Column" from the context menu.
A new data cell is added to the matrix.
In the new data cell enter an expression for aggregating a field. In our case we
want to show how many units of each product were sold by each sales person, so we
want to display a count of how many rows exist corresponding to a given product
and a given salesman. You can do this by taking advantage of the `CountRows()`
function, so the expression to enter is `=CountRows()`. In the header
corresponding to the new data cell enter the label "Count".

Matrices are not limited to one dimension along columns and one dimension along rows. In the above matrix, assuming the transaction date is available, we may want to group data by month. You can do this as follows:

Right-click on the "Rows" cell and select "Insert Group" from the context menu.
The Grouping and Sorting Properties dialog opens. In the Group on Expression list
enter the following expression: `=MonthName(Fields!Date.Value.Month, true)`
and close the dialog. A new "Rows" cell is inserted. (Tip: You can re-order rows
cells by clicking in the cell and then dragging the gray edge of the textbox.)
Then right-click in the cell and select "Subtotal" from the context menu.

Download an app that contains all the samples on this page.