Framework:Tables
The data entered by the user are saved in tables. The user may enter data in tables by means of input masks. In order to process a table, highlight it and press the “design” button or select “design” from the context menu (can be called up by pressing the right mouse button). Double-clicking on the table will also open it in design view.
The table designer opens:
Table designer
It consists of three areas:
- the tabs
- the tab area
- the general area
General area
There are five buttons, and their function is as follows:
OK
Applies the modifications and closes the table designer.
Update table If the table does not yet exist, “Update table” creates it. New fields, which are subsequently available, are created in the database. For dimensions, default elements (root element and “No assignment” element) are created. “Update table” carries out the following steps:
- The definition of the table is saved
- All fields that are enabled (-> red triangle, these fields are not yet in the data table and can be processed without restriction up to this stage!) are now applied in the data table (also in database!) and cannot be renamed from now on!(-> because there is no consistency of the corresponding DB commands across different databases!)
- Other settings of fields that do not effect a change in the database can still be processed, as they are made when the table is “loaded”!
- Table is reloaded on the server (see “Apply“)
- The data of the data table remain unaffected!
- Deleted columns will only be removed from the database when updating is performed!
Apply
“Apply” saves the current definition – tables are not re-created in the database. Newly calculated fields are available immediately. Note Saves the definition of the table on the CoPlanner server; but does not apply these changes in the real data table on the database server! Green-ticked fields have already been applied in the data table (with “Update table”!), fields with the red triangle have not been applied in the data table! An exception are calculated fields, which are also applied in the data table with “Apply”. This is because calculated fields are not saved in the database. Instead, they are added by the CoPlanner server and only available in the memory (in the server cache)! The following steps are carried out:
- The definition of the table is saved
- The table is reloaded from the relational database to the CoPlanner server (as it is!)
- Table is revised with information from the table definition (e.g.: calculated fields are added!)
- Changes in the definition that require a re-creation of the data table in the database are not performed! (-> red triangle!)
- The data of the data table remain untouched!
Re-create table
The table is re-created in the database. Note Caution – this deletes all existing data!!! “Re-create table” carries out the following steps:
- The existing data table is deleted!
- The data table is re-created, as if a new table was created manually!
- This means that the new table now consists of system columns only!
- The table is reloaded on the server (see above!)
- The fields saved in the definition are now enabled again as they are not in the data table! They can now be processed, and the table can then be rebuilt with these fields ->“Update table“
- The data of the data table are lost!
Cancel
Nothing is saved and the window is closed.
Fields
Here we are in the “Fields” tab. To create a new field, click on the “Add field” button.
Column name
The name of the column can be entered individually in this field.
Data type
The following data types are available:
- Decimal number
- Integer
- Percentage
- Text(50)
- Date
- Currency
- Bool
- Link
It is possible to change the maximum size of the text (defined by the number in brackets) for the “Text” data type, simply by increasing or reducing the number in brackets.
Default value
In this field, it is possible to define any default value that is suggested to the user as a value in this field. If a new dataset is created, this default value will be written into the field of the new dataset. Of course, the user can change the default value at any time.
Default code
Contrary to the static default value, the default code is a variable value that depends on the session with which the user is logged in at the CoPlanner server.
Description
Here you can enter a short description about the content of the column. Aggregation type The following aggregation types are available:
- None: no calculation is required
- Sum: The sum total of all fields in one column is calculated
- Avg: The average of all fields of a column is calculated. The calculation is performed as follows: total of the values of the datasets / number of datasets.
Format string
Here you can specify a standard format string (see also page 52, number formats) which is then applied to masks for which no separate formatting is defined. The default without the definition of a format string is N2 for decimal numbers respectively P2 for percentage values.
Visible in LookUps
If “visible in lookups“ is activated, the corresponding “Column name” field in the combination list field is shown for selection in the linked table (more about this topic on the next page).
Multilingual
Specifies whether multilingual saving of a field is possible. This setting can only be used for text fields. Its effect is as follows: if a text field is overwritten in another than the default language, the new value will be saved only for the login language, i.e. as a translation. The originally entered value continues to be visible for a login in default language.
IsAttribute
If this option is chosen, the value of the attribute is available in CoPlanner Reporting. For instance, here it is possible to define a background color in a dimension for the corresponding element, and it can then be read out in reporting and processed. This is interpreted only if used in conjunction with a dimension.
IsOK
Shows if changes have been updated, saved and implemented correctly. If you see a red triangle with an exclamation mark like in this example, you must press the “Update table” button and save the selections you have made in this way.
| Hint A column can be deleted by clicking X or selecting the line and hitting the “del” key. Please note that if the column has already been applied (indicated by a green check), data entered in it are also deleted. |
| Hint Once “column name” and “data type” of a column have been applied, these properties cannot be changed any more. If there already are data in this column, it is recommended to re-create another column, then to manually copy the values and finally to delete the old column. |
LookUp tables
Here you can link tables. A table is only a “linkable” LookUp table if “visible in LookUps” has been checked for at least one field. Only then can the corresponding fields in the linked table be displayed – by clicking in the corresponding field, a combination list field with the LookUp data opens.
Dimensions
Similar to the LookUp tables, by clicking the corresponding field the user is again presented with a combination list field. The only difference is that this time he/she is offered a structural level for selection.
Dimension name
Name of the dimension, is automatically applied
Number
Assigns a consecutive number per dimension. If a dimension is added several times, each receives an own counter for the purpose of unambiguous identification.
Alias
The dimension name is automatically applied as an alias. However, it can be adjusted.
Language
By entering a translation, an alias can be specified for the corresponding language. For instance, if you write the expression “english=Cost Center;” into the language field in the above example, the alias of this dimension will be displayed as “Cost Center” rather than “PC/KST” for the case of an English login.
Default value
It is possible to define any default value that is proposed to the user as a value in this field. If a new dataset is created, this default value will be written into the field of the new dataset. Of course, the user is free to change it at any time.
Default code
Contrary to the static default value, the default code is a variable value that depends on the session with which the user is logged in at the CoPlanner server. Furthermore, any application property may be defined as a default value.
Plan period
Checking this box next to the time dimension specifies that the time limitation set in the scenario manager is to be used (allow input only in this period, lock actual periods).
IsOK
Displays whether changes were updated, saved and correctly implemented. If you see a red triangle with an exclamation mark like here, you must press the “Update table” button and in this way save the selections you have made.
Calculated fields
Calculated fields consist of other fields of the table. In this example: Calculated field: [Total salary] = [Salary] + [Christmas bonus] + [Vacation bonus] + [Bonus]
For an optimum resolution of the expressions, it is necessary to set every expression in brackets, for instance ([a] * [b]) + [c]
Syntax of expressions
A list of possible expressions is found here.
In the following example, depending on the “Value” field, the following is to be written into the calculated field “Amount”: either the value itself if it is not zero, or the calculation “Quantity*Price“:
Calculated field: Amount= 𝐼𝐼𝐹([Value] =0,[Quantity]*[Price],[ Value])
