Creating Matrix

Use matrix to summarize your report data in the form of cross-section of fields in rows and columns.  For example, ‘Product Type’ and ‘Product’ in columns; ‘Location’ and ‘State’ in rows.  A cross section of ‘Product Type’, ‘Product’ and ‘Location’, ‘State’ will display sales of that product in that location.

Under Data Source tab, select the data source.

To get only matrix on the report, don’t select any display fields on the Fields tab.  If you select display fields, matrix will be placed below the tabular data.

If not already open, click Matrix tab header to open the Matrix tab.

Figure 10: Creating Matrix

To place a field as matrix row, drag it from Available Fields list and drop it in Row Fields (or, select a field and click  button).  To place a field as matrix column, drag it from Available Fields list and drop it in Column Fields (or, select a field and click  button).

To place a field on summary (intersection of row and column), drag a field from Available Fields list and drop it in Summary Fields (or click   button).

These fields will be calculated for summary/totals.  Functions listed in Function dropdown box will depend on data type of the Summary Field.

You can drop multiple fields in Row Fields, Column Fields and Summary Fields box. Fields appear higher in sequence in Row Fields and Column Fields will appear on outer side of matrix.   Summary Fields are placed left to right on the matrix. To move a field up or down, click  or  button.

Show When opens up filtering criteria to apply on column and row fields. The column/row that meets the condition shows up on the matrix when you run the report.

You can highlight a Cell, Cell Family, or an Entire Row or Column of a matrix based on a condition. The matrix highlights the summary field using the specified Style.

In case of a hyperlinked field (specified at the query object level), you can drill down to open another report or URL on clicking the value of field on matrix.

You can specify Sort Order to sort column or row fields in ascending/descending order on the ad hoc report.

Grouping values of Numeric fields

You can specify an integer value to group numeric fields.  For example, to have groups of 0-9, 10-19 …  specify 10 in Group By box of respective row in Row Fields or Column Fields box.

Grouping values of Date type fields

You can group a date by Minute, Hour, Day, Week (Sunday to Saturday), Month, Quarter (Jan-Mar, Apr – Jun, Jul – Sep, Oct – Dec), Year.  Select an option from Group By box of respective row in Row Fields or Column Fields box.

Clicking Clear Matrix would clear the specified matrix properties to start all over again.

Designers/Users can link Matrix and Chart so that any changes made in one component gets reflected automatically in the other. Linking can be done in both ways- Matrix to Chart and vice versa.

Figure 11: Link Matrix and Chart

Custom aggregation (formula based) in matrix totals

With previous versions of Intellicus, the summary function applied on the summary fields used in the matrix control of ad hoc reports was purely mathematical. The group level aggregation of the summary field for the respective summary function depends on the values of the same field.
Intellicus provides a mechanism for selecting a custom/user defined summary function for fields whose value (defined by an expression) is dependent on the values of other summary fields used.
In the below example, the value of ‘Sales Percentage’ field is calculated using a user-defined function as: (SUM(Sales Achieved) / SUM(Target Sales) * 100)

Figure 12: User Defined Formula

The field for which you are defining the formula is seen prefixed with fx symbol. You can see the ex symbol after formula is applied to the field.

The custom aggregated value of ‘Sales Percentage’ in the report is calculated based on the mathematical expression specified. This can also be seen in exported/ published outputs.

Figure 13: Custom aggregated value in report

Note: Matrix containing user defined functions can not be linked to a chart.

Also, report level filtering can not be applied on matrix with user defined functions.

Hyperlink

Intellicus allows you to create row and column values as hyperlinks. In previous versions, hyperlink functionality used to work with fields under summary. If the hyperlinked fields point to a parameterized report, the hyperlinks will open report/ content specific to the hyperlinked field. This enhances the user experience to quickly analyse relevant data and bring out timely and meaningful insights.