James Lim

May 24, 2011 4 minute read

BPC Script logic for Dummies? (Part 1)

Even though I have a long experience with BPC, if someone asks me to a write a script logic, I might not write that code within 10 minutes.

It is not a problem of my knowledge but writing a script logic needs to understand your financial requirements and knowledge of your application like account member id and name of properties.

There are some documents and help files about the script logic and HTG but end users may feel that is not easy. I agree with that but if you understand its structure and concept, I can guarantee you can read and understand what it means and what the purpose of that script logic is. On top of that, you might be enable to modify or create it. It is the same thing that is not easy to write a book but reading a book is a different story.

Let’s learn it step by step.

1. Understand 3 logic parts.

Logic is a special script engine and it consists of 3 parts. Scoping, calculation(create/Record) and Writing.

2. Scoping

BPC is based on NW BI or MSAS which has a lot of data. Therefore, if you don’t specify scope, it will take a lot of time to read data.
Let’s say you need to calculate 2011.January, actual data and only one account like ‘Discounted External sales’ based on the External Sales.

How can we scope this from a big database?

The answer is…. *XDIM_MEMBERSET

*XDIM_MEMBERSET is using for scope data by each dimension.
Here is the grammar of XDIM_MEMBERSET.

*XDIM_MEMBERSET <DIMENSIONNAME> = <MEMBERNAME 1>,<MEMBERNAME 2>…<MEMBERNAME n>

   Now, let’t scope above exampe.
   for scoping 2011.January, *XDIM_MEMBERSET TIMEDIM=2011.JAN
   for scoping actual,       *XDIM_MEMBERSET CATEGORYDIM=ACTUAL
   for scoping external sales, *XDIM_MEMBERSET ACCOUNTDIM=EXTSALES
                                (Note: we need to scope External sales because discounted External sales will be calculated based on the External Sales.)

3. Now, we just finished scoping so it is time to calculate(create) data.

Unlike other script engine, there is no temporary variable in the logic script engine so it will create a record that has same as fact table structure.
and it will replace or change its value uaing ‘*REC’ command. (Note: *REC means ‘Record’.)

Here is the grammar of *REC statement

