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.

To report this post you need to login first.

7 Comments

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

  1. Vadim Kalinin

    Hi Sami,

    I do not recommend storage of percent in some property and usage of this value in REC with FOR/NEXT loop. FOR/NEXT will result in a very slow code if you have significant number of cycles. Better to use dedicated account member!

    B.R. Vadim

    (0) 
    1. Sami Järvinen Post author

      Hi Vadim,

      I totally agree with you. It’s not recommendable to store numeric constants in master data but I like the fact that it’s possible. Freedom is to be able to choose from several alternatives. And sometimes that can mean bad choices…

      By the way, do you have some estimates how much it costs in performance to introduce a FOR/NEXT loop with only one or two values? I understand performance is a factor of many things but if you can give any kind of guideline it would help to quantify the cost.

      Sami

      (0) 
      1. Vadim Kalinin

        FOR/NEXT with 2-10 cycles is more or less acceptable. FOR/NEXT will first generate the long lgx with repeated code between FOR and NEXT (look on UJKT results).

        Vadim

        (0) 
  2. Ivan Rodriguez

    Sami, excellent series of posts I found a lot of information and useful use cases which are not so clear in the documentation. I have a big question maybe you could help me out, Any idea how could you achieve the opposite of this? That is using a signeddata as text to use it as a destination member.

    Example

    *REC(FACTOR = 1, ACCOUNT_USGAAP = ([ACCOUNT_USGAAP].[ACCT_MAP],[INTERCO].[I_NONE],[TIME].[T_NONE]))

    The intersection ([ACCOUNT_USGAAP].[ACCT_MAP],[INTERCO].[I_NONE],[TIME].[T_NONE])) has a signeddata of 110000 but I would like to use this as the destination member I would like to write into.


    When running my script as it is I get:


    Invalid Members [ACCOUNT_USGAAP]=$1 $


    So any way I could force a signeddata to be read as text? Ideas? Thanks! I would like to avoid BADIs and Z tables at all costs and this is my only chance of doing so.


    I could open a more detailed post specific for this question, just putting it here since it’s strongly related, thanks

    (0) 
    1. Vadim Kalinin

      Hi Ivan,

      Absolutely not possible! The only thing you can do with signeddata – is to check it with ternary operator:

      *REC([ACCOUNT].[A1]==110000 ? %VALUE% : [ACCOUNT].[110000], ACCOUNT=110000)

      but you can’t directly use 110000 stored in signeddata!

      Use badi or another idea!

      Vadim

      (0) 

Leave a Reply