In the second part of this series we saw simple yet effective techniques to write dynamic Script Logic. However, some requirements are not easily met. One of the most commonly cited topics is the (lacking) ability to use property values for calculations in a REC statement. I don’t recommend using property values in calculations but I will show you how this can be accomplished. I haven’t seen this method published anywhere else. I believe it is novel to all readers except for a few who are privy to intel not available to mere mortals.
Using variables in Script Logic
Let’s cover some related topics in more detail to put the solution in context. Handling of variables in Script Logic is different from many other programming languages. Some variable operations are non-obvious or entirely out of scope. Official documentation on variable usage in the Netweaver version is patchy and sometimes out of date. The following overview is my attempt to explain how the system handles variables. Please note that MDX variables are not covered in this article.
A great way to introduce variable values into your script is to use Data Manager (DM) variables also known as Dynamic Parameters. Values are based on user prompts or they can be constants. It is worth noting that for your Script Logic all DM variables behave as constants because the assigned values cannot be changed. DM variables can be recognized by the dollar signs around them such as $INCPERC1$. There is a good How to paper explaining how to pass DM variable values to Script Logic so I will skip the details. In our business example, we could prompt the user to provide a percentage and then use this value in the script as follows.
*REC(FACTOR = $INCPERC1$, ACCOUNT = PL5119, AUDITID = CALCRES)
There are two other types of variables that I have found particularly useful. Both are usually written with percentage signs around them such as %ACCSEL%. Use of $ and % signs is optional in some cases, but I find they make the script easier to read so I use the notation consistently. We already saw the first type of variable in action when we wrote our SELECT statement. In the example below, %AUDITSRC% is a variable which contains the selection specified by the condition.
*SELECT(%AUDITSRC%, ID, AUDITID, SRCTYPE= PAYR)
SELECT statement is used for data scoping, but it can also be used for other purposes. There are three interesting features that make the statement very versatile. 1) in most cases, the system treats variables without paying too much attention to data type; 2) in many cases the system interprets a variable value as a text string; 3) put together these two features enable concatenation which is nice because string manipulation is otherwise not possible. This allows us to accomplish many things that might seem unattainable.
Now we are ready to see how property values can be used in Script Logic calculations. Suppose we have the necessary percentages maintained in AUDITID property INCPERC. In the Microsoft version this is accomplished using the FLD statement as follows.
*REC(FACTOR = FLD(AUDITID.INCPERC))
We cannot use FLD in the Netweaver version as it is not supported. Luckily, we can work around this. Let’s have a closer look at the SELECT statement.
*SELECT(%INCPERC%, ID, AUDITID, ID = PAYR01)
The statement above selects AUDITID dimension member PAYR01 and stores the ID PAYR01 to our variable %INCPERC%. But we don’t want the ID but the numeric value stored in INCPERC. All we need to do is to alter the statement slightly.
*SELECT(%INCPERC%, INCPERC, AUDITID, ID = PAYR01)
The statement above selects AUDITID dimension member PAYR01 as before, retrieves the property INCPERC value and stores the retrieved numeric value in our variable %INCPERC%. Now that we have captured the percentage value we can use it in our calculation.
The property itself allows basically any value as the data type is CHAR (character). The system does not control how the properties are maintained. Script Logic accepts our text string variable value and REC statement treats it as a number. It is the up to the key user to make sure that only numeric values are entered to avoid errors. The example script below works because the system allows us to take liberties when it comes to data types.
*REC(FACTOR = %INCPERC%, ACCOUNT = ACCOUNT.TRGTACCT, AUDITID = CALCRES)
Hey presto, we have successfully used a property value in our REC calculation. This is all very nice but we only captured one value. If you want to read values from multiple members, you could simply add a variable for each of your AUDITID’s. However, that is not flexible and dynamic enough for most purposes. So let’s see how we can enhance the method.
At this stage, I would like to remind you about the nature of the select statement. As its name implies it is used for selection and it can store multiple values. If you are familiar with SAP ERP or ABAP it might be helpful to think in terms of SETs, SELECT-OPTIONs or RANGEs. To select multiple values, we can define a dynamic selection such as the example below. Please note that both variables will contain the same number of values because we use the same selection condition in both statements. As already mentioned above, the key user is responsible for maintaining valid property values.
*SELECT(%AUDITSRC%, ID, AUDITID, SRCTYPE= PAYR)
*SELECT(%INCPERC%, INCPERC, AUDITID, SRCTYPE= PAYR)
Now that our variables contain multiple values we need one more thing to use those values. It is time to introduce the third type of variable and the FOR-NEXT loop construct. See below for complete script example.
*SELECT(%AUDITSRC%, ID, AUDITID, SRCTYPE= PAYR)
*SELECT(%INCPERC%, INCPERC, AUDITID, SRCTYPE= PAYR)
*SELECT(%ACCOUNTSRC%, ID, ACCOUNT, SRCTYPE= PAYR)
*XDIM_MEMBERSET AUDITID = %AUDITSRC%
*XDIM_MEMBERSET ACCOUNT = %ACCOUNTSRC%
*FOR %AUDITLOOP% = %AUDITSRC% AND %INCLOOP% = %INCPERC%
*WHEN AUDITID
*IS %AUDITLOOP%
*REC(FACTOR = %INCLOOP%, ACCOUNT = ACCOUNT.TRGTACCT, AUDITID = CALCRES)
*ENDWHEN
*NEXT
We use two variables %AUDITLOOP% and %INCLOOP% in the FOR-NEXT loop and our SELECT statements guarantee that they contain the same number of entries. Netweaver help documentation implies that only one variable is supported with FOR-NEXT loops. This seems to be a documentation glitch and I consider it safe to use two variables. Again, this is really neat but the method works only if you need just one variable value per data record. If you need more variables the best way is to use tuple expressions. The tuple method was described earlier in this article and it is the recommended tool for the job.
In the fourth part of this series, you will get a glimpse of some of the most elusive SELECT statement features.
