![]() Previous |
![]() Next |
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.
Syntax
DEFINE name FORMULA {expression | [ datatype ][<dimensions...>]} [AW workspace] [SESSION]
Parameters
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a formula.
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. |
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.
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. |
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.
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)