Operands

The following syntax is use to access data records

– {key figure, field to be changed 1, field to be changed 2, …, field t.b.c. n}

To read data use the operands on the right side of an equation

To change data use the operands on the left side of an equation

You can read and change only records that are included in the filter

You can use the input help for

entering operands into the editor

Note: In contrast to BW-BPS, for BI-Integrated Planning "key figure"

Is always a field to be changed

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 25

Example

Business Scenario

– Copy data for each product from the current year to the next year

Fields to be changed

– Key Figure, 0FISCYEAR

Formula

– {0AMOUNT, 2008} = {0AMOUNT, 2007}.

Explanation

Amount for year 2007 is read

Amount for year 2008 is changed

You do not have to care about the product because of the subsets.

There's an automatic FOREACH product done by the system.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 26

New Feature

Reference Characteristics in Operands

Business Scenario

– We are calculating the revenue from the planned quantities and the prices.

Revenue and quantity are stored in the plan version 1 whereas the prices

come from the actual version 0.

Fields to be changed

– Key Figure

Formula

– {0REVENUE} = {0QUANTITY} * {0PRICE | 0VERSION = 0}.

Explanation

Version does not have to be in the fields to be changed but can be used

as reference characteristic

The filter the selection is set to version 1

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 27

New Feature

Input Help for Operands with Reference Fields

You can pick additional

reference characteristics

Result of operand in editor:

{ SEMPS_003, Apple Juice | Z_PRODLIN = Juice }

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 28

Local variables are…

used to store temporary results

defined using the DATA statement

always referencing a data type

usable only in a given formula

You can use the input help for

Variable definitions into the editor

Global BI variables are…

used to integrate with analytical engine

defined in Planning Modeler or BEx Query Designer

referencing characteristic or numeric values

usable system-wide

accessed using VARV and VARC functions

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 29

General Looping

Variable number of loops

– DO... EXIT… ENDDO

Predefined number of loops

– DO n TIMES... ENDDO

Looping over Certain Values

Plan data values (only existing records)

– FOREACH <Variable>... ENDFOR

Reference data values

– FOREACH <Variable> IN REFDATA... ENDFOR

Selection criteria (filter)

– FOREACH <Variable> IN SELECTION... ENDFOR

BI Variable

– FOREACH <Variable> IN VARIABLE... ENDFOR

New Feature

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 30

Example

Fields to be changed

– Key Figure, 0FISCYEAR

Formula

DATA year TYPE 0FISCYEAR.

FOREACH year.

{0AMOUNT, year} = {0AMOUNT, 2007} * 1.05.

ENDFOR.

Explanation

The values for YEAR are taken from the records in the selection

Assume we have data records for year 2008 and 2009.

– First loop: YEAR is replaced with 2008 and system calculates

{0AMOUNT, 2008} = {0AMOUNT, 2007} * 1.05.

– Second loop: YEAR is replaced with 2009 and system calculates

{0AMOUNT, 2009} = {0AMOUNT, 2007} * 1.05.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 31

Program Flow Based on Conditions

Single condition

– IF… ENDIF.

Single condition and others

– IF… ELSE… ENDIF.

Multiple conditions

– IF… ELSEIF… ELSE… ENDIF.

Example

Fields to be changed

– Key Figure, 0FISCYEAR

Formula

IF {0AMOUNT, 2007} > 1000.

{0AMOUNT, 2008} = {0AMOUNT, 2007} * 1.05.

ELSE.

{0AMOUNT, 2008} = {0AMOUNT, 2007} * 1.10.

ENDIF.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 32

Program Elements

Powerful macro language

Mathematical function library

Various financial calculations

Access to master data attributes

Access to BI variables

String operations

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 33

New Feature

String Operations

Get part of a value

– SUBSTR

Concatenate values

– CONCAT

Replace a part of a value with another one

– REPLACE

Determine length of a value

– STRLEN

Compare values

– CP, CO, CA, CS (contains pattern, only, any, string)

Financial Functions

Currency Translation

– CURC

Rounding

– ROUND

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 34

New Feature

You can call selected ABAP function modules

within a formula

Enter the name of the function module

in the table RSPLF_FDIR

Use ‘CALL FUNCTION…’ in the formula

You can use importing, exporting and

changing parameters

Restriction on types for parameters

– F, I, D, or STRING

– Types of characteristics and attributes

– No structures or tables

If the ABAP function module needs to raise an exception you have to

use the construct MESSAGE...RAISING

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 35

Designing Planning Functions

Configuring Formulas

Performance Tuning of Formulas

