Calculation Scripts:- Session 5

Welcome to Session 5, I hope up till now you must be familiar with calc scripts, its time to move 

to the most important and interesting part, we will be discussing IF and FIX in this session.

IF: IF is a conditional statement that performs a check before executing a command or a set of 

commands. The condition can either return TRUE or FALSE, If the result is TRUE then first part 

is executed if the result of condition if FALSE then ELSE part is executed. We have to include 

IF between a member block. Here is an ex: lets change our logic of calculating "Commission 

on Sales", the new logic is: If the sales is greater then 30,000 then Commission is 10%  of 

"Sales"else its 8%.

 Above is our new Hierarchy

Here I have created a member block of "Commission on Sales", Lets save this Calc Script as 

MyFifth.csc, enter the test data and descendants of Sales, lock and send the data to Essbase 

and run MyFifth.csc.

Lets complicate our logic:

Sales > 30,000 then Commission is 10%  of Sales

Sales < 30,000 and > 20,000 then Commission is 8%  of Sales

Sales < 20,000 then Commission is 6.5%  of Sales

After modifying our script and executing the script after entering test data.

Following operators can be used to make conditions:

>               Is greater than

>=             Is greater than or equal to

<               Is less than

<=             Is less than or equal to

= =            Is equal to

< > or !=   Is not equal to


We can compound our conditional statements as well, let implement a logic:

 
Sales > 30,000 AND COGS < 10,000 then Commission is 10%  of Sales else its 5%

One of the ways to implement it is:

Result of above script on below data.

Following logical operators can be used to make compound conditions:

AND: Result is TRUE if both conditions are TRUE else the result is FALSE.

OR :  Result is TRUE if any of the condition is TRUE else the result is FALSE.

NOT:  Result is TRUE if condition is FALSE and Result is FALSE if condition is TRUE.

All the Boolean function can be used create a condition, list of Boolean functions:

http://download.oracle.com/docs/cd/E17236_01/epm.1112/esb_tech_ref/func_booleantype.html

More on this later.

FIX: FIX can be used when you want to execute commands on a portion or a part of database. 

Writing calc scripts is a bit different from writing programs in programming languages like C, 

C++, Java or other scripting languages. Here the commands are executed on data blocks. 

Data blocks come into memory and a set of operations is performed. For ex: if you have Fixed 

on NA1 and wrote few commands under that fix then these set of commands will be executed 

on NA1 rather then the complete database. These statements will make more sense when we will 

deeply understand the concept of blocks in later sessions. Till let just remember a statement, as 

long as it possible try to FIX sparse dimension and write your IF for dense dimension 

members. If you remember we have three dimensions in outline out if which Account and 

Period are dense and Entity is Sparse. Lets try to implement below logic in our outline. For 

North American Entities lets assume that Commission % is 12% and for APAC its 10%.

Lock and send the values and run of calc script, I saved it MySixth.csc

Here is the result for NA descendants the values are calculated at the rate of 12% and APAC's 

descendants were calculated at the rate of 10%



All commands between FIX and ENDFIX are executed as a calculation unit. Here the 

command " "Commission on Sales" = "Sales" * .12;" calculated as a calculation unit once this 

calculation is finished the control goes to next Fix. We can use member set functions with in FIX 

to select a portion on database ex: if we want all the level 0 member of Entity we can use 

FIX(@LEVMBRS("Enitty", 0)) as per our outline @LEVMBRS("Entity", 0) will return NA1, NA2, 

APAC1, APAC2. For list of member set functions have a look at below link:

 http://download.oracle.com/docs/cd/E17236_01/epm.1112/esb_tech_ref/func_memsettype.html

If in case we want to perform a CALC DIM only on NA1 then we can write:

FIX("NA1" )

 CALC DIM("Account", "Period" );

ENDFIX


Cheers..!!!
Rahul S.