*WHEN/ENDWHEN 
Allowed uses: By Commit, SQL
This set of instructions triggers the use of SQL syntax rather than MDX syntax. This structure works in the same way as the SELECTCASE / ENDSELECT structure, but the instructions that it processes are not the usual MDX formulas of a modeling logic, but some *REC( ) statements that generate new records.
The syntax is:
//-----------------------------------------------------------------------------------
*WHEN {criteria}
*IS {valid condition1}[,{valid condition2},…]
*REC[([FACTOR|EXPRESSION={Expression}[,{dim1}={member},{dim2}=…])]
[*REC[([FACTOR|EXPRESSION={Expression}[,{dim1}={member},{dim2}=…])]]
….
[*ELSE]
…
…
*ENDWHEN
//-----------------------------------------------------------------------------------
Where |
Is |
|---|---|
{criteria} |
What to test. Typically, this is a property of the current member of a dimension. The syntax is:
{criteria} can also use the reserved keyword LOOKUP( ). End of the example. |
{ValidCondition} |
One or more values that meet the criteria. They can be enclosed in double quotes if they need to be treated as strings. If they represent numeric values, the quotes should be omitted.
End of the example. If no operator is specified, the *IS clause assumes the presence of an equal sign
{ValidCondition} can also be a dimension property specified with the simple format:
|
Note
Multiple operators (such as <> or >=) must not be separated by any space. One or more blanks can be inserted between the operators and the value.
If any operator is used, only one value can be passed. (This syntax is invalid:
*IS >2,3,4)Other operators like AND, OR and NOT are not currently supported.
Example
The following are valid examples of using *WHEN:
*WHEN ENTITY
*IS INTCO.ENTITY
…
*WHEN ACCOUNT.SCALE
*IS <>CURRENCY.SCALE
WHEN criteria special case
A special case of *WHEN criteria is:
*WHEN *
This criteria can be used when there is actually no criteria to test. In this case, the *WHEN statement is only needed to trigger the SQL mode.
The correct syntax is:
*WHEN *
*IS *
*REC(…)
*ENDWHEN
Improved recognition of PRIOR(n) periods in * IS statements
In logic such as the following:
*CALC_EACH_PERIOD
*XDIM_MEMBERSET TIME = PRIOR(3), %SET%
*WHEN TIME
*IS PRIOR
//………
*ENDWHEN
All 3 periods of data preceding the first period in %SET% is recognized as PRIOR in the statement *IS PRIOR
If, for example, the user modifies 2005.MAR and 2005.MAY, the three period 2004.DEC, 2005.JAN, and 2005.FEB meets the criteria stated in the *IS line.
This allows the logic to recognize periods that may have been loaded in memory only with the purpose of calculating some lags in the time dimension.
Example
A fair example of how to use this feature is represented by the following logic:
*XDIM_MEMBERSET ACCOUNT=REVENUE,PRICE,PAYMENTS
*CALC_EACH_PERIOD
*XDIM_MEMBERSET TIME=PRIOR(3),%SET%,%PREFIX%.DEC
*WHEN ACCOUNT
*IS REVENUE
*WHEN GET(ACCOUNT="PRICE")
*IS 0
*REC(ACCOUNT=#PAYMENTS0,TIME=NEXT(0))
*IS 1
*REC(ACCOUNT=#PAYMENTS1,TIME=NEXT(1))
*IS 2
*REC(ACCOUNT=#PAYMENTS2,TIME=NEXT(2))
*IS 3
*REC(ACCOUNT=#PAYMENTS3,TIME=NEXT(3))
*ENDWHEN *ENDWHEN
*GO*WHEN TIME
*IS<>PRIOR // prior here means any period before first in %SET%
*WHEN ACCOUNT
*IS #PAYMENTS0,#PAYMENTS1,#PAYMENTS2,#PAYMENTS3
*REC(ACCOUNT=PAYMENTS)
*ENDWHEN
*ENDWHEN
Nesting of *WHEN/*ENDWHEN
WHEN / ENDWHEN structures can be nested by as many levels as desired, and in any sequence.
Example
For example, the following structure could be a valid one:
*WHEN xxx
*IS "A"
*REC(…)
*REC(…)
*IS "B"
*REC(…)
*WHEN yyy
*IS "C","D","E"
*REC(…)
*ELSE
*REC(…)
*ENDWHEN
*ENDWHEN
Note
Indentation is used just for readability purposes, and is not required by the syntax.
The *REC instruction
The *REC( ) instruction tells the program what to do once a criteria has been met. Each REC instruction generates ONE new record to be posted to the database. Each source record can generate as many records as desired, even pointing to the same destination cell.
The parameters of the REC() function specify what to modify of the original record. Any dimension member can be modified with the syntax:
{DimensionName}={member}
Example
*REC(CURRENCY="USD", ENTITY="SALESITALY")
{member} must be enclosed between double quotes and can contain the name of any dimension, enclosed between the percent sign (For example: ENTITY="IC_%ENTITY%"). In this case, the dimension name is replaced with the value of the current member for that
dimension, and not with just the dimension name.
An alternative syntax allows the rules to retrieve the member name from the value of a property of any dimension.
Example
In the following example, the entity name is read from the ENTITY property of the current member of the INTCO dimension:
*REC(FACTOR=-1, ENTITY=INTCO.ENTITY)
The option NOADD for the *REC( ) instruction
There are cases when multiple values found in a source region should generate a certain fixed value for a given destination record and such values should be the same, regardless of how many source records have been encountered. An example could be the need to assign a value of 1 to a “flag” account, if any account of the selected region has a value. This is done in a SQL logic by inserting the option NOADD anywhere in the REC statement.
Example
*WHEN ACCOUNT.TYPE
*IS "AST"
*REC(EXPRESSION=1, NOADD, ACCOUNT=" FLAG_AST")
*ENDWHEN
Using the LOOKUP value in dimension redirection
The REC() statement can use the value of a LOOKUP to define a destination member.
Example
For example this could be a valid syntax:
*REC(ACCOUNT = LOOKUP(LK1))
This generates a numeric account ID corresponding to the value retrieved by the LOOKUP.
This syntax also support a (limited) string concatenation functionality.
Example
For example:
*REC(ACCOUNT = ACC_ + LOOKUP(LK1))
If the value of the lookup is 20, the resulting destination account is ACC_20.
The FACTOR and EXPRESSION instructions
The amount to assign to the new record can be derived from the original amount with either the instruction FACTOR or the instruction EXPRESSION.
The FACTOR instruction defines a factor by which the retrieved amount is to be multiplied.
*REC(FACTOR=1/1.55)
The EXPRESSION instruction defines any formula that results in the new value to post. The formula can include regular arithmetic operators, fixed values, and the keyword %VALUE%, representing the original retrieved value.
*REC(EXPRESSION=%VALUE% + 1000)
Both FACTOR and EXPRESSION can also contain the reserved keyword LOOKUP( ), as described later.
The FLD( ) instruction inside a FACTOR or EXPRESSION
In a FACTOR or EXPRESSION it is also possible to specify a dimension property using the special format: FLD(dimension.property)
Example
*WHEN ENTITY.SCALE *IS <>"" *REC(EXPRESSION=%VALUE%*FLD(ENTITY.SCALE)) *ENDWHEN
This syntax is a slight departure from the usual format where the dimension property does not need to be enclosed inside a FLD() clause. This format has been adopted to:
Retain good performance in the logic execution
Simplify the logic validation
Allow for future extensions of logic functionality
All the syntaxes that are supported by the FACTOR and EXPRESSION instructions can be combined inside a calculation expression.
Example
*REC(EXPRESSION=%VALUE%*FLD(ENTITY.SCALE)+ GET(ACCOUNT=ACCOUNT.MYACC)*5*LOOKUP(XYZ))
The GET() instruction
You can assign a FACTOR or an EXPRESSION to a source value to calculate a new value inside a *REC( ) statement in SQL rules. The factor and the expression can include formulas using hard-coded values
(factor=1.3) or values retrieved using the LOOKUP function (factor=lookup(avg)).
In addition to the above, you can use the keyword GET( ), which allows you to use values from some other record within the selected region.
The syntax is:
GET({dimension}={member}[, {dimension}={member}]…)
Where |
Is |
|---|---|
{dimension} |
A valid dimension name |
{member} |
A valid dimension member. This can be an explicit member name like "ABC" enclosed in double quotes, or it can be derived by reading the property of the current member of any dimension. |
Example
GET(ACCOUNT="ABC")
GET(ACCOUNT=ACCOUNT.MYPROPERTY)
GET(ACCOUNT=ACCOUNT.ELIMACC,ENTITY=INTCO.ENTITY)
Example
Assume that you want to calculate the account Revenue as follows:
#Revenue = Units * Price //this is the MDX format
The SQL rules formula is:
*WHEN ACCOUNT
*IS "UNITS"
*REC(ACCOUNT="REVENUE" FACTOR=GET(ACCOUNT="PRICE")
*ENDWHEN
The GET statements also supports the concatenation of properties with trailing fixed strings as follows:
GET(dimension=dimension.property + string )
Example
GET(ACCOUNT=ACCOUNT.ID + .INP )
Note
While using the GET() instruction in SQL provides the ability to perform much more complex calculations than the system previously allowed in SQL rules, it has some limitations that might make it preferable to use MDX logic. Here is a further explanation of the limitations to using the SQL logic GET() instruction:
The value to retrieve is not queried from the database, but it is searched for in the currently selected record set. If the value is not found, it is assumed to have a value of zero. This implies that all values required by the logic must be included in the region to process. In the previously described example, the logic does not work correctly unless the account PRICE has been included in the region to scan, for example, as follows:
*XDIM_MEMBERSET ACCOUNT = Units, Price
This is fundamentally different from the way any MDX rules works: MDX can automatically retrieve from the application all necessary information, even if not included in the queried region.
The logic is not able to use the results of a previously calculated value.
Example
The following example does not work:
*WHEN ACCOUNT
*IS "UNITS"
*REC(ACCOUNT="REVENUE", FACTOR=GET(ACCOUNT="PRICE"))
*IS "REVENUE"
*REC(ACCOUNT="TAXES", FACTOR=.5)
*ENDWHEN
…Unless the two calculations are separated by a commit statement:
*WHEN ACCOUNT
*IS "UNITS"
*REC(ACCOUNT="REVENUE", FACTOR=GET(ACCOUNT="PRICE"))
*ENDWHEN
*COMMIT
*WHEN ACCOUNT
*IS "REVENUE"
*REC(ACCOUNT="TAXES", FACTOR=.5)
*ENDWHEN