Calculation Scripts:- Session 2

We want to implement a simple logic using a calculation script; We want to calculate Commission on Sales which is the product of Commission % Entered by end user and Sales.

"Commission on Sales" = "Commission %" * "Sales";

From the hierarchy perspective we need to have member called "Commission %" where user can enter data and a member say "Commission on Sales" where we can store the result of the calculation.

Let make following hierarchy:

Create a calculation script , validate it. To validate click on green check icon if there is no error you will get a confirmation message, lets save this calc as MySec.csc.

 

This time lets Clear the data for MyDB by a different way: On the left hand pane in EAS 

console, right click on the database and select Clear-> All Data.

You will get a confirmation screen asking for confirm clear because this cannot be undone. 

Click yes on that. If its successful you will get a message saying clear Database is successful. 

Now if you will retrieve the data, it will be #Missing by default, in this case we need not to use of 

Lock and Send #Missing to clear data. Lets enter the value for Sales and Commission %, Lock 

the database and Send the values. 

Now lets calculate our database using our MySec.csc calculation Script:

once the calculation is finished, Hyperion->Essbase->Retrieve, you will get the desired result.

Lets move a step ahead, lets drill down sales a bit. Assume that Sales is the aggregation of 

"GrossSales", "ThirdParty", "IC". Now we have to create these three members below Sales 

and here sales is the sum of these three; we will create three members under sales with 

aggregation property as "+".

Now say we have to calculate "Commission on Sales" again. Clear All data, now this time we 

will enter values for "GrossSales", "ThirdParty", "IC" and "Commission %" to get the value of 

"Commission on sale". Still is the same formula:

"Commission on Sales" = "Commisson %" * "Sales";

Here #Missing with red color are input values and rest are values to be calculated. Pass the 

values by Locking and sending the data to Essbase and Re run our Calculation MySec.csc and 

check the results. Now when you try to retrieve the data here there will be no change but why? 

The reason is for calculating "Commission on Sales" we need to calculate "Sales" first, by 

placing the members in the hierarchy doesn't calculates the value automatically. Now the big 

question arise how can we calculate sales, do we need to write some formula in calculation 

script again... the answer is no, all we need to do to calculate sales is to add one more line in 

the calculation script i.e. 

"Sales";

When we write "Sales"; Essbase will perform the outline calculation for the member sales, here is our modified scipt:

Save the calculation script and come to Spread Sheet and re run the calculation  Below will be the result after Essbase retrieve.

What we learned today:

1. How to clear our database from EAS,

2. Dependents need to be calculated first,

3. How to calculate a member based on the hierarchy or formula.

Cheers..!!!
Rahul