Financial Management Blogs by SAP
Get financial management insights from blog posts by SAP experts. Find and share tips on how to increase efficiency, reduce risk, and optimize working capital.
cancel
Showing results for 
Search instead for 
Did you mean: 
james_lim
Advisor
Advisor

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

16 Comments