Oracle Financial Reporting Studio: Advanced Solutions for Dynamic Reports

The Requirement

A report user requested that the P&L show account names that adapt to the data being shown. For instance, this meant making the bottom line to show either “Net Income” or “Net Loss” depending upon the value presented:

Inherently, FR cannot pull dynamic row headers this way automatically. The options for row headers are either Member Name, Alias, or Custom Heading. As it stands, there can only be one static header for each row.

However, I am no stranger to using complex processes in FR to produce streamlined reports. I tried text formulas, formatting, and other unsuccessful methods. At last, FR’s conditional suppression functionality provided a workaround to produce these bespoke reports.

Tailoring the Report to the Requirement

Reports with One Column

Essentially, we will duplicate the data rows and suppress, or hide, the rows with headers that aren’t applicable. In this example, this means that there will be two data rows (“Net Income” and “Net Loss”). Whichever row has the incorrect header will be suppressed.

The first step is to duplicate the row.

Then, use the Custom Heading option to name the first row “Net Income” and the second row “Net Loss”.

Then, each row will have conditional suppression applied to it in unique ways. The “Net Income” row will use the following logic:

Note: This logic means that if the given data value is less than zero, then the row is suppressed and will not show on the report. It is assumed that for zero values, “Net Income” is the preferred row name.

The “Net Loss” line uses the inverse logic:

Note: This logic means that the row will be suppressed if the data value is anything other than a negative number.

The result is a report that tells the story of the data and provides direct insight to stakeholders. The user simply runs the report as usual and FR will do the rest. If the report is run on a period that had $12 million in Net Income, the bottom line will show:

On the other hand, if the same report is run on a period with a $(10.5) million Net Loss, the bottom line is displayed as:

This streamlined report will certainly be fitting for accessible, efficient reporting.

Reports with Two Columns

For reports that show year-over-year, previous period, or other comparisons, this logic is still possible. Expanding the previous example, the four row header options now are “Net Income”, “Net Loss”, “Net Income (Loss)”, and “Net (Loss) Income”. Duplicate the row so that there are four identical rows and then follow the conditional suppression methodology below.

“Net Income” row:

“Net Loss” row:

“Net Income (Loss)” row:

“Net (Loss) Income” row:

Additional Considerations

Formula Rows:

• This logic can be used on formula rows.
• If this logic is applied to a data row and that data row is used in a formula elsewhere:
  • The formula should reference all versions of this row by using the Sum() function. The formula will only use the row that is NOT suppressed.
• If this logic is applied to a formula row and that row is used in a formula elsewhere:
  • The second formula should only reference ONE of the versions of the first formula. The second formula will use all versions of this row, suppressed or not.

Additional Use Cases:

• This process can be used for other account types. Some examples are:
  • “Gain” versus “Loss”
  • “Profit” versus “Loss”
  • “Retained Earnings” versus “Accumulated Deficit”
  • “Income” versus “’Expense”
  • “Expense” versus “Benefit”

The Result: A Bespoke Report

In many cases, FR can be both the protagonist and the antagonist of building reports. While end users have polished financial statements, report builders may grow weary of FR’s complexity and limitations. Nevertheless, with some creativity, these complexities can lead to intelligent solutions.