Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Oracle Fusion - OTBI Pivot Table - Display as a Percentage

Accepted answer
54
Views
3
Comments
Pbrown.Nz-Oracle
Pbrown.Nz-Oracle Rank 1 - Community Starter
in Oracle Transactional Business Intelligence

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
Tagged:

Best Answers

  • Riyaz Ali-Oracle
    Riyaz Ali-Oracle Rank 6 - Analytics Lead
    Answer ✓
    Try the below options:
    1. 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
    Riyaz Ali-Oracle Rank 6 - Analytics Lead
    Answer ✓

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

Answers

  • Pbrown.Nz-Oracle
    Pbrown.Nz-Oracle Rank 1 - Community Starter

    Hi Riyaz,

    I was able to add the OVER PARTITION clause to my Data Model query and works exactly how I want.

    Had to modify with NULLIF, due to a DIVISOR = 0 error, which is due to the underlying data.

    SUM(INVOICE_AMOUNT)/SUM(NULLIF(SUM(INVOICE_AMOUNT), 0)) OVER (PARTITION BY PERIOD_NAME)*100

    Thank you for your timely response.

    Regards

    Peter