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 . 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.
