In previous examples we created matrix by dragging columns from the "Data" window. You can also use expressions for this purpose. In order to insert an expression into a matrix do the following:
| • | add any element from "Data" window into matrix. It can be any element, for example, a system variable "Date" - we just use it to create a matrix element; |
| • | double click the element and select the needed expression in the expressions editor window. |
If your matrix has expressions instead of data field, you have to check that the matrix "DataSource" property was set up correctly. When working with data columns, this property is filled automatically when you drag a column onto a matrix.
Let us consider an example on how to use expressions. For this, we will use the "Order Details" table as a data source, which contains a list of sold products, grouped by employees. There are several relations in this table, which gives an access to the name of an employee, product name and its category.
Our matrix will show each employee's sales, categorized by products. In order to build the matrix, do the following:
| • | add the "Order Details.Products.Categories.CategoryName" data column to the column header; |
| • | add any item to the row header in order to create a matrix element. Then set the following expression for the header element: |
[Order Details.Orders.Employees.FirstName] + " " + [Order Details.Orders.Employees.LastName]
| • | add any item to data cell in order to create a matrix element. Then set the following expression for the cell: |
[Order Details.UnitPrice] * [Order Details.Quantity] * (decimal)(1 - [Order Details.Discount])
Why did we indicate such a long data column as a name of an employee though we could get a name from the "Employees.FirstName"? We did that because the matrix is connected to "Order Details" data source. Using relations between this data source and other tables, it is easy to refer to its columns (more details about relations can be read in the "Data" chapter). If we refer directly to the "Employees.FirstName" data column, we will get a name of the first employee in a table.
Set up the matrix appearance. After that it will look as the follows:
When we run the report, we will see quite a big matrix which occupies 2 sheets:
