1. User's Guide
  2. Working With Grids
  3. Using the Formula Bar
  4. Example Report with Formulas

Example Report with Formulas

Figure 2-4 shows the use of a formulas to summarize data for the year.

Figure 2-4 Formula That Summarizes the Data For the Year


Formula to summarize data for the year.

The report resulting from this formula will include:

  • Monthly total figures for the "Product" dimension

  • A total for all months calculated using the Sum function

  • An average amount per month, calculated using the Avg function

To create this example using Oracle Hyperion Financial Reporting Web Studio:

  1. Create a grid with "Product " for the row and "Year" for the column.

  2. Double-click "Year" to bring up member selection.

  3. Place all twelve months in the selected pane and remove "Year."

  4. Insert two formula columns: one for the "annual total " and another for the "average monthly amount."

In this example:

  • The first formula column that falls outside the months of the "Year" member contains a Sum function that adds the figures for each month. Since all months are defined in one cell, the reference is to that cell location.

    The formula is:

    Sum(Cell[A1])

  • The second formula column calculates the average of the months for the "Year"member. Because there are 12 months in a year, the Avg function adds all monthly totals and divides the total by 12.

    The formula is:

    Average(Cell [A,1])

In general, there are two ways to specify a data formula in a grid, a row or column formula, or a cell formula:

  • Row/Column—Apply a formula to the entire row or column, relative to each cell in that row or column.

    To define the formula, select the formula row or column header to highlight the entire row or column, and then enter the formula in the formula bar. There are some performance benefits when applying row/column formulas.

  • Cell—Apply a formula only to cells in a formula row or column.

    To define the formula, select the cell, then select Use a Custom Formula in the Cell Properties, and then enter the formula in the formula bar.

    If the cell intersects a formula row and formula column, you can select to use the row formula or the column formula as the cell formula. For more information, see Calculating at the Cell Level.

If a formula repeats for each cell in a row or column, use a "Row/Column" formula. If different formulas are performed on each cell, use a "cell" formula.