Generating Multi-Sheet Excel Reports from Oracle HCM Using BI Publisher (BIP)

Standard OTBI outputs are flat tables. When stakeholders need data across multiple dimensions in separate sheets — say, one sheet per department, or one sheet per absence type — BI Publisher with a correctly structured RTF template is the answer.

The Key — Native Excel Output with Group Breaks

BI Publisher supports a native Excel output format (different from HTML-rendered Excel). To enable multi-sheet output, you need two things in your RTF template:

1. A for-each-group loop around the grouping field (e.g. Department)
2. A sheet-break command using the native Excel XML tag

Sheet: 

      

Naming Each Sheet Dynamically

Use the xls:sheet tag to name each sheet after the group value:

"?>

✦ The output format in the BIP report definition must be set to Excel (*.xlsx) — not Excel 2003. The sheet-break tag only works with the XLSX output type.

Common Errors

Error	Cause	Fix
All data on one sheet	Missing xls-sheet-break tag	Add tag between group iterations
Sheet named "Sheet1" always	Static sheet name in template	Use current-group() in xls:sheet
Empty sheets generated	Group field has nulls	Filter nulls in the data model query

Connecting External APIs to Oracle HCM Using VBCS Service Connections

Oracle Visual Builder Cloud Service (VBCS) is a powerful tool for extending HCM with custom UI components. One of its most useful features is the Service Connection — it lets you call any REST API and display the results directly within your HCM pages.

When to Use VBCS Service Connections

• Displaying predictions from an ML model (e.g. attrition risk score)
• Pulling data from a third-party HR system in real-time
• Triggering an external workflow from within HCM
• Showing live leave balances from a custom calculation engine

Creating a Service Connection — Steps

1. Open VBCS → Navigate to your application → Services tab
2. Click + Service Connection → Choose Define by Endpoint
3. Enter your REST API URL (e.g. your Azure ML endpoint)
4. Set Authentication: Bearer Token or Basic Auth depending on your API
5. Define the Request schema (JSON body) and Response schema
6. Click Test to verify the connection returns expected data
7. Bind the response fields to your page components

