This is the final part of my six-part series “How to write dynamic BPC Script Logic”. I am sure you will find a few ideas you haven’t seen before.

Behold the myriad possibilities offered by keywords WHEN and IS

Help documentation states that IS supports only literals, not variables. The following statement is given as an example of un-supported usage.

    *IS dimension.property

The documentation seems to be out of date as properties can be used. For example, the following construction using property SRCACCT works.

  *WHEN ACCOUNT
     *IS AUDITID.SRCACCT

The following example also works. The only caveat with this pattern is that an empty property value may lead to unexpected errors.

*WHEN ENTITY.CMETHOD
   *IS INTERCO.CMETHOD

I have also used other types of variables with keyword IS. Let’s assume, we want to check if AUDITID property CHK01 equals ACCOUNT property “X” Property “X” in this case is not constant but it is a variable the value of which depends on the CATEGORY. Sounds complicated and it is if we have to hard-code all possible combinations in advance.

The following script shows how to apply concatenation referred to earlier in this article. In this case, %CATSEL% contains a single base level CATEGORY member. Now we can compare AUDITID property CHK01 value against ACCOUNT property %CATCOL% value.

*SELECT (%CATCOL%, COLSEL, CATEGORY, ID = %CATSEL%)
*WHEN AUDITID.CHK01
     *IS ACCOUNT.%CATCOL%

The same works with the IS statement or even if both parts (WHEN / IS) have a variable property.

  *SELECT (%CATCOL%, COLSEL, CATEGORY, ID = %CATSEL%)
  *WHEN ACCOUNT.%CATCOL%
     *IS AUDITID.CHK01

There can be syntax errors when you save but the script works anyhow. I suggest some caution when pushing the system to extremes but probably we are not quite there yet. I will save the most shocking stuff until a bit later.

Keyword IS can also be accompanied by the following operators: <, >, <=, >=. Logical keywords AND and OR are not supported so the following example will not work.

  *WHEN AUDITID.SRCTYPE
    *IS >= PAYR01 AND <= PAYR10

However, this is easy to work around as shown below.

*WHEN AUDITID.SRCTYPE
   *IS >= PAYR01
     *WHEN AUDITID.SRCTYPE
      *IS <= PAYR10

Keyword WHEN allows us to create conditions using member ID or property values. A nice addition to the arsenal is the addition of using the parent as a condition. This allows us to check the immediate parent of a member.

*WHEN ACCOUNT.PARENTH1
    *IS SALARIES

If you need to create a condition for all base members of a hierarchy node you can use keyword BAS.

  *WHEN ACCOUNT
     *IS BAS(PL5000)

If you need to exclude those base members you can use the following format.

  *WHEN ACCOUNT
     *IS <> BAS(PL5000)
   // Some calculation here
   *ENDWHEN

If you find it easier to use only positive statement the following example has the same effect.

*WHEN ACCOUNT
   *IS BAS(PL5000)
    // Do nothing
   *IS SOMEOTHERCONDITION
     // Some calculation here
*ENDWHEN

Help documentation states that you can only pass a single value when the unequal sign (<>) is used. I have used multiple values such as the examples below. So the documentation seems to be out of date.

*WHEN ACCOUNT
   *IS <> PL5111, PL5112, PL5211, PL5212

*WHEN ACCOUNT
   *IS <> BAS(PL5100,PL5200)

Documentation about wild card usage is not entirely consistent so I will explain my findings here. Asterisk works with the IS statement. The following condition is true for any ACCOUNT number. So the statement can be used when you don’t want to apply any condition for your REC statement.

*WHEN ACCOUNT
   *IS *

Unfortunately, wildcard can’t be combined with a partial string to check for patterns. The following statements do NOT work.

*WHEN ACCOUNT
   *IS PL2*

*WHEN ACCOUNT
   *IS PL2?2?

Wildcard does not appear to work with the WHEN statement. The following expression which can be found in some documents does NOT work. I don’t consider this to be a real loss.

*WHEN *
   *IS *

Write back statements can include variables

Help documentation is silent about using variables with write back statements. In fact, you can construct very dynamic write back statements using variables. Below you can find some elementary examples with variable values. Variables can be used either directly in calculations or as selection conditions to retrieve the relevant values.

