In 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 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 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 it's 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.