Defining Formula Rows or Columns

A formula consists of a combination of grid references, mathematical functions, and arithmetic operators. A grid reference uses values from the current grid or another grid in a calculation. You can define arithmetic formulas on formula rows, columns, cells, or a range of formula cells in a grid. For example, you might want to build a formula that adds one or more rows. Or, you might want to multiply two rows, add a third row, then display the result. For a list of mathematical functions you can use in formulas, see Mathematical Functions.

Note:

By default, cells that contain missing data (#MISSING) are not treated as zero (0) in formulas, unless specified differently using the IfNonNumber property. In the case of a formula that uses division, an ERROR is returned.

Note:

When using the Financial Management database connection, the results varies, based on the settings of the MissingValuesAreZeroInFormulas (formerly MissingValuesAreZeroInFormulasInHFM) option in the JConsole.exe file. For information on JConsole, see the “Property Information” topic in the Oracle Hyperion Financial Reporting Workspace Administrator's Guide. If the property is set to one (1), the #Missing value is treated as a zero. However, if the property is set to zero (0), the #Missing value is treated as missing which results in #missing or #error values. The default setting is one (1).

To specify mathematical properties, you can use dot notation. Dot notation is a syntax that specifies properties for a grid or another property. You specify a property using a period ( . ) followed by the property name.

The following syntax describes how to define formula rows or columns:

gridname.row\column[x].property

Argument

Description

gridname

The name of the grid. Grid names cannot include spaces. For more information on grid references, see Mathematical Functions.

x

The row or column, number, and property.

For example, mygrid1.row[3].sum adds all elements in row 3 of mygrid1.

Note:

When specifying the full gridname notation, you must also indicate the row, column, or cell.

The following example returns the sum of the nth column:

  mygrid2.column[C].sum

You can omit references to the current grid, row, column, or cell. The following example returns the absolute value of the cell located at the intersection of row 5 and column B in the current grid:

  ABS([5,B])

  To define formula rows or columns:

  1. Open a report.

  2. Select a row or column in a grid.

  3. Select Insert, then Row, and then Formula or Insert, then Column, and then Formula.

  4. Select the formula row or column, or a range of formula cells.

  5. In the drop-down menu from the formula bar, select a function. For a list of mathematical functions, see Mathematical Functions.

  6. Insert your cursor in the formula text box and enter the rest of the formula.

  7. Repeat steps 4 through 6 as necessary to build your formula.

  8. Select File, and then Print Preview to see the results of your formula.