Formula Syntax and Terminology

 

Formulas take the general form:

<LHS> = <RHS>;

[Note the semicolon (;) that completes the expression.]

Where

 

DESCRIPTION

LHS

Left-hand side range, for the data to be calculated by the formula (i.e., where data will appear in the Cube);

RHS

Right-hand side, which will either be a constant or a calculation performed on data from the range of a specified originating Cube (may be the same Cube).

 

Notes

as will be demonstrated, the LHS can be constructed using the Build Range Reference dialog box; and the RHS can be constructed, using the Build Cube Reference dialog box.

A Numeric Formula is any valid arithmetic expression composed of one or more of the items in the table:

 

Example

Numeric Constants

e.g.: 2, 3.5, 2.5E10, etc.

Numeric Operators

e.g.: +, -, *, /

Parentheses

( )

Specific Functions

e.g.: round, abs(x), IF, etc…

 

Numeric Constants are the simplest components of a numeric formula. A numeric constant consists of digits, an optional leading sign, and an optional decimal point.

 

Example

Valid Numeric Constants

6       -2       5.0

Invalid Numeric Constants

0a      0-       3..0

 

Numeric Operators are simple mathematical expressions:

 

Operator

Addition

+

Subtraction

Multiplication

*

Division

/

Exponentiation

^

 

If different numeric operators are used in an expression, the order of computation is:

 

1st:

Exponentation

2nd:

Multiplication and Division

3rd:

Addition and Subtraction

 

Parentheses may be used to force a different order of computation and are used in traditional algebraic notation. For example, 2*3+4 is the same as (2*3)+4 which equals 10. Whereas, 2*(3+4) equals 14.

 

Specific Functions are provided by PowerOLAP® for additional computation capabilities. They have a wide variety of uses from simple sums to trigonometric and financial functions to logical constructions (an example of a logical function, an IF statement to be discussed inCube Formulas topic of this section). All are described in the section Advanced Reference Materials.

 Notes

The full range of PowerOLAP Cube Formula Functions is explained in detail in the section for Advanced Reference Materials.