Formula
Use
The planning function type Formula provides you with a simple programming language for manipulating transaction data.
The same formulas are valid in BI Integrated Planning as were valid in BW-BPS. Note, however, that in BI Integrated Planning the name of the key figure is always part of the operand. For further information about formula calculation in BW-BPS, see the BW-BPS documentation under Formula.
Features
As in many macro languages for business applications, the following components are available:
● Variable concept including DATA statement for Data Declarations and help variables
● Callable Function Modules
● Comment function
You can use line notes, the starter mark (at the start of the line) is '*'.
Internal Data Objects
A formula is not normally only executed once, but multiple times, that is, exactly once for each data object: If you execute a planning function, first select a filter, the filter describes the quantity of transaction data. The amount of transaction data is divided into smaller data objects. If a formula needs reference data, then these data objects are also formed directly from this reference data.
Note: If no data objects can be formed, a formula is not executed.
The only difference between the data objects is in the characteristic values of the characteristics to be changed. The values of the other characteristics are the same. If no characteristics to be changed are selected, the formula is executed once for each transaction data record. Each key figure value in the data object can be addressed uniquely by entering the characteristic values of the characteristic to be changed and the key figure name and can be changed using functions.
To better understand how to work with a formula, you can define a query in which all the characteristics to be changed are in the lead columns and the remaining characteristics are in the header. The formula is then processed for all combinations. You possibly have to extend the query by columns with reference data.
We recommend that you limit the values in the filter relating to the characteristics in the formula that are not to be changed as much as possible so that few data objects are created.
Reference Data
You have two possibilities for accessing reference data:
...
1. You make the field because of which the reference data of the data of the active planning filter differ as the field to be changed, for example, Version (0VERSION). You then write the operands as follows:
{ REVENUE, 002 } = { REVENUE, 001 }.
If there is no data available in version 002, then the internal data objects are made from the data from version 002 and also from 001. When executing the formula, data is created in version 002.
This syntax is intended for formulas that are to create new data from reference data.
2. You address reference data explicitly. To do so, you enter the name of the characteristic and the value in the operand.
{ REVENUE } = { REVENUE | 0VERSION = 001 }.
The formula will only run for records in version 002 as it cannot be determined from the formula how the records from version 001 are to be transformed into records from version 002. If there are no records available, the formula is not run. No records are created.
The syntax is intended for formulas that need reference data to evaluate existing data.
In the following example the data from version 002 is in the active planning filter. The formula is run for each record of the active filter. As there are no fields to be changed, the data objects consist of one record.
Transaction Data is as Follows:
0VERSION |
0COSTCENTER |
REVENUE |
001 |
4711 |
3 |
001 |
0815 |
2 |
002 |
4711 |
9 |
Result of: { REVENUE, 002 } = { REVENUE, 001 }.
0VERSION |
0COSTCENTER |
REVENUE |
001 |
4711 |
3 |
001 |
0815 |
2 |
002 |
4711 |
3 |
001 |
0815 |
2 |
Two data objects were formed:
0COSTCENTER |
4711 |
0815 |
Result of: { REVENUE } = { REVENUE | 0VERSION = 001 }.
0VERSION |
0COSTCENTER |
REVENUE |
001 |
4711 |
3 |
001 |
0815 |
2 |
002 |
4711 |
3 |
Available data objects:
0VERSION |
0COSTCENTER |
002 |
4711 |
Before executing the parameter group it is decided which reference data is required based on the formula. The original selection is enhanced for selecting reference data. We assume the planning filter contains only data from version 002: Based on the formula rows { REVENUE, 002 } = { REVENUE, 001 }. the selection is enhanced by the value version 001. Unfortunately it is not always possible to decide which reference data is required before starting a planning function of the type Formula, the selection is then deleted once the results of the functions for addressing reference data are used. Cases where this can occur:
● Using the TMVL function to change the value of a time characteristic
● Using the ATRV or ATRVT functions to read attribute values
● Calling function modules
DATA FISCPER TYPE 0FISCPER.
DATA ACTPER TYPE 0FISCPER.
ACTPER = VARV( CURPERIO ).
FISCPER = ACTPER.
FISCPER = TMVL( FISCPER , -12 ).
{ ERLOES, ACTPER } = { ERLOES, FISCPER }.
In the example, first the formula variable ACTPER is filled from the global variable ACTPERIO. In the second step this value is assigned to the FISCPER variable. In a third step, the value of ACTPER is reduced by 12 using the TMVL function. Finally, the value of the REVENUE key figure in the FISCPER period is assigned to the value of the REVENUE key figure in the ACTPER period.
Formula or Own Planning Function Types?
Aside from using the function type Formula, you can also create your own planning function types to manipulate transaction data using a programming language. Weigh up the following points to decide for one function type or the other:
● Planning functions of type Formula are easy to learn and do not require a high level of effort. Ideally an end-user in controlling who can already use an algorithmic programming language or a macro language and can solve the majority of problems with the formula language.
● Own planning functions must therefore always be written when you require features that were not previously available. Example: For calculating costs, you must access customer-own tables. Until now there is no feature for accessing formulas in any table.
● It is possible to call function modules in formulas. This means part of the logic can be is function modules and part in formula logic.
● Generally, every own planning function type can be implemented to be more high-performance than the formula calculation. The main reason for this is that each operand and every result is read separately by more complex formulas from an internal table. These accesses would of course be optimized in a self-written program. This performance perspective is a major deciding factor for larger amounts of data.
Expressions
You can mention the following components in expressions:
● Constants consist of numbers and a decimal point.
● Variables you have declared with the DATA statement. (See also Data Declaration).
● Operands you get using the input help.
○ If characteristic values appear in operands that are not addressed using the key figure name, operands have to be enclosed in curly brackets { and } so as to avoid operands being confused with constants. If no characteristic is to be changed, you can add the key figure name as an operand without parentheses. The system shows the valid syntax and, in particular, the correct order of characteristics above the editor. Using the input help you can easily combine valid operands.
○ If the characteristic values in operands contain blanks or symbols such as +, -, *, the characteristic values have to be enclosed in apostrophes.
○ There is an internal and external format for each characteristic value. For example, date values are shown externally as 3.12.1963 and internally as 19631203. Always use the internal format in formulas. You can also use variables instead of characteristic values in operands.
○ If key figure name is the only characteristic to be changed and you have defined a variable of type KEYFIGURE_NAME to be able to change key figures, then you also have to enclose this in braces. The following statement sets all key figures to the value 1. In this way, the FOREACH construct fills the variable KEYFIG with all the key figure names of the key figures available in the level.
DATA KEYFIG TYPE KEYFIGURE_NAME.
FOREACH KEYFIG.
{ KEYFIG } = 1.
ENDFOR.
● Operators
● Functions without, with one, or multiple operands
You can find a detailed list and explanation of the individual operators under Mathematical Operators and Functions.
More Information:
Examples for Formula Applications