The idea is to reduce the complexity of the formula, without

changing the result of the calculation.

Less lines of FOX leads to less ABAP coding that the system has to

process and therefore faster execution times.

There are two main targets for optimization:

1. Formula Operands

2. Foreach Loops

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 37

Fields to be changed:

{ Key figure, Cost Element, Version, Planning Item, InfoProvider }

Parameter group:

* Cash Discount = (Base Revenue - Price Adjustment) * 2%

DATA C TYPE 0COSTELMNT.

DATA V TYPE 0VERSION.

DATA P TYPE ZPLANITEM.

FOREACH C,V,P.

{0AMOUNT,0000415001,V,CASHDISC,PA000004} =

( {0AMOUNT,0000400001,V,CUSTSALES,PA000004}

-

{0AMOUNT,0000410001,V,PRICADJDIS,PA000004} +

{0AMOUNT,0000405002,V,PRICADJBKT,PA000004} ) * 0.02.

ENDFOR.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 38

Find all characteristics that are restricted to only one value in the

formula. This means that the characteristic is NOT being changed

and therefore should be remove from the "fields to be changed".

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 39

Optimization 1: "Fields to be changed" that are not changed

Remove from "Fields to be changed": Key figure = 0AMOUNT

Remove from "Fields to be changed": Version = V

Remove from "Fields to be changed": InfoProvider = 'PA000004'

Fields to be changed:

{ Cost Element, Planning Item }

Parameter group:

* Cash Discount = (Base Revenue - Price Adjustment) * 2%

DATA C TYPE 0COSTELMNT.

DATA P TYPE ZPLANITEM.

FOREACH C,P.

{0000415001,CASHDISC} =

( {0000400001,CUSTSALES}

-

{0000410001,PRICADJDIS} +

{0000405002,PRICADJBKT} ) * 0.02.

ENDFOR.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 40

Determine the selections i.e. the characteristic combinations that

are changed by the formula and compare it against the filter.

There can be several cases:

Perfect match (best case)

Filter selection is equal to the formula selection. The system will read

only data that is actually being changed.

Filter selection is bigger (worst case)

The system will read more data than required by the function.

Filter selection is smaller (error)

The formula potentially generates records that are not part of the filter,

which could lead to an error message.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 41

Optimization 2 A: "Formula selections (excl. reference data)" that are different than

filter

Restrict level/filter to formula selection: Key figure

Level/Filter:

= 0AMOUNT, ZPRICEADJ, ZUNCOLLAR

Formula:

= 0AMOUNT

Level contains more key

figures than necessary

Restrict level/filter to formula selection: Cost Element

Level/Filter:

No restriction

Formula:

= '0000415001'

Level is not restricted on Cost

Element but formula changes

only one Cost Element

Optimization 2 B: "Formula selections (incl. reference data)" that are different than

filter

Optimization works same way as 2A but tries to combine two database selections.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 42

Optimization 3 A:

If the condition is equal to the filter restriction, then the condition

can be removed.

Optimization 3 B:

If there's only one condition it can be merged with the filter

selection.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 43

It is not necessary to write to the same result operand several times.

It's also not necessary to read the same reference operand several

times. Instead, the operands should be stored temporarily using

local variables (DATA).

Optimization 4 A:

Based on result data only

Optimization 4 B:

Based on reference data only

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 44

Fields to be changed:

{ Material, Planning Item }

Parameter group (BEFORE):

DATA M TYPE 0MATERIAL.

{M,TOTALSALES} = 0.

FOREACH M.

{M,TOTALSALES} = {M,TOTALSALES} + {M,CUSTSALES}.

ENDFOR.

If there are 1,000 materials,

then TOTALSALES has to be

read and updated 1,000 times

Parameter group (AFTER):

DATA M TYPE 0MATERIAL.

DATA TOTAL TYPE F.

TOTAL = 0.

FOREACH M.

TOTAL = TOTAL + {M,CUSTSALES}.

ENDFOR.

{M,TOTALSALES} = TOTAL.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 45

There's only one update of

TOTALSALES independent of

the number of materials

If an operand is used in an foreach loop but does not depend on any

foreach variable, then the operand is being processed too often and

therefore should be moved before (reference data) or after (result

data) the foreach loop.

Optimization 5 A:

Based on result data only

Optimization 5 B:

Based on reference data only

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 46

Fields to be changed:

{ Cost Element, Planning Item }

Parameter group (BEFORE):

* Cash Discount = (Base Revenue - Price Adjustment) * 2%

DATA C TYPE 0COSTELMNT.

DATA P TYPE ZPLANITEM.

