Create Fusion HCM OTBI Detail Report

Creating a Fusion HCM OTBI Detail Report is one of the most practical skills every Oracle HCM consultant must master. In real implementations using Oracle Fusion Cloud HCM, clients frequently ask for detailed employee-level reports—whether it’s headcount, salary breakdown, or assignment history. OTBI (Oracle Transactional Business Intelligence) becomes the go-to tool for such reporting because it provides real-time access to transactional data without needing complex data extraction.

In this blog, I’ll walk you through how consultants actually design OTBI detail reports in live projects, including navigation, subject area selection, filters, and best practices.

What is OTBI Detail Report in Oracle Fusion?

An OTBI Detail Report is a transaction-level report built using Oracle Transactional Business Intelligence that displays granular data directly from Fusion tables via subject areas.

Unlike summary reports, detail reports show row-level data, such as:

Employee Name
Assignment Number
Department
Salary
Location

Key Point from Implementation Perspective

In projects, OTBI detail reports are used for:

HR audits
Payroll validation
Data reconciliation before go-live
Manager self-service reporting

Key Features of OTBI Detail Reports

1. Real-Time Data Access

OTBI directly fetches data from Fusion transactional tables. No delay like BI Publisher extracts.

2. Drag-and-Drop Report Creation

Consultants can quickly build reports without coding.

3. Subject Area-Based Reporting

Each module (Core HR, Payroll, Absence) provides predefined subject areas.

4. Role-Based Security

Data visibility depends on user roles—critical in HCM implementations.

5. Drill-Down Capability

You can navigate from summary to detail (if configured properly).

Real-World Business Use Cases

Use Case 1 – Employee Master Data Audit

A client in the manufacturing sector needed to verify:

Missing email IDs
Incorrect department mappings
Duplicate employee records

We created an OTBI detail report using “Workforce Management – Worker Assignment Real Time”.

Use Case 2 – Salary Validation Before Payroll Run

Before the first payroll cycle, HR wanted to validate:

Salary basis
Compensation amounts
Effective dates

A detailed OTBI report helped identify inconsistencies before payroll errors occurred.

Use Case 3 – Department Headcount Report

HR leadership required:

Headcount by department
Employee-level breakdown

Instead of summary-only data, they wanted employee-level visibility, which OTBI detail reports provided.

Architecture / Technical Flow

In simple terms, OTBI works as follows:

User logs into Fusion
Selects a Subject Area
OTBI queries transactional tables
Data is displayed in report format

Important Insight

Unlike Oracle BI Publisher:

OTBI = Real-time reporting
BI Publisher = Extract-based reporting

In projects, we often use both together.

Prerequisites

Before creating an OTBI report, ensure:

1. Required Roles

BI Author Role
HCM Data Access Roles

2. Subject Area Access

User must have access to relevant subject areas like:

Workforce Management – Worker Assignment Real Time
Workforce Management – Worker Person Real Time

3. Data Security Setup

If security profiles are not configured properly, the report may return:

No data
Partial data

Step-by-Step Build Process

Now let’s build a Fusion HCM OTBI Detail Report.

Step 1 – Navigate to OTBI

Navigation:

Navigator → Tools → Reports and Analytics

Click on:

Browse Catalog
Open “New” → Analysis

Step 2 – Select Subject Area

Choose:

Workforce Management – Worker Assignment Real Time

👉 This is the most commonly used subject area for employee details.

Step 3 – Add Columns

From the left panel, drag and drop:

Worker → Person Number
Worker → Full Name
Assignment → Assignment Number
Assignment → Department Name
Assignment → Business Unit

Consultant Tip

Always include:

Person Number (unique identifier)
Assignment Number

These are critical for debugging data issues.

Step 4 – Apply Filters

Click on “Filters” and add conditions.

Example:

Assignment Status = Active
Business Unit = Vision India

Real Project Insight

Without filters, reports become slow and unusable in production.

Step 5 – Add Sorting

Sort by:

Department Name
Employee Name

This improves readability for business users.

Step 6 – Format the Report

Rename column headers
Adjust column width
Apply table format

Step 7 – Save the Report

Click:

Save → Choose Folder

Example:

Custom → HCM Reports → Employee Reports

Testing the Technical Component

Once the report is created, testing is critical.

Test Scenario

Input:

Business Unit = Vision India
Active Employees

Expected Output:

List of all active employees
Correct department mapping
No duplicate records

Validation Checks

Check employee count against system dashboard
Validate sample employee records
Cross-check with HR team

Common Errors and Troubleshooting

Issue 1 – No Data Returned

Cause:

Missing data security
Incorrect filters

Fix:

Verify security profile
Remove filters and test

Issue 2 – Duplicate Records

Cause:

Multiple assignments
Incorrect subject area joins

Fix:

Use primary assignment filter

Issue 3 – Performance Issues

Cause:

Too many columns
No filters

Fix:

Limit columns
Add filters

Best Practices from Real Implementations

1. Always Use Primary Assignment Filter

Avoid duplicate employee records.

2. Limit Columns

Only include fields required by business.

3. Use Prompt Filters

Allow users to dynamically select:

Business Unit
Department

4. Validate with Business Users

Never assume report correctness—always validate.

5. Use Naming Conventions

Example:

HCM_EMP_DETAIL_OTBI

This helps in large implementations.

6. Combine OTBI with BI Publisher

Use OTBI for quick reports, BI Publisher for formatted reports.

Summary

Creating a Fusion HCM OTBI Detail Report is a foundational skill for any consultant working with Oracle Fusion Cloud. It enables real-time reporting, quick analysis, and better decision-making.

In real projects, OTBI becomes the first tool HR teams rely on for:

Data validation
Reporting
Auditing

Once you master subject areas, filters, and report design, you can deliver high-value insights to clients quickly.

For deeper reference, always check Oracle documentation:
https://docs.oracle.com/en/cloud/saas/index.html

FAQs

1. What is the difference between OTBI and BI Publisher?

OTBI provides real-time reporting, while BI Publisher is used for formatted and scheduled reports.

2. Which subject area is best for employee detail reports?

“Workforce Management – Worker Assignment Real Time” is most commonly used.

3. Why does my OTBI report show duplicate records?

This usually happens due to multiple assignments. Use “Primary Assignment = Yes” filter.