1. Resources
  2. >>
  3. Blog
  4. >>

  5. SAP BPC Version 10 – Script Logic (Syntax!)

In my previous posting; I mentioned about two types of Syntax options in SAP BPC Script Logic. They are:

      1. SQL based syntax (Choose this option for better performance)
      2. MDX based syntax (Choose this option for specific record level calculations)

SQL based syntax Simplification:

1. *ADD / *ENDADD =

        Value of set of members (accounts) are added and included in another account.

2. *INCLUDE =

        Multiple Script Logic files with different purpose can be added into a single fine using this command. During LGX generation, all codes are combined into one big piece of code, during runtime.

3. *REC =

        This statement generates a record / modifies a record; to be posted in the database. Even if it is for the same destination cell, a source record can generate as many destination cell records. Multiple REC statements possible between *END/*ENDWHEN statement

4. *XDIM_MEMBERSET =

        It restricts the data, filters the source dataset to the required dataset for specific calculation, intended in the script logic. For each restriction using a dimension, there can be a separate XDIM statement. Generally it is followed by the Dimension name & Member value/value set.

5. *XDIM_ADDMEMEBERSET =

        This statement is used to add more member values to the scope of member value already defined with the XDIM statement for conditional calculations.

6. *XDIM_FILTER =

        Filters the members of the scope of that dimension. It does not filter the scope of data, rather filters from available scope.

7. XDIM_MAXMEMBERS =

        Specifies the maximum number of members that should be included in one query, per dimension the syntax is associated with.

8. XDIM_PACKAGEBY =

        This syntax is used for parallel processing. However configuration needs to be done in SPRO need to be done before using this.

9. *SELECT / *ENDSELECT =

        Like any other SQL statement, this syntax helps to retrieve a list of values from the master data table (Dimension) and store them in a defined variable. Can be written in any part of the logic and the resultant value can be used in any part of the logic as well. Generally better to write in the beginning of the logic structure.

10. *WHEN / *ENDWHEN =

        *WHEN/*ENDWHEN can be used with property values of the dimension; to further create conditions for data processing/transformation/modification/manipulation, for the REC statement. Ex: *WHEN ACCOUNT *IS “XY00001234”. Same as SELECT/ENDSELECT statement, but this it used when different transformation is needed for different type of field values in a record. Hence multiple *WHEN/*ENDWHEN statements possible within a script logic. It works as a loop statement.

11. *DESTINATION_APP =

        Use to make the datasets of one model available for other. It transfers the data from one model to another. Between Dimension “Category” values. It is acts like a “MOVE-CORRESPONDING” statement in traditional ABAP.

12. *LOOKUP / *ENDLOOKUP =

        Similar to “*DESTINATION_APP” but this does not transfer the data between the model, rather looks up for the required calculation. It is acts like a “READ” statement in traditional ABAP. Example: While doing currency translation of a value, it will be required to look into the EXCHANGERATES model.

13. *TMVL =

        This is used to retrieve a time value after taking into consideration an offset value from a given time period. Similar concept existed in traditional ABAP. Example traditional ABAP statement X = Y+0(4). If value of Y = “27.08.2013” or “20130827”. X will return a value = “2013”. This can be used with WHEN, FOR, REC statements. Can be used with variables. Multiple offset values with TMVL statements can be used with XDIM statements.

14. *COMMIT =

        Saves/stores the calculated values in the database. In case of two dependent calculations, resulting value of the first calculation must be saved into the database with a “*COMMIT” statement, before its value is used in another calculation formula within the script logic.

15. *ADD_DIM =

        While copying data between two models, if source model does not contain all the dimensions as the destination model, missing dimensions in the source model can be created/added using this syntax before data copy. Possible to use this for multiple dimensions in one statement.

16. *RENAME_DIM =

        While copying data between two models, if dimension names between the models do not match; then mismatching dimension names can be renamed to be the same before copy process. Possible to use this for multiple dimensions in one statement.

17. *SKIP_DIM =

        While copying data between two models, if destination model does not contain all the dimensions as the source model, some of the destination models can be skipped from copying process using this syntax. Possible to use this for multiple dimensions in one statement.

18. *SELECTCASE / *ENDSELETC =

        This is select statement with a CASE together to encourage conditions within. Works as IF, ELSEIF & ENDIF statement in traditional ABAP.

19. *START_BADI / *ENDBADI =20. *RUN_ALLOCATION =21. *BEGIN / *END =

        These syntaxes are used with Boolean Expressions (YES / NO). Similar concept, as may have seen in BW-BEX formulas. The individual conditions will be enclosed in parenthesis. If the condition is true, return value will be 1, else 0.

22. *IIF =

        These syntaxes work, just as IF, ELSEIF, ENDIF statements in traditional ABAP. If the condition satisfies, then it directs the record to undergo a calculation.

23. *DIMNAME_DIM =24. *DIMNAME_SET =25. *SUB/*ENDSUB =26. *FOR / *NEXT =

        This syntax is used with a variable, which returns/contains multiple values. This syntax then helps to process individual variable values, once by one. It works like FOR EACH statement in FOX formula.

27. *GET =

        Retrieves the value from the memory

28. *FUNCTION / *ENDFUNCTION =29. *WHEN _REF_DATA =

      It sets the focus to either master data or transactional data. When is set to master data, it loops through all the master data values of the dimension. And when set to transactional data, it loops through all transactional data, which uses the dimension/values in its scope. Without an explicit definition, transactional data mode is selected by default.

Notes:

  • “*” at the beginning of a statement, means it is an executable statement.
  • There is no FULL STOP needed in any of the above statements.
  • DimensionName.Property = ACCOUNT.RATETYPE. In this example “Account” is the Master data and “Rate type” is the attribute. If “Property” is not defined or missing, then the Primary Key is assumed, which in this case would be “Account ID” OR “GL Account”.
  • Constant values of Numeric form, should not be included in double quotes.
  • Constant value with String form, should be included in double quotes.
  • While a “comma” between the “member values” reads/records individual member values; a single “member value” after the “=” sign; summarizes all children values of the members to one parent record.
  • Bas () syntax with a member value is used to read all children values of a member defined. Generally used to copy values between time periods at a summary level, without defining every single member.
  • A BAS () syntax can not be combined with another member value in a single statement.
  • [# ] syntax with a member value is used as the target destination; to hold value after calculation in the memory, before saving it into the database with a *COMMIT statement.
  • Statement “*REC(FACTOR=4, CATEGORY=”FRCST”) is used to multiply the value of the record by 4 and to the result under the category “FRCST”.
  • Statement “*REC(EXPRESSION=%VALUE% + 750) is used to add the original value of the record, which is represented by a variable with 750.
  • Variables are represented with notation “%VARIABLE%” (percentage sign before and after).

MDX based syntax Simplification

  • More detail or granular level calculation possible with MDX based syntaxes in script logic
  • It is possible to focus on a specific record by indicating a dimension value.
  • Generally the syntax contains two parts: “Dimension” itself and “Dimension value” with a decimal point in between to tie them up
  • Both Dimension & Dimension value are bracketed as below for the MDX syntax to work
  • Most of the syntaxes written above with SQL approach, can be written with MDX approach as well
  • MDX syntax is straight forward, but may incur a performance penalty.
  • The SQL syntax involves the creation of two records: one with the value originally held by A and one with the value originally held by B. The two new records , assigned to account “C” by the REC statements, then aggregate up to the correct value.
  • MDX: [#C] = [A] + [B] – The calculated member is prefixed with a ‘#’
  • SQL: *WHEN ACCOUNT *IS « A »
  • *REC(ACCOUNT=« C »)
  • *IS « B »
  • *REC(ACCOUNT=« C »)
  • *ENDWHEN
  • Revaluation example with MDX: [ACC].[#KEYFIGURE] = [ACC].[KEYFIGURE] * 1.10
  • Addition example with MDX : ADD%ACCT%=[ACCOUNT1], [ACCOUNT2], [ACCOUNT3], [ACCOUNT4]

SAMPLE SYSTEM VARIABLES

  • USER% – Returns current planning & consolidation User
  • %APPSET% – Returns current planning & consolidation AppSet
  • %APPLICATION% – Returns current planning & consolidation Application
  • %YEAR% – Returns current planning & consolidation Current Calendar Year