Particular features of Line by Line forms with transactional matrices

Background¶

Line by Line forms allow you to perform data entry in a more flexible and customisable way. A form containing transactional matrices related to Analytical Workspace datasets has the particular feature of comprehensively showing all the records during the access phase in data entry mode: the retrieved records are filtered on the basis of the form filters (Form filters, Tab or matrix filters) and user rights. fields and editability settings are shown on the basis of their structure management.

How transactional matrices work¶

When a Line by Line form with a multidimensional matrix related to a Analytical Workspace dataset is accessed, the matrix functionality is standard for the user: the matrix retrieves from the dataset the fields corresponding to the filters on the fields present in the same matrix and, depending on the form type and user rights, writes, via the matrix, on the same fields.

The matrix structure shows the following particular features:

The dataset fields can be set either on rows or on columns of the matrix; however, filters cannot be set on both rows and columns.
The matrix contains the Counter element which, when the form is run, corresponds to a row or column that enumerates the records read by from the dataset (see Details of the Counter element).
The matrix may contain the Status element which, when the form is run, makes it possible to delete the record on a row or column (see Details of the Status element).

To correctly manage the partitioning of a dataset’s data, filters or parameters related to the Scenario, Period and Entity dimensions must be added to the reports and data entry forms, as per the following guidelines:

If...	Then...
the user wants to open, in the data entry phase from a process that can be submitted by Scenario/Period, a form containing a matrix associated with a partitioned dataset	the specific Scenario and Period elements, valid for the entire matrix, must be specified in the filters applied on opening. Note: this requirement is not applicable for non-partitioned datasets.
the user wants to open, in data entry or navigation mode, a form containing a matrix associated with a dataset partitioned by Entity	the Entity element must be specified in the filters applied on opening. Note: this requirement is not applicable for datasets not partitioned by Entity.

These conditions are also valid for datasets that do not contain the Scenario, Period or Entity dimension fields.

Note: the transactional matrix’s dataset may include both analytical dimension fields and Financial Workspace fields.

Details of the Counter element¶

The Counter element, which can be selected in the dimensions tree of the Row/column filters tab, is useful for data entry forms in which the number of records shown in the table can vary. When the form is run, it adds a column containing a numbered list of the rows displayed, to make it easier to read the form.

How the new rows are managed will depend on the settings of the selection attributes on the counter element, which can be accessed in the Matrix Wizard’s properties table (see Matrix filter properties table).

Details of the Status element¶

The Status element, which can be selected in the dimensions tree of the Row/column filters tab, allows contributors to delete records from the dataset. When the form is run, for every dataset field it shows the Status column, containing a button to delete the record.

Note: if the status column is not present, then data can only be entered or edited.

Creating Line by Line forms using transactional matrices¶

Transactional forms based on a Line by Line matrix can be created in the following ways:

Automatically, with the Data entry command from the actions menu in the dataset definition. In this case, the form will contain a column for every field of the selected dataset.
Manually, directly within the Excel add-in. In this case, it is possible to define, in the matrix, the dataset fields to be shown in the form.

Fields that are not managed will be ignored when the dataset is read. On saving, if any (form, tab or matrix) filter refers to fields that are not managed in the matrices, then they are set and filled out, if they can be taken from a form filter (form, tab, or matrix filter), or simply left empty.

For further details on the Analytical Workspace settings, see Dataset settings window.

Further information¶

In transactional forms that refer to an Analytical Workspace dataset, one or more comments can be added in the data entry phase, under the following conditions:

This cell must be editable.
The cell refers to a measure field of comments and attachments type.
If the cell is part of a newly added row, at least one other cell (dimension or measure) of the new row must be populated.

For more details on inserting comments, see Cell edit - comments window.

To view comments inserted on a cell during the navigation and data entry phase, the Display automatically comments in reports data entry and Display automatically comments in navigation options (Home page - Administrator > Settings > forms tab > Comments section) must be enabled. See Settings window.

Note: With the new function 0004073 it is possible to insert comments on both LBL and MultiDimensional matrices. As a result, edit/delete operations are only possible on comments inserted after submission and only if the function is enabled. However, these operations cannot be performed on comments inserted pre-submission. Moreover, editing a comment inserted during the submission phase means that the comment becomes pre-submitted when the data item is rejected, and it will no longer be editable at the time in which the user decides to resubmit the data.

In transactional forms, it is possible to configure, via the validation window, a check for the presence of attachments on a comment cell (HasAttachment). The operator to be selected within the validation window is 'Contains Attachments' see Validation Window.

Note: If this type of validation is configured, the option Automatically display comments in reports data entry (Administrator Home Page > Settings > form tab > Comments section) is enabled by default, regardless of whether it is enabled in the Settings window.

Particular cases regarding editability¶

In general, the editability of form cells is determined by the settings on the matrix row/column editability attributes (see Editability). However, cell editability in Line by Line transactional forms depends on the following combination of conditions:

The cell must be defined as editable in the matrix.
The editability property of the dataset fields must be enabled (see Editability).
An Account-type dimension field subject to the submission process must be present.
One or more measure-type fields subject to the submission process must be present.

For more information on editability checks, see Checks on multidimensional matrices.

