Syntax of rules

You can use these arguments for a dimension rule:

  • Element names
  • Numerical constants
  • The arithmetical operators + - * /
  • Parentheses
  • A number of mathematical functions

    See Rules functions

  • Logical functions (IF statements)

Element names must be enclosed in square brackets and single quotes. For example, ['Price'], ['Argentina']. You can use only elements from the dimension for which you are writing the rule.

Arithmetical operators are evaluated with standard algebraic priority. That is, multiplication and division are evaluated first and then addition and subtraction. The order of calculation can be forced with parentheses.

Note: 

Dimension rules do not take into account calculations made in other dimensions. This leads to problems in forcing the order of calculations. For example, consider the simple rule ['Sales'] = ['Price']*['Units']. This will work correctly for cube cells where all other defining elements are base elements.

However, a problem arises if you want to calculate the Sales for a calculated element in a separate dimension. For example, you may have a Region dimension containing the element Central Europe. Central Europe is a calculated element, defined as the sum of ['Germany'] and ['France']). There is no way to force the order of calculation with regard to elements from other dimensions so a rule will calculate this cell incorrectly. It will multiply the sum of the Units for France and Germany by the sum of the Prices for France and Germany.

Mathematically, the problem can be expressed as:

['Sales', 'Central Europe']= (['Units','Germany']+['Units','France'])*(['Price','Germany']+['Price','France'])

What is required is to calculate Sales independently for France and Germany and to add these two numbers to arrive at Sales for Central Europe. Mathematically:

['Sales', 'Central Europe']= (['Units','Germany']*['Price','Germany'])+(['Units','France']*['Price','France'])

To overcome this problem, write the rules as cube rules which are written for a specific cube. Cube rules take into account all the dimensions of a specific cube and allow you to force the order of calculation.

The rules compiler ignores both blanks and line breaks. You may insert spaces and line breaks to make a rule easier to read and understand.

The logical statement IF has the format:

IF(Conditional statement, expression 1, expression 2)

The conditional statement is evaluated. If it is TRUE, then the results of expression 1 are returned. If it is false, the results of expression 2 are returned.

In the conditional statement these comparison operators can be used:

  • > Greater than
  • < Less than
  • = Equal to
  • @= Comparison of strings
  • >= Greater than or equal to
  • <= Less than or equal to
  • <> Not equal to

Strings can be used as arguments for dimension rules. They must be enclosed in single quotes. For example:IF(['Sales']>1000, 'OK','Not OK')

Null is returned as the result of division by an empty cell.

Element names are separated by commas (,).