1. Designing with Reports for Oracle Enterprise Performance Management Cloud
  2. Working with Grids
  3. Using the Formula Bar
  4. Example Report with Formulas

Example Report with Formulas

Figure 4-32 shows the use of the Sum and Average functions to summarize data for the year. It uses the Narrative Reporting sample model as its data source. You must create the sample application in order to follow along with the steps in the example.

Figure 4-32 Formula That Summarizes the Data for the Year


figure showing months, sum, and average

The report resulting from this formula will include:

  • Monthly total figures for the "Segments" 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 Reports:

  1. Create a grid with "Segment " for the row and "Fiscal Calendar" for the column.

  2. Select the cell that contains "Fiscal Calendar", and then click select members to bring up member selection.

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

  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 "Fiscal Calendar" 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 [A,1])

  • The second formula column calculates the average of the months for the "Fiscal Calendar" 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, 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, click formula, select Custom Formula , 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.