Skip to Content
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.
To report this post you need to login first.

19 Comments

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

  1. Scott Bean
    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.
    (0) 
    1. v sapfico
      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.

      (0) 
  2. v sapfico
    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.

    (0) 
  3. Surendra kumar patra

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

    (0) 
  4. Man M

    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

    (0) 
  5. Ron Keshet

    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.

    (0) 
  6. Alexander Curry

    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.

    (0) 
  7. Mike Harding

    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

    (0) 
    1. Vadim Kalinin

      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.

      (0) 
  8. JP Pinelli

    Hello All

    Just wondering if anyone has an answer to Ron question above

    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.

    (0) 
  9. Tung Ha

    James,

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

    (0) 

Leave a Reply