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
Bool
Link

Hinweis 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.

Hinweis Datatype link can be used for usual weblinks or CoPlanner-links.

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 (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.

Visible for analysis (Power BI)

Defines, if the field is loaded for analysis in Power BI or not.

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.