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