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
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:
- Row (formerly Single): One record generates another record
- Same: One record calculates to itself, so it writes to another field of the same record, for instance
- SourceRow (formerly Quellsatz): Just can be used in comination with a formula for type row. The formula doesn't create new records. Instead it writes its values on the sourcerow auf the row-formula. E.g. A row-formula on table tb_A creates a new record in table tb_B. Manual changes in the table tb_B can be written back to the record of table tb_A using a sourceRow formla. (A formula of type row would create a new record)
- Group: Several records generate aggregated records. The calculation will be started once even there are many changes.
- Makro: It's possible to start a macro by a change on a table.
- Aggregation (formery Aggregat): Several records generate aggregated records. The calculation is triggered by every change on sourcerows. In most cases a group-formula would be more efficient.
- Source dataset: A formula generated dataset serves to update the source dataset again
- Update: Several records generate aggregated records. The formula doesn't create new records, only existing records will be updated.
Typed calculations
If TypedCalculations is true, expressions will be compiled to code functions while loading the formulas. Due to that the execution is essential faster. Parameters of that code functions have defined datatypes where they geht the name "TypedCalculations" from. Because of that change existing formulas may need to be adjusted when using TypedCalculations.
This option has to be set on formulas of typ group and update and can't bei used for formulas of type aggregation. It is recommented to use TypedCalculations for all new formulas and change existing formulas to that option and modify them to work.
| Hint Changes on formulas using FKT_GetRecord and FKT_Aggregat when using TypedCalcultaions Old: FKT_GetRecord(Kurse_TBL_Standard.Kurs, Unternehmen !me [Umsatzerloese_PLAN_REC.Unternehmen_1.COPSYS_ID], Zeit !me [Umsatzerloese_PLAN_REC.Zeit_1.COPSYS_ID]) New: FKT_GetRecord(„Kurse_TBL_Standard.Kurs“, "Unternehmen !me " + [Umsatzerloese_PLAN_REC.Unternehmen_1.COPSYS_ID],"Zeit !me " + [Umsatzerloese_PLAN_REC.Zeit_1.COPSYS_ID] ) |
| Hint FKT_CIF and FTK_CHOOSE are not supported by using TypedCalculations. |
| Hint Using TypedCalculations, there is a new function FKT_TimeDimDatePart
FKT_TimeDimDatepart('M',[tbPers.Zeit_1.COPSYS_ID]) Parameter: Y,Q,M & D (as string), no validation if the date is a valid date, no fiscal year support. |
Source table
The table or dimension the calculation is based on. (Using a formula of type group or update there can be definied additional source tables on the tab 'Tables')
Target table
The table or dimension in which records will 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 ‘row’ 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).
Tables which trigger this formula in case of changes
Former formulas just have been triggered by a macro or by a change of data in the source table of the formula.
Now it's possible to deactivate the triggering of formulas by changes of data in the source row. Just don't mark the source table in that section.
It's also possible to define other tables and dimensions which should trigger the formula. There is no need of a technical reference to the content of the formula.
Assignments conditions
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.
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.
| Hint Using a group formula the formula is just executed for records within the condition. All former other records created by that formula will be deleted. That means, if you have a condition on the dimension company using the session variable @Session_Entity_ID@ and a user changes data on company A, the formula will create the records for company A. When the user changes his logon to company B and changes data on the same table, the formula will create the records for company B and deletes the records for company A, because they are not within the condition. |
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.
Groups
These are only used with the aggregation and group formula type. They define by which dimensions the target dataset has to be grouped.
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
If an iteration table is stated in the “Match” tab, this can be used to jump to the datasets of the iteration table.
Tables
Hier kann man weitere Quelltabellen angeben und verknüpfen.
Weitere Quelltabellen
Syntax
Tablename,Alias,Join;
Bsp.: Umsatzerloese_PLAN,Umsatz,HAS;
Join types:
HAS (or INNER): is equivalent to an INNER JOIN in SQL. Using HAS or INNER, all rows from Table A and Table B which have the same columnelements in the join conditition are brought together.
OPT (or LEFT): is equivalent to a LEFT JOIN in SQL. Using OPT or LEFT, all rows from Table A and Table B which have the same columnelements in the join conditition and all elements from the first table are brought together.
OUT (or OUTER): is equivalent to a FULL OUTER JOIN in SQL. Using OUT or OUTER, all rows from Table A and Table B which have the same columnelements in the join conditition and all elements from both tables which have no same elements are brought together.
Alias: the alias defined for that table can be used all over the formula. E.g. you can use instead of [Umsatzerloese_PLAN.Unternehmen_1.COPSYS_ID] [Umsatz.Unternehmen_1.COPSYS_ID].
Add joins
You can define how the tables are joined together. Join table definies for which table the join should be used. Only entries which are defined in the additional sourcetables are valid. You can use the tablename or the alias. If a table is defined more than once as additional sourcetable you must use the alias.
The following operators are supported for joins:
- =
- <
- >
- <=
- >=
Subqueries in joins
Subqueries give you the possibility to aggregate and filter the data of tables and use that data for your calculations. You only can use fields and dimensions of the table by alias which are included in the subquery.
Syntax:
Tablename,Alias([Sumfield1]$[Sumfield2]#Filter1$Filter2#[Groupfield1]$[Groupfield2]),Join;
The areas of the sumfields, filter and groups have to be separated by #. Within the sumfields/filter/groups $ is the separator between the fields/expressions.
E.g.:
TableA,A([a]##[Company_1]),INNER;
Makes a sum von field [a] based on a grouping on companies.
TableA,A([a]$[b]##[Company_1]$[Period_1]),INNER;
Makes a sum on the fields [a] and [b] based on a grouping on companies and period.
You can use expressions in subqueries. Using a expression you have to define an alias for the new column you create. E.g. '([a]*[b]) as [newcol]'. To use the result of that expression you have to use the name [newcol]. Also filter can be defined only on expressions (e.g. '[a]+1=iif([b]=1,0,2)'). The datatype of the whole expression must have a boolean result.
Additional also filter createrias can be a expression. E.g. '[a] !me+under fkt_TimeDimX(@Planstart@)'. That expression must not have row depending expressions (columns).
| Hint FKT_SQLQUERY, FKT_AGGREGAT and FKT_GETRECORD are not allowed. |
| Hint Branckets for datacolumns in subqueries are mandatory. |
