Additional expert rule examples
Dimension constants
You can specify the dimension constants that are created for the item, location, and period in SCP 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. |
SCP
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')