Other rule functions

This topics describes cube rules that are not math, text, date and time, or dimension cube rules.

DB

This function indicates a reference to an aea in a cube other than the cube for which the rule is written.
Note: DB must not be used in dimension rules.

Syntax

DB(Cube,Dim1...DimN)

Where Cube is the name of the external cube and Dim1...DimN are elements from the dimensions of this cube. All arguments must be strings or functions returning strings.

As arguments for Dim1...DimN you may either specify one element from the dimension, or you may specify the dimension as a variable. To indicate the dimension as a variable, precede the dimension name with an exclamation mark as in this example.

DB('TOTSALES',!years,'Actual','USA','Total',!months,!measures)

The external cube need not have the same dimensional structure as the cube for which the rule is being written. But, if you indicate a dimension as a variable, the variable dimension must be a superset of the corresponding dimension in the cube for which the rule is being written.

Because writing rules with external references can be complicated, the Edit Cube Rule dialog has a wizard to help. Click External Reference in the Edit Cube Rule dialog to open the Cell Reference dialog.

See The Edit Cube Rule dialog.

Note: Strings used as arguments for rules functions must be enclosed in single quotes.

GETATTR

Returns the content of a specified field in an attribute table.

Syntax

GETATTR(DimName,ElemName,ATabID,FieldName)

Where DimName is the name of the dimension containing the attribute table, ElemName is the element the attribute is attached to, ATabID is the ID of the attribute table containing the attributes and FieldName is the name of the column in the attribute table containing the attributes.

Example

If the field PRODMAN for the element PV-VGA12 in attribute table 1 of the dimension prodcode contains the text "J Junek" then:

GETATTR('Prodcode','PV-VGA12',1,'PRODMAN') returns J Junek

Note: Note that the function GETATTR is analogous to an external cube reference in a cube rule. For example, if you want to write the above rule as a general rule for all products, you substitute ’!Products’ for 'PV-VGA12' in the second argument of the function. Here is an example of such a generalized GETATTR rule:

['Price']=IF(GETATTR('Products',!PRODUCT,1,'PRODMAN') @='J Junek',500,1000)

Note: Strings used as arguments for rules functions must be enclosed in single quotes.

IF

Returns one value if a logical expression is true and another value if the expression is false.

Syntax

IF(TestExpression,TrueValue,FalseValue)

Where TestExpression is a logical expression (e.g. X<Y) and TrueValue and FalseValue are numbers, strings or formulas.

The available comparative operators are:

  • N1 > N2 greater than
  • N1 < N2 less than
  • N1 @= N2 equal
  • N1 <> N2 not equal to
  • N1 >= N2 greater than or equal to
  • N1 <= N2 less than or equal to

The available logical operators are:

  • AND is the logical AND: X<Y AND X>Z
  • OR is the logical OR: X<Y OR X>Z
  • NOT is the logical NOT: NOT X<Y
Note: The values used in conjunction with logical operators must be enclosed in brackets.

Example

IF(1<2, 1, 'OK') returns "1"

IF(1>2, 1, 'OK') returns "OK"

IF((2>1) AND (2<3),'OK','Not OK') returns "OK"

IF(NOT (2<1),'OK','Not OK') returns "OK" .

Note: Numbers entered as arguments in rules must be entered with a period as the decimal separator. Commas must not be used as thousands separators. This is true whatever the decimal separator indicated under your Windows international settings.

STET

The STET function is used to cancel the effect of a following rule. For example, if you have a rule written in the form:

['Sales'] = expression;

but you want Sales to remain an input value for Germany; you can deactivate the rule for Germany by writing an additional rule:

['Sales','Germany'] = STET

Note: If you use STET in connection with an accelerator, note that STET principally refers to the actual value of the current recalculated cell. If you activate an accelerator, which affects a rule used by STET, you must write another accelerator for those cells affected by STET.

The alternative syntax STET( ) is no longer supported.

Example

Cube Sales

['Margin'] = if (['units'] <> 0, ['units']*['price'], STET)

has to have these accelerators:

SALES['Units'] > ['Margin']

SALES['Margin'] > ['Margin']