The first pattern you have seen in this article many times.

  *REC(EXPRESSION = %VALUE% * %INCPERC%)

The second pattern is also very useful. In the following example, we calculate a time-based ratio. The result is saved on a separate evaluation view dimension member.

*REC(EXPRESSION = %VALUE% / ([TIME].[%PREVYRSEL%]), EVALVIEW = PREVYRCOMP)

Conditions based on transaction figures

Our business case didn’t require if-then conditions based on the transaction data but the topic is very closely related. For example, you might want to apply a different interest rate depending on the account balance. This is where ternary expressions come in handy. The help documentation doesn’t provide any information, so I will write a few lines. There are also many good posts at the SAP Community Network. I would like to give special credit to Vadim Kalinin who has written some of the most instructive posts on this important topic.

Ternary expressions can be used when you need to apply Boolean logic. Boolean logic is simply an if-then statement. It consist of three parts. The first part is the condition. You can also think of it as the question asked. The second part is the outcome if the condition is met (logical true). The third part is the outcome if the condition is not met (logical false). The syntax is as follows.

  A > B ? C : D

In the above example, A > B ? is the question asked or the condition. If A is greater than B, the value will be C. In the opposite case, the value will be D. A common application is to reclassify receivables and payables based on the account balances. The following script can be used to reclassify credit balance receivable accounts.

*WHEN ACCOUNT
  *IS *
    *REC(EXPRESSION = %VALUE% < 0 ? %VALUE% * -1 : 0, DATASRC = RECL)
    *REC(EXPRESSION = %VALUE% < 0 ? %VALUE% : 0, ACCOUNT =   ACCOUNT.RECLASS, DATASRC = RECL)
*ENDWHEN

Ternaryexpressions support the following comparison operators: >, <, >=, <=, ==

Final remarks

I hope that this article has raised your awareness and that it helps to see that Script Logic can do much more than it is usually given credit for. Once you start thinking about ways to make your script more dynamic you are bound to find many uses for these techniques. If you feel uncertain about SAP service coverage, it is advisable to stick to features explicitly specified in the documentation provided by SAP. So if in doubt, please check the help documentation and your system specific Notes at SAP Marketplace to be on the safe side.

Oh, I almost forgot the truly wicked thing I promised. Here it comes, so be forewarned. Gloves are off, no narration this time, just code.

*SELECT (%COOLVAR%, COOLCOL, CATEGORY, ID = %CATSEL%)
*WHEN ACCOUNT
    *IS *
      *REC(FACTOR=1, %COOLVAR%)
*ENDWHEN

Cool, eh? How do you like this one?

*SELECT (%COOLVAR%, COOLCOL, CATEGORY, ID = %CATSEL%)
*WHEN ACCOUNT
  *IS *
   %COOLVAR%
*ENDWHEN

If that wasn’t enough, check out the following monstrosity.

%COOLVAR1%
%COOLVAR2%
%COOLVAR3%
%COOLVAR4%

Ineffable! Well, maybe not quite. But that should give you enough food for thought to cause severe cerebral indigestion.

To report this post you need to login first.

