Using Calculated Fields in a Report MatrixLast Updated: 05/03/2018 Introduced in Version: 2.0
Calculated Fields transform the values of other fields to create entirely new values. Report Matrices display a count of how many records match each value reported in a column or calculated column. Each column is referred to as a dimension. Multiple dimensions can be compared along the x and y axes of the report matrix.
To use calculated fields in a report matrix, create the calculated field in the Report Designer, select the Matrix option, and add the calculated column as a dimension to the x or y axis.
The goal of this example is to:
- Create a report with a calculated column.
- Add a matrix to the report that uses the calculated column as a dimension.
- Run the report to view the matrix.
To begin, navigate to a Design Folder. Select the button Create Report.
In the resulting New Report window, give the new report a name and click Ok.
Next, define the data source for the report by selecting the Add Button in the Folder Data Source to the Source list from the Data Sources > Common Data Source category in the Pop Up.
Next, add the Respect Permission Filter to the Filters section by selecting the Add Button in the Filers Field.
Now, select the Add Button in the Data Field field and select the columns Entity Name, Folder Type Name, and Nesting Level.
Expand the Calculated Columns category and drag the TruncateTextInlineField option into the workspace.
In the resulting Add Calculated Column pop-up window, configure the calculated column. This column will display the truncated value of another column. For this example, configure the column to display the first three letters of the Folder Type Name column. Select Folder Type Name in the Field drop down list and type “3” in the Limit field. Click Ok to finish the calculated column.
The new calculated column, truncate_text_inline_field, appears in the workspace.
To add a Matrix option to the menu bar, click on Add in the Views section, then select Matrix.
This will add a Matrix option to the view menu bar. This Matrix view will automatically open.
To build the report matrix, select the Matrix option. Select the Add Dimension link corresponding to the y-axis and select Add Field Dimension.
In the Edit pop-up, select Folder Type Name in the Field drop down list and click Ok.
To add a second dimension, in the Edit pop-up window, select truncate_text_inline_field in the Field drop down list and click Ok.
To add a third dimension, select the Add Dimension link corresponding to the x-axis and select Add Field Dimension.
In the Edit pop-up window, select Nesting Level in the Field drop down list and click Ok.
The final product should look like this. Now, save the report and close the Report Designer.
Back in the portal, select the report and in its Action menu, select Run Report.
The report will appear in a new window. All of the columns, including the calculated column, will be displayed under the Data icon.
Select the Matrix icon to see the report matrix.