*REC[([FACTOR|EXPRESSION={Expression}[,{dim1}={member},{dim2}=?)]

Using that grammar, We can make our script as below.

*REC (FACTOR = 0.9,ACCOUNT=”DISCOUNT_EXTSALES”)
Which means multiply by 0.9 to current scoped record and replace account member with DiSCOUNT_EXTSALES

Here is an example what happens with above statement.

<Scoped record>
EXTSALES,2011.JAN,ACTUAL,10000

<Generated record>
DISCOUNT_EXTSALES,2011.JAN,ACTUAL,9000

What if you want to put generated record into BUDGET category?
Then statement should be

*REC (FACTOR = 0.9,ACCOUNT=”DISCOUNT_EXTSALES”,CATEGORY=”BUDGET“)

Now you want to put 80% value into FORECAST at the same time. what should we do?
We can use another *REC statement at the same time.

*REC (FACTOR = 0.9,ACCOUNT=”DISCOUNT_EXTSALES”,CATEGORY=”BUDGET“)
*REC (FACTOR = 0.8,ACCOUNT=”DISCOUNT_EXTSALES”,CATEGORY=”FORECAST“)

<Scoped record>
EXTSALES,2011.JAN,ACTUAL,10000

<Generated record>
DISCOUNT_EXTSALES,2011.JAN,BUDGET,9000

DISCOUNT_EXTSALES,2011.JAN,FORECAS,8000

Getting easier? I hope so 🙂

Please keep in mind below rule.

a. Each REC instruction generates ONE new record.

b. Each source record can generate as many records as desired.

It means you scoped 1 record but you can create multiple records using this.

Currency translation is the best example because you need multiple

converted currencies using a local currency record.

Therefore, you can imagine there will be multiple *REC statement

in your currency conversion script logic.

c. Destination cell can be same. All values will be accumulated.

*REC statement will generate a new record so it doesn’t matter even though destination is same.

4. As a final step, we need to write data into database.

script statement is really simple one.

*COMMIT

Fortunately, it doesn’t have any parameter. Just use *COMMIT.

When BPC script engine execute *COMMIT, generated records will be posted

to the table using BPC sending engine which is same engine that you submit

data from the Excel workbook.

We reviewed three main parts of BPC Logic script as a first step.

I will explain advanced scoping, recording and commit command in the next post.

19 Comments

You must be Logged on to comment or reply to a post.

Former Member

May 24, 2011 at 11:29 am

This is great for someone who is just gettting started with script logic. Showing not just statement examples, but also the results of those statements is really helpful. Thanks James.

Former Member

June 1, 2011 at 3:56 am

Thanks for the information James.

Can you also please show me an example of how to 'get' the value of a member from a dimension, and use that value in the *REC statement? Basically, I want the system to 'read' values from the master data and use those values in my calculations.

Thanks,
V.

Former Member

May 25, 2011 at 3:18 am

James,

Thank you very much for this blog series. While I'm not a script logic writer myself, I find it very valuable to understand at least the basics. This helps me to learn more of the IT speak so I can help translate business requirements into language that IT folks can better understand. I look forward to your future posts on this subject.

~Jim

Raja Thangamani

May 25, 2011 at 3:30 am

Its very useful. Looking forward to see more blogs on the same 🙂

Raja

Former Member

June 1, 2011 at 3:58 am

Thanks for the information James.

Thanks,
V.

Former Member

June 27, 2011 at 7:01 am

Helpful post!

Surendra kumar patra

June 12, 2012 at 11:08 am

I feel the document is very good for a begineer who wants to have a basic idea/understanding about Script logic.

Former Member

July 10, 2012 at 8:20 pm

Thanks for the document, it is very helpful and well-written!

Former Member

December 20, 2012 at 7:38 pm

Hello James,

Thank you very much for this documents. I am bit slow learner. I am new to BPC and i have been asked to work on BPC scripts. I appreciate your help as i am not able to create even basic script.

I will tell you about my data in BPC

Value Dimension Member Property

GL account 1006 E70

1000.00 RC RCDEF

My requirement is i have multiple gl account members in GL account dimension. Each member has property which can have value E70 or M70 or so on.

I have another dimension RC which has member RCDEF and value for this record is 1000.00 (This is just example)

I want my final record like this

Value Dimension Member Property(RC)

1000.00 GL account 1006 E70

1000.00 RC RCDEF

I am trying to create script for this which i have mentioned below.

*SELECT (%ACCSET%, "[ID]" , GL_ACCOUNT, "[RC]<>' ' ") // To select all accounts where RC(property is not initial)

*SELECT (%RC1%, "[RC]", GL_ACCOUNT,"[ID]='%ACCSET%'") // To select all values of properties in derived accounts

*XDIM_MEMBERSET GL_ACCOUNT = %ACCSET%

//*XDIM_MEMBERSET GL_ACCOUNT.RC = %RC1%

*FOR %LOOP_ACCSET%=%ACCSET% AND %RC2%=%RC1%

*WHEN RC

*IS = "RCCDEFAULT"

//*WHEN GL_ACCOUNT.RC

//*IS = %RC2%

*REC(EXPRESSION =[GL_ACCOUNT].%LOOP_ACCSET%,RC=%RC2%)

//*ENDWHEN

*ENDWHEN

*NEXT

i am getting error "UJK_VALIDATION_EXCEPTION:LINE 22 syntax error: " missing ; before statement""

Can you please tell me where i am going wrong?

Thanks

Former Member

January 24, 2013 at 8:54 pm

Hello James and above commentators / contributors,

I am wondering if there is any SAP / BusinessObjects OFFICIAL (!!!) formal comprehensive manual or guide to their Logic Script for SAP
BusinessObjects Planning and Consolidation 10.0, Version for
NetWeaver, as I was unable to find mentioning of such document by Googling for it.

Thanks,

Ron.

Daniel Nurindra

November 26, 2013 at 12:21 pm

Hi James, it's very helpful post especially for the beginner like me.

Thanks,

Cheers,

Daniel. N

Former Member

December 3, 2013 at 1:34 pm

Hi James!

Thanks for sharing your knowledge!

This is very helpfull!

Best Regards,

Santiago R.

Former Member

December 4, 2013 at 4:55 pm

Hey James,

Thank you for this post, it is very beneficial to me starting out trying to wrap my head around script logic. This lays it out very nicely.

Thanks,

Alex C.

FASI UR RAHMAN MOHAMMED

April 24, 2014 at 9:41 am

Good explanation 🙂

Former Member

September 10, 2014 at 8:37 pm

James,

Is it possible to use time conditions in a script determine how to proceed with running subsequent code? We implemented a process change mid year; so if date in data pkg <2014.AUG, then run old process, else run new process.

Thank you

Vadim Kalinin

September 10, 2014 at 9:18 pm

Hi Mike,

It's better to open a new discussion with clear explanation of your requirements. In general there is no conditional code execution in script logic, but sometimes workaround can be found.

Vadim

P.S. There are few mistakes in this article:

1. *REC can't be used without surrounding WHEN/ENDWHEN

2. COMMIT is useful ONLY with MDX syntax, not with WHEN/IS/REC/ENDWHEN, after WHEN/ENDWHEN loop autocommit happens.

Former Member

September 17, 2014 at 3:16 pm

Hello All

Just wondering if anyone has an answer to Ron question above

Hello James and above commentators / contributors,

Vadim Kalinin

September 17, 2014 at 3:26 pm

Unfortunately, there is no such document at all! Only help and notes reading 🙁

Former Member

February 12, 2015 at 6:52 pm

James,

Really well done. Even a complete novice like me can immediately create effective script logic from your easy to read/understand material.

BPC Script logic for Dummies? (Part 1)

Assigned Tags