Leveraging Flexfields in Oracle Cloud ERP BI Publisher Reports

1 February 2025

Leveraging Flexfields in Oracle Cloud ERP BI Publisher Reports

Flexfields are at the heart of Oracle Cloud ERP’s extensibility, allowing organisations to capture and report multidimensional business data with custom, user-defined segments. When building BI Publisher reports in Oracle Cloud ERP, properly including Key Flexfields (KFFs) is essential for delivering clear, context-rich analytics.

What Are Flexfields?

A Flexfield is an extensible data structure that allows for multi-segment codes: think of account strings in General Ledger, where each segment (company, department, account) is distinct, or Descriptive Flexfields (DFFs) to hold extra attributes.

Configuring Flexfields for BI Publisher

When reporting on Flexfield data directly, you must account for both display and concatenated segment values. BI Publisher enables this through the Flexfield component in the data model and SQL lexicals.

Example Setup

Suppose you need to extract segment values and concatenated displays for GL code combinations:

Flexfield Type: Key Flexfield
Lexical Name: KFF_SELECT
Application Short Name: GL
Flexfield Code: GL#
Lexical Type: Select
Code Combination Table Alias: CodeCombination
Structure Instance Number: 101
Segments: ALL
Output Type: Full Description

Or you want to display the full segement descriptions for GL code combinations:

Flexfield Type: Key Flexfield
Lexical Name: KFF_SELECT
Application Short Name: GL
Flexfield Code: GL#
Lexical Type: Select
Code Combination Table Alias: CodeCombination
Structure Instance Number: 101
Segments: ALL
Output Type: Full Description

Example SQL

				
					SELECT CodeCombination.CODE_COMBINATION_ID,
       CodeCombination.CHART_OF_ACCOUNTS_ID, --required for flexfield
       CodeCombination.SEGMENT1,
       CodeCombination.SEGMENT2,
       CodeCombination.SEGMENT3,
       CodeCombination.SEGMENT4,
       CodeCombination.SEGMENT5,
       CodeCombination.SEGMENT6,
       CodeCombination.SEGMENT7,
       &KFF_SELECT AS ConcatSegments,
       &KFF_SELECT_DESC AS ConcatSegmentsDesc
  FROM GL_CODE_COMBINATIONS CodeCombination

&KFF_SELECT and &KFF_SELECT_DESC are lexical references replaced at runtime with Oracle-generated logic for concatenated strings and descriptions, as configured for your chart of accounts.

Steps to Use Flexfields in a BI Publisher Data Model

Add a Flexfield Component: In the BI Publisher data model designer, add a Flexfield and link it to your report’s SQL.
Configure SQL Lexicals: Insert lexical tags (e.g., &KFF_SELECT) in your SQL WHERE or SELECT clause to inject the correct join, concatenation, and display logic.
Deploy and Test: Validate that all segment labels, values, and descriptions appear correctly in the BI Publisher output.

Tips and Best Practices

Always ensure the required IDs (such as CHART_OF_ACCOUNTS_ID) are included in your SELECT for flexfields to resolve their structure at runtime.
Use both lexical (&KFF_SELECT) and descriptive (&KFF_SELECT_DESC) references for full analytics and a user-friendly display.
If requirements change (new segments added, labels updated), redeploy the flexfield structure in the application, BI Publisher will pick up changes dynamically.

Summary

Using these techniques, you can greatly enhance the interactive power of your Oracle BI Publisher reports, providing end users with familiar, dashboard-style filtering and a seamless analytics experience.

Need some assistance?

Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.