1. Database Administrator's Guide for Oracle Essbase
  2. Formula Examples for Block Storage Databases
  3. Test for #MISSING Values

Test for #MISSING Values

You can use an Essbase formula to test for #MISSING values in the database.

Assume that the outline contains a member called Commission. Commission is paid at 10% of sales when the Sales value for the current member combination is not #MISSING. When applied to a Commission member in the database outline, the following formula calculates Commission:

IF(Sales <> #MISSING) Commission = Sales * .1;
   ELSE Commission = #MISSING;
ENDIF;

If you place the formula in a calculation script, you must associate it with the Commission member as shown:

Commission(IF(Sales <> #MISSING) Commission = Sales * .1;
   ELSE Commission = #MISSING;
ENDIF;);

Essbase cycles through the database, performing the following calculations:

  1. The IF statement checks the value of the Sales member for the current member combination.

  2. If Sales is not #MISSING, Essbase multiplies the value in the Sales member by 0.1 and places the result in the Commission member.

  3. If Sales is #MISSING, Essbase places #MISSING in the Commission member.