Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

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
 

Introduction

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

  • ROUND

  • FLOOR

  • CEILING

  • ABS

  • AVG

  • LENGTH

  • SUM

  • NVL

  • CONCAT

  • STRING

  • SUBSTRING

  • INSTR

  • DATE

  • FORMAT_DATE

  • FORMAT_NUMBER

  • DECODE

  • REPLACE

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

Screenshot

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 ',

param.ComparisonValue))

Screenshot

Verifying Results

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


Ashish Harbhajanka

Add comment


Security code
Refresh

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 
        1
  2  3  4  5  6  7  8
  9101112131415
16171819202122
23242526272829
3031     

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner