Skip to content

Dictionaries

Introduction

Dictionaries are lists of elements that allow you to restrict the permitted values for a Dataset field. In particular, when a dictionary is created, its items can be defined using an SQL query. Every dictionary item therefore corresponds to a value that is available for the dataset field. Based on the selected connection, the tab to be used is identified: if the connection is primary, one of the following fields is used:

  • MsSql
  • Oracle
  • Postgres
  • Hana Whether the connection is secondary or tertiary, the query in the Other Body tab is used Secondary connections are prefixed (S) and tertiary connections are prefixed (T).

Primary connection means a connection defined as primary in the configuration file of the standalone.xml application server.

A secondary connection is a connection defined as secondary in the configuration file of the standalone.xml application server.

A tertiary connection is a schemaless connection defined as primary or secondary in the configuration file of the standalone.xml application server.

When the dataset is accessed via a data entry form, if the dataset contains fields with dictionaries, a drop-down menu with the selectable values is shown for the cell of the field with the dictionary. If multiselection is active on the dataset, it will be possible to choose more than one item in the drop-down menu.

Note: you can specify which fields used in the query contain personal data and the permitted values. When the dictionary is saved, the system validates the inserted fields with those written in the query.

Details of the query body

The query definition defines the elements that make up the dictionary. The query may refer to one or several fields: one field to determine the code and one or more fields to identify the descriptions depending on the languages supported by the system. The order in which the elements of the SQL query are defined determines the order in which the values of the dictionary are shown in the drop-down menu for the form cell.

The query that defines the dictionary items can refer to the metadata attributes (e.g. the scenario code). These attributes are resolved at runtime based on the execution context (e.g. based on the scenario or Entity on which the DTP is being run), or based on parameters set by the user at runtime.

If the dictionary is associated with a form cell (i.e., a dictionary linked to a dataset field), these attributes are resolved at runtime using the form, sheet, and matrix filters applied to the cell as context. Row/column filters set on the cell itself are therefore excluded from the context.

The syntax for specifying attributes corresponds to the syntax used for specifying cell fields in the reporting (see ). To identify an Advanced selection script within the SQL query, the syntax must be enclosed between brackets like in this example: ${<...>}.

When relating the dictionary to a field of the dataset (see Dataset definition page), it is possible to define the fields resulting from the create dictionary query to be used to determine the code (Query field code) and descriptions (Query field description). These two properties are optional: if they are not defined, the first field of the query defines the code of the dictionary items, while the second, if present, defines their description (if absent, the first field of the query will be used to define both the code and the description).

Dependent dictionaries

To use dictionaries in reporting, it is possible to create dictionaries with selectable values in a field's drop-down menu that depend on values of other fields in the same dataset. To create these relationships, you must:

  1. In dictionary settings, create a dictionary element containing a code, description and dependencies, indicating the corresponding dataset fields to be related.
  2. In dataset settings, associate the dictionary with the fields involved.

Note: Dependent dictionaries are not compatible if multiselection is active and more than one value has been selected within the dictionary

Example

Given a Dataset containing a City field and a Country field, it is possible to define a dictionary that returns the following elements:
CITY_CODE CITY_DESCR COUNTRY_CODE COUNTRY_DESC
01 Milan EN Italy
02 Rome EN Italy
03 London UK United Kingdom
04 Liverpool UK United Kingdom
05 New York US United States
06 Los Angeles US United States

To do this, relate the Dictionary fields to the Dataset fields as follows:

  • Query field code City: field code CITY_CODE
  • Query field descriptionCity: field description CITY_DESCR
  • Query field code Country: field code COUNTRY_CODE
  • Query field description Country: COUNTRY_DESC

In the data entry step, if the user selects the Country Italy, they will only have Milan and Rome available in the City field, while if they select United States they will only have New York and Los Angeles. The dependence works in both directions: if Liverpool is selected, the country field will only allow United Kingdom to be selected.

Dictionaries and user language

When, in defining a dataset, a dictionary is related to a text field, the Query field description property is defined, and the results of the dictionary contain several description fields related to several languages, the system selects the value with the description in the language of the connected user. In the absence of this correspondence, the field is searched in the query results as specified.

Example

This example shows how to use dictionaries in the presence of several system languages.

If the following languages are defined in the system:

  • Language 0: English
  • Language 1: Italiano
  • Language 2: Français
  • Language 3: Deutsch

A field of the dataset associated with the dictionary indicated above specifies the following properties:

  • Query field code: CODE
  • Query field description: DESCR_

The query specified in a dictionary returns the following fields:
CODE DESCR_0 DESCR_1 DESCR_2 DESCR_
EN Italy Italy Italy Italy
UK United Kingdom Regno Unito Royaume-Uni United Kingdom
US United States Stati Uniti États-Unis United States

If the connected user uses English, the system concatenates the suffix 0 to the DESCR_ value, and shows the descriptions in the field reported in the DESCR_0 field of the query.

If the connected user uses German, and the field DESCR_3 is not found among the query fields, the system shows the descriptions reported in the DESCR_ field.