You can define formulas in SAP BW Screens and software which define how the transaction data is to be processed in order to generate plan data. Formula functions enable you to use extended mathematical functions to calculate plan data.In addition to different calculation functions, which you can use for value assignment in formulas, there is also the possibility to model complex flow structures with the formula language FOX (FOrmula eXtensions).
FOX Formulas - Example
How to specify a record in a formula:
{field to be changed 1, field to be changed 2, …, field t.b.c. n}.
If the key figure is a field to be changed then each key figure in a record can be addressed individually. If the key figure is not in the fields to be changed then all key figures of the data record will be changed according to the formula.We use the above Example: for each product we copy data from the current year (2004) to the next year (2005):
Field(s) to be changed: 0FISCYEAR
Formula: {2004} = {2005}.
You do not have to care about the product because of the subsets ("automatic FOREACH")!Do not forget the "." at the end of each statement
Some Elements of FOX Formulas
The planning function of the type formula calculation offers you a simple programming language for the manipulation of transaction data. It includes elements, which can be found in many macro languages for business applications.In addition to the formula calculation, there is also the possibility to create planning functions of the type Exit, in order to manipulate transaction data using a programming language. Weigh up the following points against each other in order to make a decision for one or the other function type.
Planning functions of the type formula are easily learned, and only require a small amount of training. Ideally, we imagine an end used in Controlling, who has already mastered an algorithmic programming language or a macro language, and can solve most of the problems with the formula language.Planning functions of the type Exit must always then be written, when you require features, which are yet not available in another way. Example: for the calculation of costs, customer tables must be accessed. Up to now, there is no feature to access formulas in any tables.
Generally, every Exit function module achieves a higher performance level than the formula calculation. The reason for this is mainly that every operand and every result from somewhat complex formulas are read separately from an internal table.In a self-written program, you would of course optimize this access. This performance point of view is a decisive criterion for larger quantities of data.
FOX Formulas – Foreach Statement
You have to define a local variable:
DATA year TYPE 0fiscyear.
Use this variable in the Foreach Statement:
FOREACH year.
{year} = {2004} * 2.
ENDFOR.
The values for YEAR are taken from the records in the selection of the planning package.
Assume we have data records for year 2005 and 2006.
First loop: YEAR is replaced with 2005, system calculates {2005} = {2004} * 2.
Second loop: YEAR is replaced with 2006, system calculates {2006} = {2004} * 2.
FOX Formulas – Nested Loops
Use nested FOREACH statements only if necessary.Use FOREACH VAR1,VAR2 wherever it is possible.
Variables In Formulas
In the example below we see use of the "Global" Variable being used in a FOX formula (PERCUR).This is used in the planning area to represent "Current Period". In variables set-up within the planning area the current period is set once per period to the current period value.
In addition the formula must work with "Local Variables". These are declared at the beginning of the formula: ZCURPER is used to represent current period, and ZFCPER the forecast period. There is also a counter, ZCOUNTER.
Within the planning function the "Fields to be Changed" are respectively key figure name, fiscal year/period and version.Very simply the formula will calculate forecast sales quantity (0COPASLQTY) for the next five periods. ZFCPER is incremented (using the concept of a local time variable TMVL – see later unit for more information about this) for each of five times, and in each case the quantity for the respective forecast period is set to the actual quantity (version ACT) of the current period (using local variable ZCURPER).
The key point to note as far as syntax is concerned within the formula is that the local variable (ZCURPER) is set to the value held against the (global) variable within variable settings in the Planning Area.
FOX always uses INTERNAL format for characteristic values.For example, accounts have to be entered with leading zeros.
Use F4 help for entering { }-operands.
If–statements versus conditions - when to use which:
If–statements versus conditions - when to use which:
In conditions you can use any variable – also variables with ranges, several values or hierarchy nodes.When using if-statements you only have one parameter group and thus only one formula. Therefore the formula is easier to understand.
Use naming conventions for local FOX variables, e.g.:
- CHA_… for characteristics,
- KYF_… for key figures,
- VAR_… for global (BPS) variables,
- INT_… for integer numbers.
Do not use "ABAP" logic like PERIOD = PERIOD + 1. for calculating in time characteristics. Use the TMVL function instead PERIOD = TMVL(PERIOD, 1).
If you need the value of a characteristic that is not in the ‘fields to be changed‘ use the function OBJV().If there is no data in a subset, the FOREACH does nothing (empty loop) – be careful when creating new records using a FOREACH statement.No loop over reference data possible – reference data can only be used on "right side" of formulas.
No loop over master data possible – FOREACH statement only goes through existing records .
Performance:
If you use ATRV for a characteristic and the formula requires reference data, then the system ignores any restrictions for this characteristic when reading the reference data form the database.
Especially during the test phase, sending additional messages in the FOX formulas is very useful. Please refer to the online help for an explanation of the syntax of the MESSAGE statement.
Tip: Message number 001 of message class UPF is generic and can be used to include up to four parameters in the message. For example: MESSAGE E001(UPF) WITH 'Please enter data for period' FISCPER 'and year' FISCYEAR.
Since the system generates complex ABAP coding for each FOX formula, it does not make sense to use the standard ABAP debugger (/h) for debugging FOX formulas. Also including a BREAK-POINT in a FOX formula, is only relevant for experts (for example SAP Support).
Related Posts
No comments :
Post a Comment