Framework:Formulae

A formula is an object that has at least one source and one target table. These can also be the same table. Fundamentally, formulae differ from calculated fields in that they have a wider range of uses, their application options are more complex and their execution time is usually longer.

Formula designer

Match

Framework, Formulae

Activ

The formula is executed if a dataset is saved in the source table. The saving procedure is either initiated by a user input, or by clicking the ‘Save’ button, or by another formula in which the above source table is included as the target table. Another possibility of executing active formulae is through the macro command “Recalculate the table datasets of this plan”.

Execute in macro mode

The formula is executed when you are in macro mode. You can go to this macro mode with the macro command “Change session parameter”, with the parameter “Macro mode=1”. To return to the user mode, use the parameter “MacroMode=0”.

Formula type

Refers to the execution logic of the formula. The following types are distinguished:

• Single: One dataset generates another dataset or several datasets
• Same: One dataset calculates to itself, so it writes to a field of the same dataset, for instance
• Aggregate: Several datasets generate aggregated datasets
• Source dataset: A formula generated dataset serves to update the source dataset again

Source table

The table or dimension that is to act as the trigger for the formula

Target table

The table or dimension in which a dataset is to be created or updated

Execution Order

Processing order. This is to be used if several formulae use the same source table and if the order of the calculations is important. For instance in success planning, the formula calculating the VAT amount using the VAT rate must be executed before the formula that subsequently includes this VAT amount in the calculation of the payable amount.

Pass on deletion

Predecessor

If Predecessor is active, the following happens: if a dataset generated by a formula is deleted, the corresponding source datasets will also be deleted. This behavior is only desirable in exceptional cases.

Successor

If the Successor option is active, the target datasets generated from a source dataset are automatically deleted if the source dataset is deleted. In most cases, this behavior is desired!

Hint  ‘Pass on deletion’ only concerns formulae of the ‘single’ type. This setting does not have any effect on formulae of the ‘same’ type because the dataset itself is deleted anyway. The following applies to formulae of the ‘aggregate’ type: when a source dataset is deleted, the formulae are executed but the target dataset is not deleted because it aggregates several source datasets into a target dataset.

Iteration table In order to call up a formula several times with different parameters, you can use iterations. If the elements on which iteration is to be performed are to be provided by a table, the relevant table is selected here (see following example under Iterations).

Assignments conditions

Framework, Formulae - Conditions & Formulae

Assignments

In the upper part of the “Assignments conditions” tab, the contents of a formula are defined. While the left column, “Source”, has a calculation or a value from a source table, in the right column we select “Target”, i.e. the target table field to be written to. To specify my new connection, click the upper “Add” button. Then with a click on the … icon jump to the editor of the corresponding field. There, all the fields of the source and target table as well as all functions, variables and application properties are available.

Maintain synchronicity between shared lookups

For value fields, connections between source and target are explicitly specified by means of separate lines in the assignments. Dimensions can also be matched in this manner – by checking “Maintain synchronicity between shared lookups”, however, all dimensions whose alias names in source and target coincide are automatically synchronized. Fields that are not matched or cannot be linked with this option will be supplied with their default value in the target when newly created.

Functions

Functions can be selected in the formula designer; they facilitate comprehensive calculations and data manipulations in formulae. An overview of the CoPlanner functions you find here.

Conditions

Any conditions for the formula are defined in the lower part of the “Assignments conditions” tab, i.e. the formula is only applied if certain conditions are fulfilled. In the standard model of CoPlanner, for instance, all formulae are limited to the plan period. To insert a new condition, click the lower “Add” button. Then with a click on the … icon jump to the editor of the corresponding field. There, all the fields of the source and target table as well as all functions, variables and application properties are available. Between the two values to be compared, please choose a fitting comparison operator, and in the end define a type of comparison (numerical, text or date). If the specified condition is fulfilled (e.g. [sales revenue_PLAN.turnover]>0), the formula for this dataset will be executed.

Iterations

Framework, Formulae - Iterations

Iterations are available for the multiple execution of one formula with different parame¬ters. The Iterations tab is available for this task. At first, add a parameter (a column) with the “Add parameter” button, then insert any number of lines you wish with “Add ite¬rations”. Then assign the values for the parameter.

If you need additional parameters, add them with the “Add parameter” button. With “Delete parameter”, you can also delete them. To delete a particular iteration, highlight it and press “Del.” or the “X” icon at the right end of the dataset.

In the formula itself, you address the values defined in the “Parameter” fields with the placeholder [PAR 0] etc.

If you have defined a formula which writes the value [PAR 0]*100 to a target field and then defines two iterations that contain the values 0.8 and 0.2, two target datasets will be created when the formula is executed, one with a value of 80 and the other with a value of 20.

If an iteration table has been specified, the “Iterations” tab is replaced by the “Column mapping for iteration table” tab. As you have now specified a table upon which iteration is performed, all datasets of this iteration table will be processed automatically one after the other. All available fields of this table can be used as parameters. To identify them, click “Add mapping”, then “New entry” and finally select a field.

The time dimension is frequently used for such iterations, for instance if a dataset is to be generated for all months within a certain period (to be defined via the conditions of the formula). For the time dimension, the restriction to months only is additionally recommended, for instance with the “fct_TimeDimTypeFlag” function.

Framework, Formulae - Column mapping for iteration table

Groups

These are only used with the AGGREGATE formula type. They define by which dimensions the target dataset is to be grouped.

Framework, Formulae - Groups

Target record attachment

The “Target record attachment” option specifies whether the generated groups of datasets are to be updated per table, per formula or only referred to the fields specified in the grouping.

Iterations from table

Framework, Formulae - Iterations from table

If an iteration table is stated in the “Match” tab, this can be used to jump to the datasets of the iteration table.