// Example request payload for Azure ML attrition endpoint
{
  "input_data": {
    "columns": ["tenure_months","absence_count","perf_rating","grade"],
    "data": [[36, 8, 3, "M3"]]
  }
}
// Response
{ "result": [0.73] // 73% attrition risk }

✦ Always handle the case where the external API is unavailable. Add a fallback value or a "Data not available" message in your VBCS component — otherwise the HCM page will show a blank tile with no explanation.

Migrating to Oracle Redwood UI — A Practical Checklist from the Field

Oracle's Redwood UI is the new standard for Fusion HCM. If your organization is still on the classic UI, migration is inevitable. Here is the practical checklist I used — tested against a real implementation.

Phase 1 — Audit Your Current Configuration

• List every custom page layout and flexfield configuration
• Identify personalizations that will not carry over automatically
• Document all custom BIP reports that display within HCM pages
• Note any third-party integrations that depend on classic UI URLs

Phase 2 — Enable Redwood in Sandbox

Navigate to Setup and Maintenance → Manage Administrator Profile Values. Search for the profile option HCM_EXPERIENCE_DESIGN_MODE and set it to REDWOOD at the Site level — in Sandbox only first.

✦ Never enable Redwood in Production before completing a full regression test in Sandbox. Some workflows behave differently, especially absence and performance forms.

Phase 3 — Test High-Traffic Pages First

Page	Test Focus	Risk Level
My Team → Absence	Absence request flow end-to-end	High
Performance → Goal Setting	Goal creation and approval chain	High
Payslip view	Rendering and download	Medium
Learning → My Courses	Course launch and completion	Medium
Directory search	Search results and person card	Low

Phase 4 — Promote to Production

After sign-off in Sandbox: enable Redwood at Site level in Production during a low-traffic window. Prepare a rollback plan — you can revert the profile option if critical issues arise.

The Biggest Lesson

The technical switch takes minutes. The people side takes weeks. Invest in quick reference cards and short demo videos for end users — these reduce helpdesk tickets by more than any technical preparation.

Building an Employee Attrition Prediction Model with Azure ML Studio and Oracle HCM

 Category: Azure ML / HCM Integration

Tags: Azure ML, OTBI, VBCS, Attrition

Predicting employee attrition before it happens is one of the most valuable things an HR analytics team can do. In this post I walk through how I built a full pipeline: Oracle HCM data → Azure ML AutoML → Managed Online Endpoint → OTBI/VBCS for display.

Step 1 — Export the Dataset from Oracle HCM

Use an OTBI analysis or HCM Extract to pull the training dataset. Key columns to include:

• Employee tenure (months)
• Department, Job Function, Grade
• Absence frequency (last 12 months)
• Performance rating (last 2 cycles)
• Last promotion date
• Voluntary termination flag (target variable: 1 = left, 0 = stayed)

Export as CSV and upload to Azure Blob Storage.

Step 2 — Run AutoML Classification in Azure ML Studio

# Azure ML AutoML setup (Python SDK)
from azure.ai.ml import MLClient
from azure.ai.ml.automl import classification

automl_job = classification(
    compute="cpu-cluster",
    training_data=training_data,
    target_column_name="attrition_flag",
    primary_metric="AUC_weighted",
    n_cross_validations=5,
    enable_model_explainability=True
)

returned_job = ml_client.jobs.create_or_update(automl_job)

✦ Use AUC_weighted as the primary metric for attrition prediction — the dataset is almost always imbalanced (far more retained employees than terminations). Accuracy alone will mislead you.

Step 3 — Deploy to Managed Online Endpoint

Once the best model is selected by AutoML, deploy it as a REST endpoint. This gives Oracle VBCS a URL to call for real-time predictions.

az ml online-endpoint create --name attrition-endpoint
az ml online-deployment create \
  --endpoint-name attrition-endpoint \
  --name blue \
  --model azureml:attrition-model:1

Step 4 — Connect to Oracle VBCS

In VBCS, create a new Service Connection pointing to the Azure ML endpoint URL. Pass employee attributes as the request payload. Display the attrition risk score in a custom HCM dashboard tile.

Step 5 — Surface Results in OTBI

Write the prediction scores back into a custom Oracle HCM attribute (using HCM Extracts in reverse or a REST API write-back). Create an OTBI analysis that shows high-risk employees by department, enabling HR to act proactively.

Azure ML StudioOracle Fusion HCMOTBIVBCSAttrition PredictionAutoML

Deploying an Absence Analyst Agent in Oracle AI Agent Studio — What Nobody Tells You

 

Deploying an Absence Analyst Agent in Oracle AI Agent Studio — What Nobody Tells You

Category: Oracle AITags: AI Agent Studio, Absence, HCM

Oracle AI Agent Studio lets you build intelligent agents that run inside Oracle Fusion — no external tools required. I recently deployed a Workforce Operations Absence Analyst agent, and I want to share the exact issues I hit and how I resolved them.

What the Agent Does

The Absence Analyst agent monitors absence trends across teams, flags unusual patterns, and surfaces insights without a human having to run reports manually. It connects to HCM Absence records and uses configured business rules to classify and summarise absences.

The JSON Node Error — What It Looks Like

The most common failure during deployment is a JSON node misconfiguration. The agent workflow file (.wf) stores node definitions in JSON format. A missing comma, an extra bracket, or a misquoted key will silently break the entire flow.

{
  "nodeType": "ABSENCE_QUERY",
  "parameters": {
    "dateRange": "CURRENT_MONTH"   ← missing comma after this line
    "employeeScope": "ALL_ACTIVE"
  }
}

✦ Always validate your .wf JSON against a linter before saving. Even one syntax error causes the entire agent to fail at runtime with a generic "Node execution failed" message — no line number given.

Debugging the .wf File

1. Download the .wf file from the Agent Studio export
2. Open in VS Code and run JSON validation (Ctrl+Shift+P → Format Document)
3. Look for red squiggles — fix all JSON syntax errors
4. Re-import the corrected file into AI Agent Studio
5. Test in Sandbox environment before promoting to Production

Deployment Checklist

Step	Action	Common Mistake
1	Configure data source connection	Wrong HCM environment URL
2	Define agent role and permissions	Missing Absence Viewer role
3	Upload .wf workflow file	JSON syntax errors
4	Set trigger schedule	Timezone mismatch (use UTC)
5	Test in Sandbox	Skipping this step

Once the agent is live, it saves HR teams hours each week. The output is surfaced directly in the HCM dashboard — no separate tool, no export, no manual refresh.

Oracle AI Agent StudioOracle Fusion HCMAbsence ManagementWorkforce Analytics

COALESCE-SQL

Coalesce- return the null values from the expression. It works similar to a case statement where if expression 1 is false then goes to expression 2 or returns a default set value.

In same way Coalesce will take the null values and replace with the given default value or act according to expression defined for null.

e.g:-

SELECT product_id, list_price, min_price,
   COALESCE(0.9*list_price, min_price, 5) "Sale"
   FROM product_information
   WHERE supplier_id = 102050
   ORDER BY product_id, list_price, min_price, "Sale";

PRODUCT_ID LIST_PRICE  MIN_PRICE       Sale
---------- ---------- ---------- ----------
      1769         48                  43.2
      1770                    73         73
      2378        305        247      274.5
      2382        850        731        765
      3355                                5

NULLS LAST/FIRST

When we use RANK function and if we have the ranking criteria column with NULL values, they do have chances to be listed in first position. By using Nulls last we can avoid such occurrences.

Ex:

Student ranking first based on their achieved grades on all test they appeared.

select p.name Student, m.name, m.tests, rank() over ( order by tests desc ) as S_rank from ptudents p, test m where p.name = m.student_name(+) and p.name in ('M','V','P','M','N') order by s_rank

Student	NAME	tests	S_RANK
V	-	-	1
M	-	-	1
N	T1	1353.4	3
P	C1	603.6	4
N	P1	210	5
N	N1	170	6

select p.name Student, m.name, m.tests, rank() over ( order by tests desc NULLS LAST ) as S_rank
from ptudents p, test m where p.name = m.student_name(+) and p.name in ('M','V','P','M','N') order by s_rank

Student	NAME	tests	S_RANK
N	T1	1353.4	1
P	C1	603.6	2
N	P1	210	3
N	N1	170	4

Ans null will be listed in last