Oracle Fusion - OTBI Pivot Table - Display as a Percentage

Question

Hi,

Looking for some guidance on an OTBI Report using a Data Model sql where the report layout is a Pivot Table and displaying Percentages.

Oracle Fusion - Report and Analytics

I have created a Data Model sql to analyse Payables Invoice Payments to determine whether they have been paid within N business days from a date.

Subject Areas and Analysis are not being used as I am initializing a custom function using the WITH statement prior to the main SQL statement.

The custom function can then be used to generate a date N business days from a date, plus allows for Public Holidays. Public Holidays are pre-defined in a Flex Value Set.

The sql does an initial grouping by Period and Paid_Target (On-time or Late) on SUM(invoice_amount).

The Pivot Table Layout can be created either using the Wizard or manually to display the SUM(invoice_amount) as a numeric value, no problems there.

But I would also like to include a % column to represent the SUM(invoice_amount) values as well.

I cannot find anywhere in the Report Layout that allows me to select the display as a Percentage.

There is Formating > Percentage, but that just adds a % character.

I have checked both the Data Source/Properties and the Pivot Table/Formula sections.

Is this achievable using the in-built Tools In Oracle Fusion - Report and Analytics?

Or do I have to create a custom XML template from sample XML data and use that, is that even possible ?

Rgds

Peter Brown

Oracle NZ

OTBI_Pivot_Table.docx

Riyaz Ali-Oracle · Accepted Answer

Try the below options:* Use a Formula in the Pivot Table

Instead of relying on formatting, create a calculated measure in the Pivot Table.

Example formula:

sql

SUM(invoice_amount) / SUM(SUM(invoice_amount)) OVER (PARTITION BY Period) * 100

This ensures that each row displays the percentage relative to the total.

2. Apply a Custom Calculation in the Data Model SQL

Modify your SQL query to include a percentage calculation before passing data to the Pivot Table.

Example:

sql

SELECT

Period,

Paid_Target,

SUM(invoice_amount) AS Total_Amount,

(SUM(invoice_amount) / SUM(SUM(invoice_amount)) OVER (PARTITION BY Period)) * 100 AS Percentage

FROM Payables_Data

GROUP BY Period, Paid_Target;

This ensures the percentage is calculated before reaching the Pivot Table.

3. Use the Pivot Table Formula Section

If your Pivot Table allows custom formulas, define a percentage calculation using the available functions.

Meanwhile, Please review the below as it might be helpful:

About Pivot Tables

Showing Percentages in a Bar Pivot — Cloud Customer Connect

Riyaz Ali-Oracle · Accepted Answer

@Pbrown.Nz-Oracle : Please mark this thread as answered in case your issue is resolved as it might help other customers.