Previous
Previous
 
Next
Next


DEFINE FORMULA

The DEFINE command with the FORMULA keyword adds a new formula object to an analytic workspace. You define a formula to save an expression. A formula can take the place of an expression you use repeatedly. The name of the formula takes the place of the text of the expression. Oracle OLAP does not store the data for a formula in a variable; instead it is calculated at run time each time it is requested.


See also:

"OLAP DML Formulas"

Syntax

DEFINE name FORMULA {expression | [ datatype ][<dimensions...>]} [AW workspace] [SESSION]

Parameters

name

The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.

FORMULA

The object type when you are defining a formula.

expression

The calculation to be performed to produce values when you use the formula. It can be any valid expression, including a constant or the name of a variable as described in OLAP DML Expressions.

You can specify an expression for a formula when you define it or after you define using an EQ statement. When you define a formula without specify an expression, a formula returns NA with the specified data type.


Note:

Oracle OLAP does not automatically convert text in a formula to uppercase.

datatype

The intended data type for the formula when you do not specify a value for expression. You can use any of the data types that apply to variables. If you do not specify a value, the data type is determined at run time.

When you include an expression in the formula definition, DEFINE automatically determines the data type for a formula defined using expression. Later, when you add the expression using an EQ statement, its data type should match the type you specify now. When it does not, DEFINE converts the output to the specified type.

dimensions

The dimensions of the formula. Enclose the list in angle brackets. The dimensions argument is optional. When the formula is a single-cell value, you do not specify any dimensions. Also, when you include an expression in the definition, you do not specify a value. DEFINE automatically determines the dimensions.

However, when you do not include an expression in the definition, you must specify the dimensions. When you add the expression later using an EQ statement, the expression must have the same dimensions as the formula definition. When it does not, DEFINE forces the output to have the specified dimensions.


Restriction:

You cannot define a formula that is dimensioned by a composite.

AW workspace

The name of an attached workspace in which you want to define the formula. When the formula is dimensioned, it must be defined in the same workspace as its dimensions. For general information about this argument, see the main entry for the DEFINE command.

SESSION

Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.

Usage Notes

Effect of Changing the Characteristics of Objects Used by a Formula

When you change the name, data type, or dimensions of any of the objects used by a formula, the formula is not automatically updated. The formula causes an error when objects it refers to have been deleted or are now the wrong data type.

Storing Complex Expressions and Calculations

To define a very complex calculation, you can define a program that uses a RETURN statement to return a value. You can then use the program as a function wherever you would use an expression or formula.

Examples

Defining a Formula

This example adds a formula named sales.diff to an analytic workspace. This formula calculates the percent difference between total sales for the current year and last year.

The statements

DEFINE sales.diff FORMULA LAGPCT(TOTAL(actual year) 1 year)
DESCRIBE sales.diff

produce the following definition.

DEFINE sales.diff FORMULA DECIMAL <year>
EQ lagpct(TOTAL(actual year) 1 year)