FOREACH C,P.

{0000415001,CASHDISC} =

( {0000400001,CUSTSALES}

-

CASHDISC is updated inside

the loop although it does not

depend on C or P.

{0000410001,PRICADJDIS} +

{0000405002,PRICADJBKT} ) * 0.02.

ENDFOR.

The other operands are read

inside the loop although they

don't depend on C or P.

Parameter group (AFTER):

{0000415001,CASHDISC} =

( {0000400001,CUSTSALES}

-

{0000410001,PRICADJDIS} +

{0000405002,PRICADJBKT} ) * 0.02.

Ultimately the complete

Foreach loop can be

removed.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 47

If the formulas is using data of only one InfoProvider but is defined

on a MultiProvider, then the complete formula should be moved to

the single base InfoProvider.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 48

Use nested FOREACH statements only if necessary.

Use FOREACH VAR1,VAR2, ..., VARn wherever it is possible.

Example:

Records

0PRODUCT

0001

0002

FOREACH product.

FOREACH year.

...

ENDFOR.

ENDFOR.

0PRODUCT

0001

0001

0002

0002

0FISCYEAR

2010

2011

2010

2011

0AMOUNT

10

0

0

20

0PRODUCT

0001

0002

0FISCYEAR

2010

2011

0AMOUNT

10

20

FOREACH product, year.

ENDFOR.

0FISCYEAR

2010

2011

0AMOUNT

10

20

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 49

If Statements versus Conditions - when to use which:

When the logic depends on variables with ranges, several values or

hierarchy nodes, then use conditions.

When using if-statements you only have one planning function and thus

only one formula. Therefore the formula is easier to understand.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 50

Use naming conventions for local FOX variables, e.g.:

CHA_… for characteristics,

KYF_… for key figure values,

VAR_… for global BI variables,

ATR_... for attributes,

INT_… for integer numbers,

DAT_... for dates,

TIM_... for times.

Add comments to the formula.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 51

Fields to be changed:

{ Key figure name, Posting period, Version }

* Posting period and total amount

DATA CHA_0FISCPER3 TYPE 0FISCPER3.

DATA KYF_TOTAL

TYPE F.

* Planning version (via variable)

DATA VAR_ZPLNVER

TYPE 0VERSION.

* Actual period (attribute of plan version)

DATA ATR_ZACTPER

TYPE 0FISCPER3.

* Get actual period from plan version (via variable)

VAR_ZPLNVER = VARV( 'ZPLNVER' ).

ATR_ZACTPER = ATRV( 'ZACTPER', VAR_ZPLNVER ).

* Get total to be distributed greater than actual period

KYF_TOTAL = 0.

FOREACH CHA_0FISCPER3.

IF CHA_0FISCPER3 > ATR_ZACTPER.

KYF_TOTAL = KYF_TOTAL + {0AMOUNT,CHA_0FISCPER3,VAR_ZPLNVER}.

ENDIF.

ENDFOR.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 52

If you set a local variable for a characteristic using TMVL, ATRV, or

ATRVT and use this variable in a reference data operand (right side

of formula), then the system ignores any restrictions for this

characteristic when reading the reference data from the database.

This can lead to performance problems. Check the BI statistics for

long database selection for reference data.

Try to use the new syntax for reference data in operands:

{key figure, field 1, field 2, …, field n | ref. field 1 = value 1, ref. field 2 =

value 2, …, ref. field m = value m}

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 53

Fields to be changed:

{ Company, Version }

* Company

DATA CHA_0COMPANY TYPE 0COMPANY.

* Source version (attribute of company)

DATA ATR_SVERSION TYPE SVERSION.

* Target version (variable)

DATA VAR_TVERSION TYPE 0VERSION.

Business Case: Copy data

into a target version and each

company potentially has a

different source version.

* Get target version from variable

VAR_TVERSION = VARV( 'TVERSION' ).

* Copy data from source to target version

FOREACH CHA_0COMPANY.

Source version is set via attribute

(ATRV) and then used in operand

to read reference data.

System will read ALL

versions since it can't know

which ones would be required!

ATR_SVERSION = ATRV( 'SVERSION', CHA_0COMPANY ).

{CHA_0COMPANY,VAR_TVERSION} = {CHA_0COMPANY,ATR_SVERSION} .

ENDFOR.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 54

Save the formula before testing! Otherwise you loose your work if

the program goes into an endless loop and you have to stop the

program.

To stop a modus after having programmed an "endless loop" (for

example a do loop without exit statement) go to transaction SM50

and choose Program/ModeProgramCancel.

SAP AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 55