*FOR / *NEXT

Allowed uses: Global, MDX, SQL

*FOR {variable1} = {set1} [ AND {variable2={set2}]

{text}

{text}

…

*NEXT

You can use the *FOR / *NEXT structure to define loops over one or more lists of members. In addition, the rules module supports any number and nesting of FOR/NEXT loops in the body of the rules files.

Single For/Next loop

Say, for example, in the default translation rules you want to repeat a calculation for each reporting currency. You can write the following logic:

*FOR %CURR%=USD,EURO

//Average Rate for currency %CURR%

[measures].[!Avg_%CURR%] = {expression}

*NEXT

This works the same as the following commands:

//Average Rate for currency USD

[measures].[!Avg_USD] = {expression}

//Average Rate for currency EURO [measures].[!Avg_EURO] = {expression}

In addition, *FOR/NEXT loops support up to two variables iterating on two independent sets of members:

*FOR %ACC1%=ThisA,ThisB,ThisC AND %ACC2%= ThatA, ThatB, ThatC

[ACCOUNT].[#%ACC1%] = ([ACCOUNT].[ %ACC2%],[TIME].currentmember.lag(1))

*NEXT

The correct number of members is driven by the set of the first variable. If the first variable has less values than the second variable, the extra values of the second variable are ignored. If the first variable has more values than the second variable, the missing values of the second variable are assumed to be null. Note that this is not a nested loop. It is just one loop on two sets of variables.

FOR/NEXT loops executed on empty lists of elements do not cause the logic execution to fail. Instead, it is skipped without generating an error message.

Nested *FOR / *NEXT loops

The rules module supports any number of nested levels of FOR/NEXT loops in the body of the rules files.

Following is an example of valid syntax:

*WHEN TIME

*IS <>TOT.INP

*WHEN ACCOUNT

*IS PERCENT.ALLOC

*FOR %YEAR%=2003,2004,2005

*FOR

%MONTH%=JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC

*REC(FACTOR=GET(ACCOUNT="TOT.OVRHEAD",TIME="TOT.INP")/100,TIME="%YEAR%.%MONTH%")

*NEXT

*NEXT

*ENDWHEN

*ENDWHEN

Note that, in case of a single-level loop and the set of elements is empty, the rules still validate and execute correctly. However, in case of nested For/Next loops, none of the loops can contain an empty set of elements, otherwise the rules do not validate.

Nested loops, similar to single-level loops, can handle up to two sets of parallel variables, with the syntax:

*FOR %VariableOne%=FIRSTSET AND %VariableTwo%=SECONDSET

Using a passed set of members at runtime

You can use the passed members of a dimension in a For/Next loop, when the validation is performed at runtime.

*FOR %MYTIME% = %TIME_SET%

// logic content

*NEXT

When the dimension is Time, you can have the passed members ordered in the correct sequence (that is, from earliest to latest). You can do this by inserting the keyword ORDER_TIME after the *FOR instruction, as follows:

*FOR ORDER_TIME %MYTIME% = %TIME_SET%

The ordered set of members also automatically fill the gaps in the time sequence.

For example, if the members to order are 2005.JUN, 2005.FEB, the ordered set is:

2005.FEB, 2005.MAR, 2005.APR, 2005.MAY, 2005.JUN