Skip to Content

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

To report this post you need to login first.

16 Comments

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

  1. Ethan Jewett
    Thanks again James, for doing these blogs. We need more like them.

    While BPC NW does have an equivalent to the GET() statement in the form of an MDX tuple, the rest (FLD, *GO, and dummy variables) are very difficult to implement unless you put your complete logic in a BADI. I could not agree more with your statement that we need these in the NW version.

    Cheers,
    Ethan

    (0) 
  2. Pankaj Patil
    Hi James,

    Can the intermediate # values generated before GO be used after the commit as well ?

    Example:

    Logic…(#A1 is generated)
    GO
    Logic…(#A1 can be used)
    COMMIT  (Write to DB)

    Logic… (Can #A1 values be used in this block ?)
    COMMIT

    Regards,
    Pankaj

    (0) 
    1. James Lim Post author
      Hi PanKaj,

      I have never tried it that case before 🙂
      Please try it but I guess it might not WORK because all temp variable will be cleared after COMMIT.

      Why do you want to use COMMIT between them?
      If the number of records are not too big, it will be ok to use GO.

      James.

      (0) 
  3. Nathan Arbeau

    Hi James:

    I am wondering if there is a way to pickup dynamic temp variables in GET statement.  For example:

    // Do Calculation within WHEN

    *REC(EXPRESSION=%VALUE%,ACCOUNT=#%ACCOUNT%)

    // Now if I try to pickup those dynamic temp account with GET it doesn’t work

    *REC(EXPRESSION = GET(ACCOUNT = “#%ACCOUNT%”)

    I have tried various methods to get this to work but it continues to bring back 0

    Is there a way to do this?

    (0) 
    1. James Lim Post author

      Nathan,

      I am sorry for late reply, I was on vacation and am travelling now for customer.

      Basically, All dynamic variable can’t be used in the same block because it is not in the table that will be read by GET function.

      James.

      (0) 
      1. sharif shaik

        James,

        Thanks for sharing BPC Script logic basics in your blog. It is very useful for people new to BPC.

        I do have question on *REC statement. what makes *REC statement writing a record verses updating existing record. What happened if the record already exists and *REC stement try to add. will simply add another record?. We are using BPC 7.5 NW.

        Thanks

        Sharif

        (0) 
        1. James Lim Post author

          Sharif,

          Basically, if your logic script tries to post same data, it will be posted difference between existing value and posting value.

          Therefore, if there is no difference, it will not post any data.

          There is an option to change this behavior in BPC MS only.

          *Calcuate_difference = 0 (default is 1)

          Thanks,

          James Lim

          (0) 
          1. sharif shaik

            Thanks James, I have one more script logic question and appreciate your help.

            I’m trying to read PLAN/LC record and get the exchange rate (NRATE for GBP from FXRATE) and store the translated value under category = M03-CYP-S1. Some how my logic not translating. Just writting zero value, even though I’ve exchange rate NRATE have changed values and PLAN/LC records have values. 

             

            *XDIM_MEMBERSET CATEGORY=PLAN,%CATEGORY_SET%

            *XDIM_MEMBERSET TIME=%TIME_SET%

            *XDIM_MEMBERSET FS_ITEM=SELLEXP.BAS

            *XDIM_MEMBERSET COMP_CODE=%COMP_CODE_SET%

            *XDIM_MEMBERSET RPTCURRENCY=LC

            *LOOKUP FXRATE

            *DIM R_ACCT=”NRATE”

            *DIM R_ENTITY=”GLOBAL”

            *DIM NEWRATE:INPUTCURRENCY=COMP_CODE.CURRENCY

            *DIM MEASURES=”PERIODIC”

            *ENDLOOKUP

            *WHEN CATEGORY

            *IS “PLAN”

            *WHEN RPTCURRENCY

            *IS “LC”

            *WHEN FS_ITEM

            *IS SELLEXP.BAS

             

            *REC(EXPRESSION=%VALUE%*LOOKUP(NEWRATE),CATEGORY=%CATEGORY_SET%,FS_ITEM=SELLEXP.NRATE,RPTCURRENCY=”USD”)

            *ENDWHEN

            *ENDWHEN

            *ENDWHEN

            *COMMIT

            Thanks

            Sharif

            (0) 
    1. James Lim Post author

      BAS should work in BPC 10, I tried to find a note but there was no specific note.

      Please try it again and if you have any trouble, contact SAP support team.

      Thanks

      (0) 
  4. Takato Nagashima

    Hello James

    Thank you so much for your helpful blogs.

    I and our team do appreciate them.

    There is a question that we would like your advice to solve.

    About “WHEN_REF_DATA” statement.

    Though , “WHEN_REF_DATA” statement allows us to filter data by data types; transaction data or masterdata, in our understanding, the basic function of logic script is for scoping transaction. So there should be no situation to process master data in logic script.

    We will appreciate it if you provide us some advice to understand the real function of this statement.

    Thank you.

    (0) 
  5. Kati Hornung

    Hi James –


    Your blog postings were incredibly helpful and as a result I was able to script a somewhat complicated account reclassification.  I had seen elsewhere on the boards that doing a scripted account balance reclassification evaluation (if sum of X > 0, do this – if X < 0, do that) was not possible in the NetWeaver version, although perhaps I misunderstood that. 

    However, with your assistance I discovered a way to make it work.  I have posted my solution (SAP-BPC Netweaver Scripting for Account Reclassification | Kati Hornung | LinkedIn) for anyone else wanting to script account reclassification in NW.  I’m paying it forward with a resounding thank you to you at the top! 

    Thanks again – this was invaluable!

    – Kati

    (0) 

Leave a Reply