Oracle Cloud EPM reports formulas – reference rows and columns

Sarah Ansell

Brovanture Consultant

In Oracle Cloud EPM reports, formulas can be used to perform simple calculations, such as a variance calculation. Formulas require references to rows and columns in a grid to calculate a result. The syntax for referencing a row or a column can be found here: Row, Column, or Cell Reference Arguments.

While many find creating a report variance a simple task in Oracle Cloud EPM Reports, it’s often overlooked that you can reference sub-rows and sub-columns within a formula. For example, let’s suppose you have a basic grid with a Forecast column and a Budget column defined, and you want to display two years for each Forecast and Budget.

Let’s look at this simple example:

Suppose you wish to add a variance per year into the report. Now, you’ll need to reference each year; otherwise, you’ll get the variance from the two years’ Budget minus two years’ Forecast in totality. Many report designers forget that you can reference sub-rows and instead split the column segments into four separate columns. This is not ideal because when referencing years by substitution variable, you would need one for each year, rather than a single substitution variable with the year range. This becomes messy as you scale up the number of years!

Reference sub-columns or sub-rows in Oracle Cloud EPM Reports with the following syntax:

• [A(A)] : The first column in display mode from the grid column segment A
• [A(A:C)] : The first three columns (A,B, and C) in display mode from the grid column segment A
• [1(2)] : The second row in display mode from the grid row segment 1
• [1(1:3)] : The first three rows (1,2, and 3) in display mode from the grid row segment 1

Returning to our example, let’s see how this looks in practice:

In this instance:

• Segment E represents the FY24 variance
• Segment F represents the FY25 variance

During run-time, the report will expand the columns, and the formula will reference the expanded columns to produce the dynamic variance columns.

It’s challenging to discover this feature because there isn’t a designer view that displays the grid with its sub rows and columns defined using this naming convention, as the segments are only expanded at run-time.

This information is somewhat buried in the Oracle documentation which itself doesn’t provide a huge amount of explanation. Therefore, I thought I would write a short reminder for those working with Reports.

Until next time

Sarah

You can read more of Sarah’s blogs HERE