Fusion Blog

EBS Blog


Contact Us

Fusion PayRoll
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive


It is a very common requirement to perform some calculations to derive some fields by applying some logic in either any of the existing data elements present in the Data Set of a BI Data Model or even based on the input parameter value passed.

While one may decide to include the calculation logic in the underlying SQL query and define a new data element there or one may wisely choose to use “Add Element By Expression” functionality.

Some of the advantages of using “Add Element By Expression” feature over creating a new element in the SQL query are as follows:

Delivered Functionality supporting most commonly used functions


Commonly used functions are available for ready use. Some of them being:


  • IF

  • NOT

  • AND

  • OR

  • MAX

  • MIN




  • ABS

  • AVG


  • SUM

  • NVL





  • DATE





Screenshot below for ready reference.


Flexibility to use any/all of the data elements and parameters used in the Data Set

As visible in the screenshot above all the data elements defined in the Data Set along with the parameters used can be utilized for deriving the expression logic.

Improved SQL Execution Time (performance perspective)

We are using the elements/parameters and constructing an expression outside the scope of SQL, which means that all the values are first fetched from the database and then the corresponding logic is applied on the retrieved values. The expression logic is not being applied in the SQL engine as such. This makes the execution a little faster.

There might be some other advantages too but the above are the ones which comes to my mind at this point of time.

Now, let-us try to understand how the “Add Element By Expression” feature works with help of a worked-out example.

Worked Out Example

For this example, we will create a very simple SQL which will fetch the “Person Number” field from the database. We would then use the “Add Element By Expression” feature which would check if the “Person Number” value fetched is less than an integer value passed using a bind variable. If the value of  “Person Number” is less than the value of bind variable the Expression Variable will return “Person Number is less then <bind variable value” else it will return “Person Number is NOT less than <bind variable value>”

Data Set SQL Query

select papf.person_number

from per_all_people_f papf

where trunc(sysdate) between papf.effective_start_date and papf.effective_end_date


Logic for Expression Element

IF( G_1.PERSON_NUMBER < param.ComparisonValue,CONCAT('Person Number is less than ',

param.ComparisonValue),CONCAT('Person Number is NOT less than ',



Verifying Results

We will pass a Value for Input Parameter (Comparison Value as 10) and verify the results

Ashish Harbhajanka

Add comment

Security code

About the Author

Ashish Harbhajanka


Oracle Fusion HCM Techno Functional Consultant with overall 10 years of Experience in software industry with 5 years in EBS HRMS and rest 5 in Fusion HCM.

My areas of intesrest in Fusion HCM include :

a) Inbound Outbound Integration using FBL/HDL or BIP/HCM Extracts.

b) Fast Formula

c) BIP Reports

d) OTBI Reports

e) RESTFUL API / Web Service Call

f) Functional Setup

g) End to End Testing

h) Regression Testing

i) Preparing COnfiguration Workbooks

j) Creating Speed Solutions

k) Preparing User Guides

l) UPK


Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  May 2022  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
  2  3  4  5  6  7  8

Enquire For Training

Fusion Training Packages

Get Email Updates

Powered by Google FeedBurner