19 Comments

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

  1. Sunil Kumar

    I am trying this in my system but other than Literals it is not allowing anything like the below. Is there a certain SP I have to be at. We are are BPC NW version SAPK-81002INCPMBPC. Please help.

    The IS <> BAS(STATISTICAL) is not working

    *XDIM_MEMBERSET ACCT_PRIME=<ALL>

    *XDIM_MEMBERSET AUDITTRAIL=BAS(PLCV)

    *XDIM_MEMBERSET BUSAREA=<ALL>

    *XDIM_MEMBERSET CO_CODE=<ALL>

    *XDIM_MEMBERSET DC=<ALL>

    *XDIM_MEMBERSET FUNC_AREA=BAS(FUNCTOTAL)

    *XDIM_MEMBERSET PROFIT_CENTER=<ALL>

    *XDIM_MEMBERSET RPTCURRENCY=USD

    *XDIM_MEMBERSET TRANS_TYPE=<ALL>

    *XDIM_MEMBERSET TRADE_PARTNER=BAS(WTP)

    *XDIM_MEMBERSET VERSION=%VERSION_SET%

    *XDIM_MEMBERSET MEASURES=PERIODIC

    *WHEN ACCT_PRIME

    *IS <> BAS(STATISTICAL)

      *REC(FACTOR=0)

    *ENDWHEN

    (0) 
  2. Mani Ramasamy

    Very useful post 🙂 ,

    Wish *ELSE could work work as below,

    *WHEN ENTITY.CMETHOD

       *IS INTERCO.CMETHOD

    *ELSE

    *REC(…)

    *ENDWHEN

    This returns,

    UJK_VALIDATION_EXCEPTION:Invalid when/endwhen – Line #xx : Only “=” supported here

    Appreciate any help on this.

    Thanks

    (0) 
        1. Mani Ramasamy

          Yes,

          Same as Vadim’s.

          I tried to  use the select statement to get all the property variables as well

          *SELECT(%TEST%, “CMETHOD”, INTERCO, “[CMETHOD]” <>””)

          *WHEN ENTITY.CMETHOD

             *IS <> %TEST%

          *REC(…)

          *ENDWHEN

          This doesn’t fail, but then it doesn’t produce the correct results either, since %TEST% has all values at once and it doesn’t compare one by one.

          Thanks

          Mani

          (0) 
                1. Sami Järvinen Post author

                  To avoid overwriting, it’s necessary to add some distinction between read and write area. In the following example I use AUDIT. By tweaking the FACTOR of each REC statement it should be possible to get the desired results.

                  *WHEN AUDIT

                  *IS INPUT

                  *WHEN ENTITY.CMETHOD

                     *IS *

                  *REC(FACTOR=-1, AUDIT=ELIM10)

                    *IS INTERCO.CMETHOD

                  *REC(FACTOR=1,AUDIT=ELIM10)

                  *ENDWHEN

                  *ENDWHEN

                  (0) 
                  1. Vadim Kalinin

                    For sure you have to use some extra dimension to differentiate target records…

                    I am simply talking about the difference between this approach (write all, then zero records with equal properties) and not working method (write only to not equal property combinations).

                    (0) 
              1. Mani Ramasamy

                Good workaround.

                Although, we still cant get this to work in our SP version. UJKT doesn’t give any error but doesn’t post any records either.

                *WHEN ENTITY.CMETHOD

                   *IS INTERCO.CMETHOD

                *REC(…)

                *ENDWHEN

                Installed Software_2015-09-11_13-11-31.png

                It only works as below,

                *WHEN ENTITY.CMETHOD

                   *IS = value //<> value also works

                *REC(…)

                *ENDWHEN

                Can someone provide the SP version that this truly works in BPC 10 NW.

                Thanks again.

                Mani

                (0) 
  3. Graham Johanson

    Hi Sami,

    Can you expand on your “truly wicked” section?  Are you implying that you would house the REC statements in properties,  i.e. allow the script to be dynamically controlled through master data?

    Thanks,

    Graham

    (0) 
  4. Graham Johanson

    Hi Sami,

    You can also include && (AND) or || (OR) conditions in the ternary statement.  In fact, we have found that quite a few of the C++ operator’s syntax are supported.

    Thanks,

    Graham

    (0) 
  5. Mark P Burke

    Sami…..this is  fabulous set of posts!  Your passion for the product is evident in all six parts of the series.   On behalf of SAP, thanks for your contributions on SAP SCN.

    How can a customer/partner bridge a gap if new to BPC Logic?   I wanted to make all aware that you can find a significant amount of working BPC logic script in the Strategic Financial Planning model of the Extended Financial Planning RDS (rapid-deployment solution).

    This model is FREE from SAP Labs to customers of SAP BPC.  You can download the models in minutes per links below, and the script is tested/working and available in context of a working BPC application!    Download and restore the model, and you will have working script at your fingertips to refer to.

    Extended Financial Planning RDS (BPC NW)

    • – Includes G/L Financial Planning, Strategic Financial Planning, Capital Planning

    https://service.sap.com/public/rds-epm-planning

    And if you are interested in same model on BPC MSFT, we have:

    Strategic Financial Planning RDS (BPC MSFT)

    https://service.sap.com/public/rds-sfp

    Contact Mark Burke at Mark.p.burke@SAP. com if any questions on BPC RDS

    Free training from SAP available here:

    Register here:  Registration for EPM RDS Academy 2015 Survey

    Access here:  SAP – SAP Jam

    (0) 

Leave a Reply