I am sorry for the late posting of this series but I had to take my vacation and needed to get some training about HANA :smile:
Let's start to learn how to caluate and write some data using the script logic.
Again, the script logic consists of 3 parts;
Scoping,
Calculationand
Writing.
1. Basic concept of Writing and *REC statement
As we saw in my first posting of this series, *REC statement is used for writing data.
You need to keep in mind that *REC will create records based on the scoped records.
For example, if your scoped record is same as below.
<Scoped record>
EXTSALES, 2011.JAN, ACTUAL, USA, 10000
and your *REC statement is below.
*REC (FACTOR = 0.9, ACCOUNT="DISCOUNTED_EXTSALES", CATEGORY="BUDGET")
Then your generated record will be
<Generated record>
DISCOUNTED_EXTSALES, 2011.JAN, BUDGET, USA, 9000
What if your scoped record is not a single one but multiple record?
<Scoped record>
EXTSALES, 2011.JAN, ACTUAL, USA, 10000
EXTSALES, 2011.JAN, ACTUAL, KOREA, 3000
EXTSALES, 2011.JAN, ACTUAL, CANADA, 5000
Then your generated records will be
<Generated record>
DISCOUNTED_EXTSALES, 2011.JAN, BUDGET, USA, 9000
DISCOUNTED_EXTSALES, 2011.JAN, BUDGET, KOREA, 2700
DISCOUNTED_EXTSALES, 2011.JAN, BUDGET, CANADA, 4500
As you can see, we changed Account value, Category value and its signeddata vale (or measure value) using *REC statement.
The other dimension that is not specified in the *REC statement will be same as scoped data so 2011.JAN and each country (entity) doesn't
be changed.
2. Grammar of *REC statement.
Here is the grammar of *REC statement. You can use FACTOR or EXPRESSION for various calculations for signeddata vale (or measure value).
And specify dimension name and member to change its value.
*REC[([FACTOR|EXPRESSION={Expression}[,{dim1}={member},{dim2}=?)]
3. What is the difference between FACTOR and EXPRESSION?
The FACTOR is a factor(multiply) by which the retrieved amount is to be multiplied.
Here is an example.
<Scoped record>
EXTSALES, 2011.JAN, ACTUAL, 10000
*REC(FACTOR=6/2)
<Generated record>
EXTSALES, 2011.JAN, ACTUAL, 30000
What if you want to add or divide? then you should use EXPRESSION.
The EXPRESSION is any formula that will result in the new value to post.
The formula can include regular arithmetic operators, fixed values and the Script logic keyword %VALUE%
this is representing the original retrieved value of the scoped record.
Here is an example.
<Scoped record>
EXTSALES, 2011.JAN, ACTUAL, 10000
*REC(EXPRESSION=%VALUE% + 5000)
<Generated record>
EXTSALES, 2011.JAN, ACTUAL, 15000
Now we got the basic things of *REC statement
but you may ask below questions. "There are some scoped data and I need to do different calculations based on each specific dimension member."
"I need to copy a value to multiple destinations!"
"How can I get the value from the other application?"
"I want to use some value from other records to calculate the result."
"Can I use a property value to calculate the result?"
The script logic can handle above requirements.
I will explain first question in this post and will do others in the next post.
"There are some scoped data and I need to do some calculations based on each specific dimension member."
Yes. That's why *you MUST use *REC statement with *WHEN ~ *IS ~ *ELSE ~ *ENDWHEN statement.
Let's assume you want to create forecast values of salary and put it into the forecast category based on the country's actual salary values of January, 2011.
We need to increase 10% for US, 5% for Canada and 3% for other countries.
Let's assume ENTITY dimension has country information.
To do this, you need to scope first.
*XDIM_MEMBERSET ACCT = SALARY
*XDIM_MEMBERSET TIME = 2011.JAN
*XDIM_MEMBERSET CATEGORY = ACTUAL
Now you need to write the *REC statements
*REC(FACTOR = 1.1, CATEGORY=“FORECAST“) // 10%
*REC(FACTOR = 1.05, CATEGORY=“FORECAST“) // 5%
*REC(FACTOR = 1.03, CATEGORY=“FORECAST“) // 3%
Finally, you should specify a condition of each *REC statement.
For doing this, you MUST use *WHEN ~ *IS ~ *ELSE ~ ENDWHEN statement.
First, Write down *WHEN and *ENDWHEN outside of the *REC statement
*WHEN
*REC(FACTOR = 1.1, CATEGORY=“FORECAST“) // 10%
*REC(FACTOR = 1.05, CATEGORY=“FORECAST“) // 5%
*REC(FACTOR = 1.03, CATEGORY=“FORECAST“) // 3%
NOTE : You don't need to use the indentation of code in the script logic
but I would like to recommend using it for better readability.
Second, write a dimension name that you want to compare next to *WHEN.
In this example, it will be ENTITY dimension.
*WHEN ENTITY
*REC(FACTOR = 1.1, CATEGORY=“FORECAST“) // 10%
*REC(FACTOR = 1.05, CATEGORY=“FORECAST“) // 5%
*REC(FACTOR = 1.03, CATEGORY=“FORECAST“) // 3%
*ENDWHEN
Third, put *IS statement on top of each *REC statement and *ELSE statement on top of the last *REC statement.
We need two *IS statements and *ELSE statement because there are two conditions and others will be calculated as one condition.
*WHEN ENTITY
*IS
*REC(FACTOR = 1.1, CATEGORY=“FORECAST“) // 10%
*IS
*REC(FACTOR = 1.05, CATEGORY=“FORECAST“) // 5%
*ELSE
*REC(FACTOR = 1.03, CATEGORY=“FORECAST“) // 3%
ENDWHEN
Fourth, put each condition value next to *IS
*WHEN ENTITY
***IS USA
*REC(FACTOR = 1.1, CATEGORY=“FORECAST“) // 10%
***IS CANADA
*REC(FACTOR = 1.05, CATEGORY=“FORECAST“) // 5%
***ELSE
*REC(FACTOR = 1.03, CATEGORY=“FORECAST“) // 3%
*ENDWHEN
As a last step, put *COMMIT at end of the script so that logic engine can post data to Database.
so final version should be same as below code.
*XDIM_MEMBERSET ACCT = SALARY
*XDIM_MEMBERSET TIME = 2011.JAN
*XDIM_MEMBERSET CATEGORY = ACTUAL
*WHEN ENTITY
***IS USA
*REC(FACTOR = 1.1, CATEGORY=“FORECAST“) // 10%
***IS
CANADA
*REC(FACTOR = 1.05, CATEGORY=“FORECAST“) // 5%
***ELSE
*REC(FACTOR = 1.03, CATEGORY=“FORECAST“) // 3%
*ENDWHEN
*
COMMIT Note 1 : You can use multiple condition value like *IS VALUE_A, VALUE_B
Note 2 : You can use >, <= with numeric value with *IS statement. ex) *IS > 4
By default, it is equal (=) so it will be ok even though you don't specify it.
Note 3 : can't use AND, OR and NOT with *IS
Note 4 : " (double quotation) is not mandatory for comparing string value with *IS statement.
Note 5 : *WHEN statement can be nested. For example,
*WHEN xxx
*IS “A”
*REC(…)
*REC(…)
*IS “B”
*REC(…)
*WHEN yyy
*IS “C”,”D”,”E”
*REC(…)
*ELSE
*REC(…)
*ENDWHEN
*ENDWHEN
Note 6 : You can use property value with *IS statement. ex) *IS Intco.Entity
Now we finished learning 3 basic parts of the script logic.
As I explained you in the first post of this series, I hope you feel script logic is not too complex. :smile:
I will post a couple of advanced features like LOOKUP in the next post for answering other questions.
