Features supported by web forms
Introduction¶
Forms can be enabled for use as web forms in the form settings in the Excel interface (see New form window) or from the table of forms in the web interface (see Forms page).
IMPORTANT: in web forms, the character "!" cannot be used in template names. If inserted, the system will remove it, preventing everything that points to these templates from functioning (e.g. hyperlinks).
Supported formulas¶
Web forms support the following Excel formulas. For the detailed list, please refer to the official SpreadJS list. Anything that is not expressly indicated is not supported.
Cell Formats and Locale¶
Separators for thousands and decimals for numeric formats, as well as date/time formats, depend on the nationality set in user configuration in CCH Tagetik.
A non-exhaustive list of the differences detected in the test phase is provided below:
- For Japanese nationality, the "short date" format; in contrast to Excel, it always uses two characters for the month (e.g. Excel: "1976/8/30"; Web form: "1976/08/30").
- For the UK and Italian nationalities, the format "30/8/76 16:28"; in contrast to Excel, it uses two figures for the year (e.g. Excel: "30/8/1976 16:28"; Web form: "30/8/76 16:28").
- For Polish nationality, the "long date" format could use different variants of the names of months and days from those used by Excel.
- For Turkish nationality, the thousands separator is a space.
- Formats with fixed localisation set from Excel design (e.g. 14-Mar-18) are adapted to the user's locale, excluding cases in which the set fixed localisation is American English (EN_US), Chinese (ZH_CH) or Japanese (JA_JN).
Since release SP15, the "short date" and "long date" formats identified with "*" in the Excel add-in are overwritten by the user's local configurations.
Supported navigation features¶
A list of the supported navigation features is provided below:
| Element | Description |
|---|---|
| Single and Multi-Template | Forms defined with several templates including tab filters are rendered. There are restrictions in data entry mode. Formulas and charts that refer to names (Excel named range) may not be defined correctly in sheets after the first of each template (e.g., formulas that refer to names generated by CCH Tagetik together with custom queries), so we advise against using them. Charts could lose some of their formatting and colour palette (where the standard palette has not been used) in sheets after the first. |
| Changing the value of parameters and the Refresh function | The value of parameters can be changed from the relevant bar after the form has been run and the form can be refreshed using the dedicated button on the toolbar. |
| Further information | - Comments are enabled to be displayed in navigation mode and attachments can be downloaded via the comment details window. - If the automatic loading of comments is disabled, they can be loaded using the dedicated button. |
| Edit IC | - Intercompany declarations can be inserted directly from an editable cell of a web form. - Pre-existing declarations can be edited and deleted. |
| Advanced selection | - Advanced selection on rows and columns is supported. For correct formatting, we recommend using Tagetik styles, without which the same formatting used by the Excel add-in cannot be guaranteed. - All matrix expansion options are supported, i.e. "Move cells down/right", "do not expand" and "entire row/column". Note: for the double expansion of multidimensional matrices, with a double "Move cells" option (for both rows and columns), the cells involved in the expansion will be first be moved down, where possible, and then the remaining cells will be moved right. To the extent that it is possible to have advanced selection on both rows and columns simultaneously, some features are not yet fully supported with the double "Move cells" option. Some cells are not moved and, in particular, some Excel components, such as named ranges and notes, are not fully recalculated. |
| Matrix layout options | - The automatic adjustment of columns "by value" is supported, but adjustment "from the template" is not supported. Matrices with automatic adjustment "from the template" are treated as if they had automatic adjustment "by value". - The "Hide row/column" matrix option is supported. |
| Tagetik styles | CCH Tagetik styles are supported, except for the numeric format. IMPORTANT: to avoid inconsistent fonts between Excel add-in, Web form visualization, and files exported from Web forms, configure the form by explicitly specifying the font in the style (rather than using the theme-based fonts “(Body)” / “(Heading)”). |
| User queries | User-defined queries are supported. These include the following options: - Show Query Name - Show headers - Create name for the result - Apply the format of the first row to the whole Output area - Automatic adjustment of columns Copy the formulas to the left/right of the query to the Output area. Queries that refer to Excel cells via A1 syntax are not supported, e.g. ${A1}. User-defined validation queries are supported, including the "Enable free text insertion" option and the ability to define dependent queries. Drill-through queries are supported. |
| CCH Tagetik hyperlinks | - CCH Tagetik hyperlinks are now supported to run data processing jobs and drill-through queries and to open modules. Hyperlinks can only be defined as "Open form specified in the next row/column" for web forms and for matrix hyperlinks. - CCH Tagetik hyperlinks defined in a numeric cell are not yet supported. Standard Excel hyperlinks are not supported. - CCH Tagetik hyperlinks support the running of processes with parameters defined on Excel cells. - The reference to the Excel Cell must only indicate cells of the current sheet in H30 format (for cell H30). The reference to the Excel cell is not supported for opening another form via hyperlink. |
| Bookmark | Bookmarks can be created for running forms in navigation or data entry modes. These bookmarks can be used to run forms directly from the Home Page with the current filters, without them being requested. |
| Tab filters | Filters that have an effect on all matrices defined in the template are supported. |
| Hide rows or columns | This functionality is supported, except where it depends on the result of Excel formulas. |
| Drill Down | Double clicking the headers of rows or columns not at the maximum level of detail activates the drill down, i.e. displays detailed rows or columns. The following features are not supported after a drill down: - it cannot be guaranteed that all the formulas that refer to the row subject to drill down will always be refreshed correctly - hide rows or columns - validation checks - comments Row and column drill down follows the expansion methods defined when the matrix was designed. In relation to "Move cells down/right", malfunctions have been detected in the lateral grouping and in the application of styles on column deployments. |
| Overridden content | The ability to override the content of static matrix cells is supported. |
| Linked templates and matrices | The ability to use links to matrices and templates of other forms is supported. |
| Multi-selection | For transactional matrices, applying multi-selection in the counter options allows you to obtain the column with cells for selecting check boxes for every record (row/column) where it will be possible to select several records (rows/columns) simultaneously. These boxes can be used in Excel formulas, taking on a positive value when selected, or a negative value otherwise. |
| Wrap Text | For transactional matrices, in the event that a column has the Wrap Text style option enabled, when a form containing that matrix is opened, the cells belonging to that column widen automatically based on the text that the cells contain. At the moment, this is only supported on Line by Line transactional matrices to which a CCH Tagetik style is applied. |
| Grouping | Grouping supports the following formulas: - SUBTOTAL - Algebraic sum of cells (does not support the native SUM function) The grouping’s orientation is determined by the Subtotal before the detail flag, with the following behaviour: - If not selected, the grouping’s orientation is standard and only formulas which have adjacent items (or items that end up adjacent after lower-level groupings have been completed) above the formula will be grouped. - If selected, the grouping’s orientation is the opposite of standard and only formulas which have adjacent items (or items that end up adjacent after lower-level groupings have been completed) below the formula will be grouped. For Synthetic FSTs, however, the grouping's orientation is determined by the first formula to be used: - If the formula refers to items positioned above, the grouping’s orientation is standard and only formulas that use adjacent items above the formula will be grouped. - If the formula refers to items positioned below, the grouping’s orientation is opposite to the standard and only formulas that use adjacent items below the formula will be grouped. |
| Rich Text | Supported only on transactional Line-by-Line matrix using a formatted text type field (see Dataset definition page and Text field characteristics), it allows formatted text to be displayed on a specific cell. - An unformatted preview of the text entered in Data Entry can be displayed on the cells of the column related to the formatted text type field by means of an editor. - To view the entire formatted text, which is available in read-only mode, it is necessary to open the panel containing the editor, which can be accessed via a button in the ribbon bar (see The web forms page (Navigation)). |
Supported data entry functionalities¶
| Element | Description |
|---|---|
| Single and Multi-Template | Forms defined with several templates including tab filters are rendered. |
| Editability | - Lock editing for sheets in both data entry and navigation modes (when the "Lock output sheets in navigation" option is active) and unlock cell editability. - Conditional editability - Only unlock editable cells and areas explicitly defined as "Unlocked" by the user in design mode - Different colouring for editable or unlocked cells, protected cells, editable cells with IC, editable cells with edit transaction currency (IC editor and transaction currency not available at the moment) - Save edited values (without updating values) Validation checks on numeric values use a tolerance threshold defined when the form is defined, in the Form options window (see Form options window). |
| Open new rows/columns | New rows or columns can be inserted. - New rows are formatted as per the applied Tagetik style. - The formulas present on the relative columns are propagated on the new rows, with the exception of formulas with bidimensional references {row,column}, formulas with offsets {+x}, {-x} and Server-type formulas. Where such formulas are present, an error message will be shown. - Inserted records can be deleted using the delete button in the Status column when selecting the row. - For transactional matrices, the number of rows/columns to add is not required, as the system always adds them individually. - For multidimensional matrices, the refreshing of validation checks, validation queries and comments is supporting. |
| Multi-selection | For transactional matrices, with multi-selection active in the counter options, you can obtain the column with cells for selecting check boxes for every record (row/column) in which it will be possible to select several records (rows/columns) simultaneously. These boxes can be used in Excel formulas, taking on a positive value when selected, or a negative value otherwise. Multi-selection is not supported in Line By Line matrices of a validation query is used. This behaviour differs from the Excel client. |
| Spreading | The buttons for locking and unlocking cells can be accessed from the sheet's context menu: - the Lock button locks the selected cells - the Unlock button unlocks the selected cells - the Unlock all button unlocks all cells in all sheets Arithmetic operators to be used directly in the cell are not supported. Editing a cell containing a formula whose valuation is X to apply a spread, using X as the desired value, may make the form inconsistent (e.g. loss of the cell formula). The value of a cell containing a formula, even if locked, could be modified by a spread operation on another cell with a formula when the two formulas have at least one cell on which they both depend in common. When a cell with a formula is edited, only the values of the cells on which it depends are reconfigured, even when another locked formula depends on the cell. Even in this case, the value of the cell with a locked formula may change. When the two spread methods (formula, as it contains a formula, or inverse, as a locked cell with a formula depending on it) are applicable to a cell with a formula, both methods are applied in sequence. After the value in the cell with the formula has been changed, applying spreading activates the Cancel spreading option (). This allows you to restore values from prior to the activation of the Spreading method on all edited sheets. Where Spreading is applied several times to the same cell without the form ever having been saved, when the Spreading action is cancelled the restored values are the original values, shown when the form was opened. The following option is deactivated in the following situations: - If Spreading has not been applied at least once, or if the cell with the formula has not been edited and the user has pressed Enter or moved on to another cell. - If another item from the main menu (Home/Analysis/Utility) is selected during the Spreading step with the option active. - If, in Spreading mode with the option active, the user clicks in the form on a hyperlink that entails actions including "Save". |
| DatePicker | For editable date-type cells, double clicking will reveal a DatePicker to select the desired date. The appropriate Tagetik style for these cells must be set in Excel, otherwise cells in which a date is selected will be shown in numeric Excel format. When a date-type cell is copied, using the copy-paste function or by dragging the source cell, the DatePicker will be deactivated in the target cell. Therefore, copied dates will not be editable. Note: the DatePicker is not currently supported on the Safari browser. |
| Further information | - The display of comments in data entry mode and the download of attachments via the comment details window are enabled. - If the automatic loading of comments is disabled, they can be loaded using a specific button. - It is also possible to edit a pre-existing comment and add a new comment (which will also be visible in navigation mode if the relevant option is enabled in the Comments section of the Settings window. |
| Validation queries | Validation queries are supported, even with dependent rows/columns, including the Enable free text insertion option present in the Query Wizard (see Query Wizard window). Note: in the presence of Dictionary and Validation Query insisting on the same textual measure, the dropdown shows all values in the validation query as suggestions, but if data validation is active, only those values belonging to the Dictionary are saved. An exception is made if the 'Out of dictionary values allowed' flag has been enabled on the measure. When the elements that are returned by the query and can be selected in the cell are figures (e.g. 01, 02, etc.), the cell must have General or Text formatting; otherwise, the system could overlook the zero at the start and treat the selected value as a number. Note**: in contrast, the Excel client always treats the selected value as text. |
| Dictionary | Dictionaries are supported - on the columns of transactional matrices related to Analytical Workspace datasets - on textual measures in multi-dimensional matrices related to Analytical Workspace datasets with non-incremental storage Validation in the presence of a dictionary on Multi-Dimensional matrices Automatically enabled once the dictionary is configured to the textual measure. Validation is based on the display mode combined with the set of values defined with the dictionary. - If the display mode of the dictionary is Code and the value you want to save belongs to the range of codes defined in the dictionary, then validation is successful and the data is saved. - If the display mode of the dictionary is Description and the value you want to save belongs to the range of descriptions defined in the dictionary, then validation is successful and the data is saved. - If the display mode of the dictionary is Code and Description and the value you want to save belongs to the code range or the range of Codes and Description defined in the dictionary, then validation is successful and the data is saved. - If the flag Out of dictionary values allowed is enabled, validation is always successful and all values are saved Note: in the presence of Dictionary and Validation Query insisting on the same textual measure, the dropdown shows all values in the validation query as suggestions, but if data validation is active, only those values belonging to the Dictionary are saved. An exception is made if the 'Out of dictionary values allowed' flag has been enabled on the measure. Note: the dictionaries described above are also supported on the Excel client.** |
| Validation checks | Validation checks are supported, both inside and outside the matrix. The On save and On close options have no effect in web forms because the checks are run continually and messages relating to failed checks are shown in a pane at the top-right of the interface. The occurrence of blocking expression breaches prevents data from being saved. The general functioning of validation checks positioned below or to the right of added rows or columns is guaranteed. In financial forms, added rows are not subject to evaluation. Validation checks on numeric values use a tolerance threshold defined in the form configurations, in the Form options window (see Form options window). |
| Wrap Text | Only supported on transactional Line by Line matrices to which a CCH Tagetik style is applied. When text is inserted on columns of a transactional Line by Line matrix on which the Wrap Text type option is enabled, or when a form containing the matrix is opened, the cells belonging to that column expand automatically based on the text that the cells contain. |
| Running ETLs | In data entry mode, ETLs can be run via the button present on the toolbar in the Home section. To be able to run an ETL, the user must select a previously configured job that has been associated with the process for which data entry is being performed. One or more parameters needed for running the ETL can be associated with the job and, by default, those parameters are defined with values inherited from the form execution context (e.g. Workflow). The value of those parameters can also be changed via the Filters panel and then the form can be updated before the ETL is run. In this case, the values of the parameters will be those of the form filters, and not those of the execution context. For an Entity-type parameter, it is possible to select hierarchy elements (and therefore all the entities belonging to the hierarchy). As it acts on the Filters panel, at the moment the ETL does not manage multiple scenarios, so only one scenario must be selected. |
| Rich Text | Supported only on transactional Line-by-Line matrices using a formatted text type field (see Dataset definition page and Text field characteristics), it allows formatted text to be inserted on a specific cell. - Formatted text can be entered using the relevant editor. Once the cell of interest has been selected, the editor is opened via a button in the ribbon bar (see The web forms page (data entry)). - The text is only seen in formatted mode in the relevant panel, whereas an unformatted preview of the entered text is displayed on the matrix cells. - It is possible to insert text using cut and paste from other editors (e.g. Word), in these cases the formatting will be maintained within the limits of the editor's possibilities. - Only text input in the editor is permitted, images or other multimedia elements are not supported. - To close the editor, either use the 'x' button in the editor itself or select the button in the ribbon bar again. - It is recommended to insert a Wrap-Text style to allow better reading of the formatted text preview |