Table of row/column filter properties
Introduction¶
The properties table allows the user to manage the filter properties to customise the matrix. When several filters are selected, only the shared properties are shown and the values set on them will be applied to all the selected filters.
Reference FX rates
This property manages the FX rate when a different FX rate from the one specified in the table is needed, or if it must be read from a different scenario or period from those of the data to be converted.
The possible values are provided below:
| Element | Description |
|---|---|
| average | Average FX rate of the period in which it is calculated. These are the FX rates generally used for P&L accounts. |
| period average | Applies a different FX rate for every period into which the year is divided. Allows the user to perform more accurate conversions than the average FX rate. |
| final | Period-end FX rates. These are used for the balance sheet accounts, with the exception of the equity accounts, which are typically converted at the historical FX rate. |
| based on account | FX rate used for conversions and defined in the accounts table |
FX rate
Currency dimension properties.
| Element | Description |
|---|---|
| For matrices on the financial layer | |
| From Configuration | Value, displayed in brackets next to the active property, previously configured in Default FX rate for the new filters on the currency in the Forms section of CCH Tagetik’s general settings. See Currencies: basic concepts. |
| None | The Currency dimension is used as a dimension filter: only records whose currency is the selected currency will be read by the database. |
| Final FX rate | The records read by the database are converted into the selected currency using the final FX rate, as defined in the FX rate table. |
| Average FX rate | The records read by the database are converted into the selected currency using the average FX rate, as defined in the FX rate table. |
| Period average FX rate | The records read by the database are converted into the selected currency using the period average FX rate, as defined in the FX rate table. The FX rate property of the Currency dimension, as well as the equivalent property in Reference FX rates, does not support the Period average FX rate option if the form uses the standard reporting engine. Where this configuration is used, at the run form step, this option is automatically set to Based on account. A warning is sent and the user must correct the form configuration. |
| Based on account | In the accounts table, it is possible to define the FX rate to be used in the various data processing to covert the relevant amounts (Conversion property, with the following possible values: None, Final, Average or Period average). With this setting, the form converts records read from the database into the selected currency, using the relative FX rate, as defined in the table, for each of them. |
| For matrices on the analytics layer | |
| None | The Currency dimension is used as a dimension filter: only records whose currency is the selected currency will be read by the database. |
| Based on the measure | The option to be applied is read from the numerical measure configuration in which it is possible to define the desired FX rate for that measure from the web interface. (See Dataset fields). |
| Based on account | With this setting, the form converts records read from the database into the selected currency, using the relative FX rate, as defined in the table, for each of them. If the account dimension is not present in the dataset, the Based on the measure FX rate is applied. |
Particular features of conversion based on account¶
Conversion based on account is only applicable when the dataset has the account dimension. If the “based on account” conversion type is specified and the matrix does not have the account dimension, the form is not run. If the account has a conversion type that is not supported by the analytical matrices, i.e. it is not the average FX rate, period average FX rate or final FX rate, the Based on the measure FX rate type is applied.
Forms in which global filters (form filters or sheet filters) on the currency are used will have the following behaviours:
| When... | Then... |
|---|---|
| the dataset does not contain the account dimension | if the matrix has no overrides on the currency dimension, the system considers the currency indicated in the global filter: - if the conversion type is final, average, or period average, the system applies the Based on the measure conversion type. - if the conversion type is None, then no filter is applied and only the values in the selected currency are read. |
| of the matrix specifies an override on the Currency dimension, then the global filters are not applied. | |
| the dataset contains the Account dimension | the Based on Account conversion type is applied, regardless of what is specified at the matrix level, save for matrix overrides. |
Periodic value calculation
Period length dimension properties.
| Element | Description |
|---|---|
| Automatic | The properties’ behaviour varies based on the engine used for reporting. Default value. See Types of reporting engine. Note: option used when the Period length is not specified in any of the form’s filters. |
| Aggregation of periodic values | Calculates the cumulated data as the sum of periodic figures. For example, the quarterly figure for the second quarter is calculated as the sum of the monthly figures read from periods 4, 5 and 6. |
| Net Year-to-date values | Calculates the cumulated data as the net year-to-date values. For example, the quarterly figure for the second quarter is calculated as the difference between the year to date figure for period 6 and the year to date figure for period 3. |
Sign change
This account dimension property allows you to change the sign with which the account is displayed. When active, this inverts the sign of the data.
Note: the elements for which the sign change is active are shown in red in the Matrix Wizard.
Line by Line matrices do not support the native sign change relative to the account dimension unless additional filters on the account dimension are present (Form/Sheet/Matrix filters).
For multidimensional AIH matrices related to an AW dataset configured with data refresh mode set to Refresh existing record, the sign change is not present for text-based measures.
Complementary
This property allows you to exclude a specific filter or node from a node of filters in a hierarchy. Thus, the system displays the entire hierarchy of the filter’s parent node or excluded node, except for the specific filter or node. This type of filter is recognisable from the
Note: the additional filter works on the hierarchies but not groupings.
Data Validation
This property allows the user to associated a validation query with the selected filter to limit their choice to a restricted set of elements.
Size
This property is not editable and indicates the dimension to which the selected element belongs.
Advanced selection
| Element | Description |
|---|---|
| Allow new rows/columns wizard in data entry | Only for transactional forms with Pruning set to To zero. When active, in data entry it allows you to view and edit rows/columns that are open but not shown because they are empty. Note: the effects of the changes are shown in the form automatically (e.g. changes on amounts). |
| Additional rows to open | With the Counter dimension, it is possible to set the number of additional rows to be opened in transactional forms. Set this value in the Additional rows to open field in the Advanced selection attribute included in the counter properties in the matrix. (See Details of the Counter element). Can only be set if Allow new rows/columns wizard in data entry is set to "false". |
| Detail subtotal | Displays the subtotal for every dimension nested in the filter node. In the presence of three or more nested dimensions, activate the option to prevent potential style application errors. |
| Generate subtotals up to | Allows the user to define the starting node for calculating subtotals. - Root: generates subtotals based on the hierarchy root (default value). - Starting node: generates subtotals based on the selected intermediate node. Note: this has no effect if the elements returned as the result of the Advanced Selection script do not belong to the starting node’s sub-tree. See Details of advanced selection scripts for calculating the default value. |
| Restrictions | The system allows you to insert in the table restrictions on dimensions of the row/column, primarily between entity/custom dimensions, in order to allow, in data entry, data to be read/written only on the relative custom dimensions specified in the table, to improve performance. This option allows you to override these restrictions on dynamic elements, in order to see the data without the restrictions set in the table. Hierarchy Node Intersection: by default, if a single dimension of the matrix has several hierarchies, the system presents the intersection of the two hierarchies, i.e. the data related to elements belonging to both hierarchies. By activating this option, the system shows all combinations of elements of both hierarchies. See Entity elements. |
| Pruning | Hides empty rows/columns. - Not valued: hides from the table rows/columns which are found to have no data in the database during the run form phase. The value zero is considered as data. - To zero: hides from the table rows/columns which are found to have no data in the database during the run form phase or have the value 0 entered. - None: shows all rows/columns. |
| Ranking | Defines the criteria for sorting data within the table. - No rule: sorts the row data based on the values of a reference column. - Top elements: brings the elements with the highest values to the top. - Bottom elements brings the elements with the lowest values to the top. - Pareto analysis 80/20: selects the minimum set of rows/columns with the highest value in the reference column and which make up a defined % of the column/rows total. By default, the threshold is 80%, but it can be changed. - Only elements that are not zero: removes from the display rows/columns with no value entered or with the value 0 in the reference column/row. - Reference column/row: selects the matrix column/row (depending on where the ranking property is being set) on which basis the data is to be sorted. - Number of elements to display: sets the maximum number of rows to display. - Accept more elements than requested: if there are several rows with the same value, all rows will be displayed. If disabled, the system chooses the rows at random, so that it displays the best rows while respecting the limit indicated in Number of elements to display. - Sorting rule: defines the row sorting criteria (increasing, decreasing, none). |
| Script | Allows the user to set a query on CCH Tagetik’s application database in the cell using a TGKML syntax, see Advanced selection script. |
| Subtotals | Only with dynamic filters. When the form is run, at every hierarchy level interruption, it displays the subtotal calculated on the lowest level elements. The subtotal therefore takes the name of the parent node of the lowest level elements on which the subtotal is calculated (for example: Europe is the name, and therefore the parent node, of the subtotal calculated on the lowest level elements Italy, France, Spain etc.). For more details on the general settings for subtotals, see Matrix options tab. |
| Selection type | Define which hierarchy levels to display. - None - Insert all children: only displays the child nodes of the selected filter. - Insert lowest level elements: displays all the lowest level elements present below the selected node, to the last level. - Insert Parent: displays the parent node of the selected filter. - Advanced selection: activates the Script property. Allows the user to insert dynamic elements that cannot be inserted via standard selection. - Insert analytical FST : (with Account type elements inserted via items of an FST) allows the user to define an analytical FST in which both the FST items and the related accounts are displayed. For more details, see Financial statement templates. - Insert synthetic FST: (with Account type elements inserted via items of an FST) allows the user to define a synthetic FST in which only the FST items are displayed. For more details, see Financial statement templates. - Control group: (with Account type elements entered via control groups) allows the user to define the accounts with reference to a group. |
Dictionary
| Element | Description |
|---|---|
| Allows the user to select an existing dictionary item. See Dictionary Items window. |
Editability
| Element | Description |
|---|---|
| Editable | Makes the filter editable. |
| Not editable | Makes the filter non-editable. |
| Protected | Makes the filter editable using the formula, the result of which is not directly editable by is saved in the database when the form is saved. |
| Unspecified | Default value. |
Transactional form editor
Only for transactional forms with "Use Transactional form editor active.
| Element | Description |
|---|---|
| Tab | Defines the editor tab in which to view the selected column filter. - Excluded from the editor: does not show the filter in any tab - |
| Column | Defines the column of the tab on which the column filter is shown (2 = second column of the tab). |
| Row | Defines the row of the tab on which the column filter is shown (2 = second row of the tab). |
| Filled columns | Defines the number of columns occupied by the filter. |
| Label space | Defines the dimension in percentage terms with which to show the column label in the editor. |
Validation
Only for data entry forms. Allows you to set checks on the correctness of the data entered. See Validations.
Conditional editability expression
Only on editable parameters or parameters with protected editability. Allows you to subject the editability of the current filter to conditions and logical expressions in relation to other filters. See Validations.
Dividing factor
Account dimension properties. Not supported for matrices designed on the Analytical Workspace.
| Element | Description |
|---|---|
| Day | Calculates the average of the amounts based on the number of days that have passed from the start of the year to the specific month, e.g. 180 days from January to June. |
| Period | Calculates the average of the amounts based on the number of days in the specific month, e.g. 30 days for the month of June. |
Tagetik style
Only available if a Tagetik style is defined for the matrix.
| Element | Description |
|---|---|
| Opens the style selection window for the matrix elements. See Tagetik styles . |
Formula
| Element | Description |
|---|---|
| Opens a window to write the calculation formula. | |
| Prevailing | Makes the formula the prevailing formula in case of intersection with others. |
| Calculation logic type | - Client: the formula refers to dimensional elements contained in the matrix and is solved in the local client. - Server: the formula refers to elements of the dimensions present in other matrices or other elements of the form, the formula is solved on the server and the client receives the resulting value. |
Hyperlink
| Element | Description |
|---|---|
| Action list | Opens the Action List Wizard window to define the actions to be run when a hyperlink is selected. See Action List Wizard window. |
| Prevailing | Makes the link defined for the row/column prevail over any other hyperlinks on intersection rows/columns on the same matrix which, at runtime, could create more than one value on the same cell. |
| Values or Headers | Allows you to specify the part of the matrix on which the hyperlink can be inserted. - Values and headers: the link is available both on headers and on row/column values. - Values: the link is only available on row/column values. - Headers: the link is only available on row/column headers. |
Multi-selection
Property only available from the web interface on row/column filters with counter dimension in transactional matrices of the Analytical Workspaces.
When active, this shows tick boxes for selecting multiple rows/columns when the form is run.
Hide
| Element | Description |
|---|---|
| Do not hide in the presence of editable cells | Shows the hidden row/column in the presence of editable cells. |
| Hide row or column | |
| Always | - Synthetically hides the row/column. In contrast to Excel's native functionality, this option also remains anchored to the element when the form is dynamic and the position can change. |
| Not defined | Hides the row/column without data. |
| To zero | Hides the row/column the amounts of which are zero. |
| chained to | Hides the row/column when this is subject to the display of another row/column that can in turn be hidden. |
| Ignore the cells that contain formulas | Only with Undefined/To zero. In the presence of cells containing formulas, the value of the cells resulting from the formula is not considered when the system assesses of whether or not to hide the row/column. |
| Ignore the matrix headers | Only with Undefined/To zero. Does not consider the content of the values in the matrix header areas in the decision of whether or not to hide the row/column. Enabled by default. |
| Do not consider cells that contain text | Only with Undefined/To zero. In the presence of cells containing non-numeric values (amount type, description, etc.), these are not considered when the system assesses whether or not to hide the row/column. Note: when this option is disabled, the cells containing non-numeric values are considered non-empty cells with values other than 0. |
| Also evaluate other matrices on the same row/column | Only with Undefined/To zero. In the presence of several matrices containing cells on the same row/column, this only hides the row/column if all the matrices allow it to be hidden. The outcome of this check is therefore applied to all matrices. Cells outside of the matrix are ignored. |
| Reference row | Only with Chained to. Allows you to select the row/column to which to subordinate the current row/column. |
Segment
Properties of the entity and accounts dimension
Allows you to view intercompany data related to the entities of an entity node called a segment. This property’s default value is defined in advanced form settings and used on all the tab filters set on entity dimensions, custom dimension 2 or the counterparty entity. This value can be overridden when designing the matrix.
| Element | Description |
|---|---|
| from configuration | Reads and applies the values indicated in the application’s general settings to the Default section of the Form tab. SeeSettings window. |
| none | Does not apply any filters to journals |
| Parent segment | The value shown includes all consolidation journals (IC eliminations, financial investment eliminations, other corrections) between legal entities belonging to the same "parent" node. |
| Segment | The value shown includes all consolidation journals (IC eliminations, financial investment eliminations, other corrections) between legal entities belonging to the same node. |
| Intersegment | Identifies the values relative to all journals referring to legal entities belonging to different segments. This function is activated ONLY if the selected node has at least TWO levels below it. Data can be read according to this method both when creating "Tab filters" and when selecting "Prompt Filter", respecting all the restrictions on the depth of the levels for the choice of "Intersegment". |
| Parent intersegment | Identifies the values relative to all journals referring to legal entities belonging to different segments and, starting from a level below, takes all the journals that start from the node on which the function is activated. |
Data type
| Element | Description |
|---|---|
| Date | Applies the date format to the row/column. |
| Number | Applies the number format to the row/column. |
| Body | Applies the text format to the row/column. |
| Default | Applies the data type defined in the cell properties. |
Prevailing data type
When active, this makes the data type set on the cell predominant over the data type defined for the entire row/column.
Navigation
Only for parametric filters on the scenario dimension. See Navigating scenario parameters.
| Element | Description |
|---|---|
| Previous | Selects the scenario indicated in the scenarios list as the previous scenario to the selected scenario. See Navigating scenario parameters. |
| Next | Selects the scenario indicated in the scenarios list as the next scenario following the selected scenario. See Navigating scenario parameters. |
| Reference | Selects the scenario indicated in the scenarios list as the selected scenario’s reference scenario. See Navigating scenario parameters. |
Period Difference
Only for parametric filters on the period dimension.
Allows the user to navigate periods, shifting forwards or backwards from the period selected when the form is run.