Skip Headers
Oracle® OLAP DML Reference
10g Release 2 (10.2)

Part Number B14346-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

$NATRIGGER

The $NATRIGGER property specifies values for Oracle OLAP to substitute for NA values that are in a dimensioned variable, but not in the session cache for the variable (if any). To calculate the values, Oracle OLAP takes the steps described in "How Oracle OLAP Calculates Data for a Variable with NA Values". The results of the calculation are either stored in the variable or cached in the session cache for the variable as described in "How Oracle OLAP Determines Whether to Store or Cache Results of $NATRIGGER".

Syntax

You add or delete a $NATRIGGER property to the most recently defined or considered object (see DEFINE and CONSIDER) using a PROPERTY statement:

Arguments

value

A TEXT expression that is the value of the property. The text can be any expression that is valid for defining a formula

Notes

How Oracle OLAP Calculates Data for a Variable with NA Values

When calculating the data for a dimensioned variable, Oracle OLAP takes the following steps for each cell in the variable:

  1. Is there is a session cache for the variable.

    • Yes. Go to step 2.

    • No. Go to step 3.

  2. Does that cell in the session cache for the variable have an NA value.

    • Yes. Go to step 3.

    • No. Go to step 7.

  3. Does that cell in variable storage have an NA value.

    • Yes. Go to step 4.

    • No. Go to step 7.

  4. Does the variable have an $AGGMAP property?

    • Yes. Aggregate the variable using the aggmap specified for the $AGGMAP property and, then, go to step 5.

    • No. Go to step 6.

  5. What is the value of the cell after aggregating the variable?

    • NA, go to step 6.

    • Non-NA, go to step 7.

  6. Does the variable have a $NATRIGGER property?

    • Yes. Execute the expression specified for the $NATRIGGER property and, then, go to step 7.

    • No. Go to step 7.

  7. Calculate the data.

  8. Apply the NAFILL function or the NASKIP, NASKIP2, or NASPELL options, as appropriate.

Making NA Triggers Recursive or Mutually Recursive

You can make NA triggers recursive or mutually recursive by including triggered objects within the value expression. You must set the RECURSIVE option to YES before a formula, program, or other $NATRIGGER expression can invoke a trigger expression again while it is executing. For limiting the number of triggers that can execute simultaneously, see the TRIGGERMAXDEPTH option.

Using $NATRIGGER with Composites

You can set an $NATRIGGER expression on a variable that is dimensioned by a composite, but Oracle OLAP evaluates the $NATRIGGER expression only for the dimension-value combinations that exist in the composite.

$NATRIGGER Ignored by EXPORT and AGGREGATE

The AGGREGATE command and the AGGREGATE function ignore the $NATRIGGER property setting for a variable during an aggregation operation. The statements fetch the stored value only, and do not invoke the $NATRIGGER expression. The $NATRIGGER property remains in effect for other operations.

In executing an EXPORT (to EIF) statement, Oracle OLAP does not evaluate the $NATRIGGER property expression on a variable when it simply exports the variable. However, Oracle OLAP does evaluate the $NATRIGGER property expression when the variable is part of an expression that Oracle OLAP calculates during the export operation.

Examples

Example 6-10 Triggering Aggregation Using an $NATRIGGER

Instead of specifying the AGGREGATE function in every statement that you want to return aggregate data, you use the $NATRIGGER property to cause the aggregation to occur when a cell in the variable has an NA value. To use $NATRIGGER for this purpose, assign an $NATRIGGER property to the variable with a call to the AGGREGATE function specified as the $NATRIGGER expression.

The following statements add the $NATRIGGER property to the sales variable, so that unsolved data is aggregated using the sales.aggmap aggmap.

CONSIDER sales
PROPERTY '$NATRIGGER' 'AGGREGATE(sales USING sales.aggmap)'

Example 6-11 Adding an $NATRIGGER Property to a Variable

The following statements define a dimension with three values and define a variable that is dimensioned by the dimension. They add the $NATRIGGER property to the variable, then put a value in one cell of the variable and leave the other cells empty so their values are NA. Finally, they report the values in the cells of the variable.

DEFINE d1 INTEGER DIMENSION
MAINTAIN d1 ADD 3
DEFINE v1 DECIMAL <d1>
PROPERTY '$NATRIGGER' '500.0'
v1(d1 1) = 333.3
REPORT v1

The preceding statements produce the following output.

D1            V1
--------- ----------
        1     333.3
        2     500.0
        3     500.0