If a non-partitioned dataset has a field defined as key in the Field Properties panel, on saving the form on which cells have been edited, the system checks that the newly added keys are unique and will potentially display an error message. The message indicates the field and the keys that infringe the non-duplication requirement.

Particular cases regarding editability for the processes with dimensions by step¶

Depending on the dimensions used in the dataset or partition logics, a matrix may or may not be subject to editability checks that verify coherence between accounts and measures and the process definition and their potential submission.

The system therefore performs checks on accounts when the dataset contains, in the fields themselves or due to partition logic, the Scenario, Period, Organization dimension and Account or Scenario, Period, Organization dimension, Account and Category, Scenario, Period, Organization dimension and Account or Scenario, Period, Organization dimension, Account and Category dimensions.

Note: for running checks, the Category dimension is only needed if the process submission is by Account/Category.

The system performs the checks related to a measure when, together with the Scenario, Period and Entity dimensions, the dataset contains the Measure dimension in question in the fields themselves, or due to partition logic. If the dataset contains data which are subject to submission in the process, the dimension fields of these data will be non-editable on the rows already defined.

Example

In the presence of a dataset in which the Scenario, Period and Entity dimension fields and an analytical dimension are editable, since the Scenario/Period/Entity combination identifies the dimensions subject to submission in the process, those fields are not editable on rows already defined. However, it is possible to edit other dimension elements on rows that have already be saved, the analytical dimension in this example, since the value of those dimensions does not impact the data’s submission status.

The system also made all the cells of a row that has already been saved non-editable if at least one of the following conditions is met:

The original scenario/period doesn't belong to the process;
The scenario/period belongs to the process but is locked;
The scenario/period belongs to the process but is not active.
The period does not belong to the scenario.
The entity does not belong the process.
The matrix shown on the form refers to a dataset containing a key measure field (PK).
When building the matrix, two different filters, two time-dependent nodes or one time-dependent node and another dimension belonging to the same category as the time-dependent node are used.
When the datasets contain the Account and are subject to submission, and the Entity-Account pair is not valid for the process.
When datasets contain the Account and the Category, are subject to submission, and are opened on a process with Account-type submission, the Category does not fall within the scope of the Categories filter specified in the process.
When the datasets contain the Account are subject to submission and are opened on a process with Account/Category-type submission, and the Entity/Account/Category combination is not valid for the process.
When the datasets contain the Account and are subject to submission, and the Scenario Period Entity Account combination refers to a submitted step.
When the datasets contain the Account, are subject to submission, are opened on a process with Account/Category type submission, and the Scenario/Period/Entity/Account/Category combination refers to a submitted step.
When, due to limitations on the dataset, the user views "Database not visible to the user" in Editability Info.
When the user is limited to read only mode on one or more dimensions, "Row not editable due to limited visibility rights (read only)" will be shown in Editability Info. When the restriction concerns entities, Analytical dimensions and custom dimensions, the user cannot enter data for certain entities of specific organisation dimension. The message then shows the entity dimension and the codes on which edit/save operations cannot be performed. When the user is limited on several dimensions then all the dimensions and codes that violate the read-only restriction are specified in Editability Info. (See Users rights: Restrictions on dimensions and User Rights: Other Restrictions).
When the validity range specified on a lowest level element of a custom dimension or an analytical dimension is not consistent with the scenario/period pair present on the row related to the same lowest level element in the matrix.

The system makes the cells containing measures non-editable if at least one of the following conditions is met:

The measure does not belong to the process.
The measure refers to a step submitted for the current entity.

Note: if at least one of the two conditions is met on a row that has already been saved, all of the row’s dimension fields become non-editable.

To ensure that submitted data are coherent, when the row is either fully or partially non-editable for submission reasons, the cell related to the Status column is non-editable to prevent the user from deleting the specific row.

To find out why a cell is not editable, in the cell properties panel > editability section > Editability Info button.

For more information on editability checks, see Checks on multidimensional matrices.

If coherence checks are disabled, it is not possible to verify submission status. When coherence checks are disabled, submission checks are not carried out either.

Editability on Demand¶

This option is present in Line by Line matrices when the Editability on Demand option is enabled. With the On Demand editability mode, cell values are not editable and are not calculated when the form is launched but later, at the user’s request. With this function enabled (see Options in the Edit amounts tab), the user can request to edit a cell that is at that time not editable, by clicking the cell in the work desktop. The button appears at the top for by-column matrices and to the left of the row for by-row matrices.

The button calculates the editability of all the cells in the selected row or column. It is therefore possible to request the editability of adjacent or non-adjacent blocks of cells at the same time. In by-row matrices, the button appears in the last row of the selected cells but, when an adjacent block is selected, the last selected cell is the same as the first selected cell. Similarly, in by-column matrices, the button appears in the last column of the selected cells.

Updating transactional forms¶

In some cases, the form updates automatically after being saved. In particular, it is updated in the following cases:

When a predefined number of rows is set to be opened in data entry and the update is necessary to add the additional rows (see Row/Column Filter Properties Grid).
When the Editable only on new rows option is active on some dataset fields and the update is necessary in order for the saved records to be defined as non-editable.