Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF


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".


When you want to trigger the aggregation of a variable, you can use the $AGGMAP property rather than the $NATRIGGER property.


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



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

Usage 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.


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 (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.


Example 4-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.

v1(d1 1) = 333.3

The preceding statements produce the following output.

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