As a last post of this script logic series, I would like to explain some special functions that only exist in BPC MS.
Even though the user can implement below functions in BPC NW using BADI, in my personal opinion, I think we really need these functions in the BPC NW version.
1. FLD function (for using property value with *REC statement)
Sometimes customer may want to use a property value for calculating with *REC statement.
The syntax is:
FLD(Dimensionname.Propertyname)
and here is an example.
*WHEN ENTITY.SCALE
*IS <>””
*REC(EXPRESSION=%VALUE%*FLD(ENTITY.SCALE))
*ENDWHEN
It means the Records value will be calculated as a product of current record value and scale property value of entity
member in the current record.
Let's assume fact table has below current records.
ACCOUNT, ENTITY, CURRENCY, SIGNEDDATA
Revenue, CT, USD, 100
Revenue, CA, USD, 200
Revenue, NJ, USD, 300
Revenue, NY, USD, 400
Here is an example logic script.
*WHEN ENTITY.SCALE
*IS <>””
*REC(EXPRESSION=%VALUE%*FLD(ENTITY.TAXRATE)/100, ACCOUNT="TAX")
*ENDWHEN
and let's say ENTITY dimension member worksheet looks like below.
ID, PARENTH1, ..., TAXRATE
CT, USA, ..., 6.35
CA, USA, ..., 7.75
NJ, USA, ..., 7.00
NY, USA, ..., 8.875
When you run the script logic, 4 records will be generated as below.
ACCOUNT, ENTITY, CURRENCY, SIGNEDDATA
Revenue, CT, USD, 100
Revenue, CA, USD, 200
Revenue, NJ, USD, 300
Revenue, NY, USD, 400
TAX, CT, USD, 6.35
TAX, CA, USD, 15.5
TAX, NJ, USD, 21.00
TAX, NY, USD, 35.5
2. GET function
When we use *REC statement, we can use Lookup function to get a value from the other application. For example, FX
translation needs rate value. I explained it in the previous post.
In addition to above, users may want to get the value from other record within SELECTED REGION.
The syntax is:
GET({dimension}={member name(ID)}[, {dimension}={member name(ID)}]…)
and here are some examples.
GET(ACCOUNT= ”EXTSALES”)
GET(ACCOUNT= ACCOUNT.MYPROPERTY)
GET(ACCOUNT= ACCOUNT.MYPROPERTY + ".TOTAL" ) // support the concatenation using '+' operator.
Note: if ACCOUNT.MYPROPERTY has "TAX" value, it will get the value of TAX account record value.
GET(ACCOUNT=ACCOUNT.ELIMACC,ENTITY=INTCO.ENTITY)
Note: User can specify multiple dimension names to select record.
All missing dimension will be same as source record.
Let's use the same example that we used above.
We will calculate NetRevenue using Revenue and Tax.
*XDIM_MEMBERSET ACCOUNT = REVENUE, TAX // I will explain why we need it.
*WHEN ACCOUNT
*IS “REVENUE”
*REC(EXPRESSION=%VALUE% - GET(ACCOUNT=”TAX”), ACCOUNT=”NETREVENUE”)
*ENDWHEN
If we run above script, 4 records will be created.
ACCOUNT, ENTITY, CURRENCY, SIGNEDDATA
Revenue, CT, USD, 100
Revenue, CA, USD, 200
Revenue, NJ, USD, 300
Revenue, NY, USD, 400
TAX, CT, USD, 6.35
TAX, CA, USD, 15.5
TAX, NJ, USD, 21.00
TAX, NY, USD, 35.5
NetRevenue, CT, USD, 93.65 // 100 - 6.35
NetRevenue, CA, USD, 184.5 // 200 - 15.5
NetRevenue, NJ, USD, 279 // 300 - 21
NetRevenue, NY, USD, 364.5 // 400 - 35.5
There are two things you keep in mind to use this GET function.
First, GET function will not search from database but search from 'SELECTED REGION'.
Therefore, you should specify members in the *XDIM_MEMBERSET before use GET function.
That's why we specified *XDIM_MEMBERSET ACCOUNT = REVENUE, TAX in the example.
Second, the Script engine can't use the results of a previously calculated value without *COMMIT statement.
This example will not work.
*WHEN ACCOUNT
*IS “UNITS”
*REC(ACCOUNT=”REVENUE”, FACTOR=GET(ACCOUNT=”PRICE”))
*IS “REVENUE”
*REC(ACCOUNT=”TAXES”, FACTOR=.5)
*ENDWHEN
So we need to separate two calculations using Commit statement to make it work.
*WHEN ACCOUNT
*IS “UNITS”
*REC(ACCOUNT=”REVENUE”, FACTOR=GET(ACCOUNT=”PRICE”))
*ENDWHEN
*COMMIT
*WHEN ACCOUNT
*IS “REVENUE”
*REC(ACCOUNT=”TAXES”, FACTOR=.5)
*ENDWHEN
Usually, *COMMIT statement will post records into the database but what if the customer wants to calculate without
posting data?
For that purpose, BPC MS has a special statement which is *GO.
*GO statement will not post data but can use the result value of the previous script.
3. Dummy memory variable
User can save intermediate result and assign them to dummy members like dummy account members or any other dimension.
This member can be used as temporary records to store intermediate results that can be used as inputs for subsequent
calculations. These temporary records will be automatically skipped during the commit time.
Dummy members must be identified with a leading pound (#) sign.
For example:
*REC(ACCOUNT=#TEMP_ACC)
Even though #TEMP_ACC member does not exist in the account dimension, the generated record can be used anywhere in the logic
as below example, but its value will not be stored in the database.
*WHEN ACCOUNT.FLAG
*IS = Y
*REC(ACCOUNT=#TEMP_ACC)
*ENDWHEN
*GO
*WHEN ACCOUNT
*IS #TEMP_ACC
*REC(FACTOR=GET(ACCOUNT=MULTIPLIER),ACCOUNT=FINAL)
*ENDWHEN
This is useful to calculate a complex calculation like allocation.
Let's assume you want to allocate some expense based on the each store's area.
You must have a total area value of each store because the formula will be
Each Store's Expense = Total Expense amount * Each Store's area / Total area
Even though each store's area is saved in the database as an account member, you need to have the total value. Of
course, it is easy to get the total value using MDX script statement but we need to do it using the SQL script statement
for better performance.
Finally, I finished the BPC Script logic for dummies(?) series.
Well, it might not be enough for using it right now but as I addressed in the first post,
I hope you get the basic concepts of BPC script logic so that you can understand and read what it means.
Thank you for reading this series.
James Lim
