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

Search Courses

Introduction

By now I assume most of us are aware how to create a BI Publisher Report in Oracle HCM Cloud Application using a SQL Query, but at times there is a need to have a BI Publisher Report to be created from an OTBI Subject Area.

 

Advantages of using OTBI Subject Area in Fusion BI Report:

  1. An OTBI Analysis takes care of security internally (meaning only the records which an individual is supposed to view are displayed)
  2. OTBI is comparatively simple as one can create reports by drag-drop feature (in case of simple reports)
  3. Creating a BI Publisher Report from OTBI Analysis gives us the flexibility to have design the Report Output as per specific business needs
  4. Many a times Business Users always want to schedule/run all kinds of reports be it BIP or OTBI from a single work-area. There are two ways to accomplish this namely:
  1. Create Custom Payroll Flow Pattern
  2. Create Custom ESS Jobs

 

Both of the above options (at this point of time as per my limited knowledge) are supported only if there exists a BI Publisher Report (.xdo). So for all the above reasons it does makes sense to explore and investigate if we can have a BI Publisher report created using Oracle BI EE as a data source.

 

Creating a Simple SQL Query Based Data Model using Oracle BI EE as Data-source

For this example we would try to create a very simple report comprising of the following fields:

  1. Person Number
  2. Display Name
  3. Full Name
  4. Known As

 

We would use “Workforce Management – Person Real Time” subject area and also ensure that apply one condition to ensure that we only pick the Person Name which has Name Type as “GLOBAL”..

Additionally, we would also have a mandatory parameter (Person Number) configured too which would have a Person Number LOV attached to it.

 

So let’s get started.

 

First Step

As a first step we would need to login to application with valid credentials (HCM_IMPL2) user for this example and navigate to following location:

 

Navigator-> Tools -> Reports and Analytics -> BI Catalog -> Create New Data Model

Once these we would need to choose “SQL Query” as the Data Set option and select “Oracle BI EE” as the data source.

One may use the Query Builder option and pick choose the database fields you are interested in:

Once done the Data Set Query would appear as shown in snapshot below ( We have added a where clause of Name type ‘GLOBAL’ and also a bind variable named personnumber)

 

 

The SQL Query used is below:

SQL Query

select "Person Details"."Person Number" as "Person Number",

        "Person Names"."Display Name" as "Display Name",

        "Person Names"."Full Name" as "Full Name",

        "Person Names"."Known As" as "Known As"

from "Workforce Management - Person Real Time"."Person Names" "Person Names",

        "Workforce Management - Person Real Time"."Person Details" "Person Details"

where "Person Names"."Name Type" = 'GLOBAL'

and    "Person Details"."Person Number" = :personnumber

Also we have a parameter which has a LOV attached to it.

LOV Query for Person Number

select "Person Details"."Person Number" as "Person Number"

from "Workforce Management - Person Real Time"."Person Details" "Person Details"

order by   "Person Details"."Person Number" asc

 

Creating a BI Publisher Report in Fusion for OBIEE Model

While I hope that most of you (or in-fact some of you who are already acquainted with BI Publisher Report Creation from one of the previous article) would be able to create the below BIP Report from SQL query for those who would like a quick refresher please feel free to read the article here.

 

We would next need to create a BI Report using this Data Model and the final output would appear as below:


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

<<  Mar 2024  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
      1  2  3
  4  5  6  7  8  910
11121314151617
18192021222324
25262728293031

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner