nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle AI and Analytics

Child Item

BIP - FAST FORMULA FUNCTION IN BI REPORT

Devang Bharad

Functionality to Call Fast formula from BIP Report

Find more posts tagged with

Submitted

Oracle Fusion Analytics

BI Publisher

Status: Submitted

Comments

Nagendra Yarramsetti

Hi, I am looking for same requirement, please let me know if someone have solution for it.

Shankar-Oracle

I do see that there is a pending ER that exist for this issue: 27067097

You can access the above ER, as you would for any other bug.

Please note that at the moment, you may not be able to view the full details in the ER (as it is not published yet).

Please raise an SR, referring to this community link and requesting your SR to be linked to this ER.

If the above ER gets implemented, you will receive the enhancement automatically. After attaching your SR to the ER, the SR will be closed.

Priyanka Sachdeva

Have we received solution for this? Can anybody please share sample code for calling Fast Formula via BIP ?

Devang Bharad

Dear @Priyanka Sachdeva @Nagendra Yarramsetti

I found the solution. Below is a sample code snippet for calling a formula from a BI Publisher (BIP) report.

To achieve this, you need to use the WITH FUNCTION clause in PL/SQL. The following example demonstrates how it can be implemented:

WITH

FUNCTION F_VALUE (PARAM_1 IN NUMBER,

PARAM_2 IN NUMBER,

PARAM_3_DATE IN DATE,

PARAM_4 IN VARCHAR2

) RETURN VARCHAR2

AS

L_FORMULA_ID NUMBER;

L_INPUTS FF_EXEC.INPUTS_T;

L_OUTPUTS FF_EXEC.OUTPUTS_T;

L_VALUE  VARCHAR2 (100);

BEGIN
select formula_id into L_FORMULA_ID from ff_formulas_f where formula_name = 'FORMULA_NAME'

and sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
FF_EXEC.INIT_FORMULA(L_FORMULA_ID, sysdate, L_INPUTS, L_OUTPUTS);
FOR l_in_count IN L_INPUTS.FIRST .. L_INPUTS.LAST

LOOP
FF_EXEC.RUN_FORMULA(L_INPUTS, L_OUTPUTS);
FOR l_out_count IN L_OUTPUTS.FIRST .. L_OUTPUTS.LAST

LOOP

IF (L_OUTPUTS (l_out_count).NAME = PARAM_4)THEN

select L_OUTPUTS (l_out_count).VALUE into L_VALUE from dual;

Exit;

END IF;
END LOOP;
return L_VALUE;

SELECT

F_VALUE (PARAM_1, PARAM_2,PARAM_3_DATE, PARAM_4) OUTPUT_NAME

FROM '<TABLE_NAME>'

Rahul Bhavikatti

Thank you for sharing this solution. We are trying to call Fast Formula results directly inside a BIP Data Model SQL query on Oracle Fusion HCM Cloud to display calculated values like OT hours, regular hours, and pay values in a report.
However, after reviewing the above approach, we found that it does not work for our formulas due to the following reasons:
1. Our Payroll formulas (Timecard Calculator type) require ELEMENT_ENTRY_ID and PAYROLL_RELATIONSHIP_ID contexts which are only available during a live Payroll Run — when called via FF_EXEC from BIP, these return 0 and the formula immediately exits with an error.
2. Our Time formulas (Time Calculation Rule type) require HWM engine contexts (HWM_FFS_ID, HWM_RULE_ID etc.) and use array-type inputs/outputs which FF_EXEC does not support.

Could you please confirm if your solution was tested with Timecard Calculator or Time Calculation Rule formula types specifically? And if yes, how did you handle the missing engine contexts inside the WITH FUNCTION block?

Rahul Bhavikatti

Hello Experts, any idea on the above query?

Top Leaders

Top Ideas

Clarification/Correction of Identifying PO Field in Payables Invoices-Transactions Real Time SA OTBI
Issue Description The Identifying PO field is a header-level attribute, but it is currently displayed under Distributions → Invoice Distribution Details → Purchase Order Information in the Payables Invoices – Transactions Real Time subject area. When an invoice has multiple POs, the same header-level PO can be repeated…
Provide Email Delivery and Bounce Status Tracking for BI Publisher Bursting Emails
BI Publisher bursting currently reports an email delivery as successful once the email has been processed and sent from Oracle. However, there is no customer-accessible functionality in Oracle Fusion/BI Publisher to determine whether the email was subsequently delivered, rejected, bounced, or returned as undeliverable by…
Copy functionality for reports to be granted independently within report security permissions
ERP: Ability to provide copy functionality for reports within report security permissions or manage privileges without providing full edit / write access. Currently, it does not appear that 'copy' access can be given independently.
FDI report/object security to grant Copy as an independently grantable permission
FDI: Ability provide copy functionality for reports within report security permissions or manage privileges without providing full edit / write access. Currently, it does not appear that 'copy' access can be given independently.
Restriction of Data Source in reports and analytics while creating Data Model
Currently any user account having access BIPDataModelDeveloper role is providing the ability to see any sensitive information in the Oracle fusion irrespective of data restrictions placed in the front end through role assignments. User while creating a data model can see any HCM data like (Payroll details, DOB, SSN, etc.)…