Additional expert rule examples

Dimension constants

You can specify the dimension constants that are created for the item, location, and period in with calculation rules. This can be used to specify all the elements in a dimension to receive the value of the constant element, or more appropriately to specify a target calculation for the dimension constant (filter text).

For example, to specify that all locations must receive the value of the LCONST location constant, the expert rule definition text is['LCONST'].

To specify a calculation with a target of the constant element, the filter text is 'LCONST'.

Null values

It is recommended that you do not populate OLAP with 0 values where no value must exist. Instead, use a definition for null: #NA.

For example, to specify a value of null for a calculation rule (or filtered elements within a calculation), the expert rule definition text must be: #NA

STET command

For some filtered elements of a calculation you can configure the OLAP rules engine to use the existing stored value (and not calculate for that filter/subselection). In this case, you can use the STET (Latin for "let it stand") command. Based on this command OLAP does not calculate the target dimension and retains the existing value.

GETATTR command

This function allows you the retrieve an attribute within one of the cube dimensions, for example an item, location, or period attribute that can be used within the calculation.

Syntax:

GETATTR(DimName,ElemName,ATabID,FieldName)
Attribute Description
DimName Dimension name: item, location, period, measure, and scenario.
ElemName An element name.
ATabID Attribute table number (default "1").
FieldName Attribute field name.

For example, to retrieve the HPreviousPeriod (Hierarchical Previous Period) attribute for a period element within period dimension:

GETATTR(%periods, !%periods,1,'HPreviousPeriod')

DB command

This function allows you to query a specific value in the cube or to reference an alternative cube with similar dimensions.

Syntax:

DB(Cube,Dim1Element,Dim2Element,...,DimxElement)
Attribute Description
Cube The name of the external cube.
Dim1Element An element of the first dimension of that cube.
Dim2Element An element of the second dimension of that cube, and so on.

has five standard dimensions (items, locations, periods, measures, and scenarios) with an additional item and location dimensions for each pivot attribute defined for that cycle period module (or cube). This must be specified in the DB command, and in the required order. If the current cube and reference cube have the same dimensions, you can specify the argument for a particular dimension as a variable. So, the calculation covers all elements, in the dimension, that the cubes share. To specify a dimension argument as a variable, specify the dimension name as the argument, preceded by an exclamation mark : !items.

For example, to retrieve the value of measure NSO_NET_SUPPLY from the cube for the period element represented by the attribute HPreviousPeriod and for matching elements of other dimensions:

DB(%cube, !%items, !%locations, GETATTR(%periods, !%periods,1,'HPreviousPeriod'), !%scenarios, 'NSO_NET_SUPPLY')
Note:  For details on OLAP cube rule syntax and commands, see BI documentation, available at the Xtreme Support portal at http://www.infor.com/inforxtreme.