By Venkat Mankena, HEXstream data analyst

In many organizations using Oracle Fusion applications, reporting is a daily necessity. Business users constantly need access to payroll reports, employee data, invoices, procurement records, and many other operational insights.

While Oracle Fusion provides dashboards and analytics, there are situations where companies need highly formatted reports that can be shared, printed or emailed automatically. This is where Oracle Fusion BI Publisher (BIP) becomes extremely useful.

BI Publisher is Oracle’s reporting tool designed to create pixel-perfect documents from application data. It allows developers and functional consultants to extract data, design templates, and generate reports in formats such as PDF, Excel, Word or HTML. The strength of BI Publisher lies not only in generating reports, but also in automating their delivery to the right people at the right time.

This article walks through the fundamentals of Oracle Fusion BIP Reports, their key components, how to create one step-by-step, the types of templates used, and how scheduling and bursting help automate report distribution.

Understanding Oracle Fusion BI Publisher reports

A BI Publisher report in Oracle Fusion is essentially a structured combination of three elements: data, layout and delivery. The system pulls data from a data source, formats it is using a template, and then delivers the output in a desired format.

Unlike simple queries or dashboards, BIP reports focus on formatted output. For example, a payslip report needs specific alignment, fonts, company branding, and sections for earnings and deductions. BI Publisher allows this level of formatting while still being connected to live application data.

Another advantage is that reports can be run manually or scheduled to run automatically. This helps organizations distribute reports such as monthly payroll summaries or financial statements without manual intervention.

How Oracle Fusion BIP Reports work

At a basic level, a BI Publisher report works by combining data extraction, template formatting, and output generation.

First, the system retrieves data from Oracle Fusion tables using a data model. This data is converted into XML format, which acts as the input for the report layout. A template is then used to format the XML data into a human-readable document such as a PDF or Excel file. The workflow of this process is shown below:

This workflow shows how raw data from the database is transformed into a structured report that users can access or receive automatically.

Components of a BIP report

To understand BI Publisher properly, it is important to know the main components that make up a report. Each component has a specific role in the reporting process.

1. Data Model

The Data Model is the foundation of a BI Publisher report. It defines how data is retrieved from the Oracle Fusion database or other sources.

The data model typically contains SQL queries that extract data from tables. It also defines how different pieces of data are organized and structured before being passed to the report template. A well-designed data model ensures that the report retrieves accurate data efficiently.

Key elements inside a data model include:

• SQL Queries
• Data Sets
• Parameters
• Data Structure
• Output XML

2. Data Sets

Within the data model, data sets represent the actual queries used to retrieve information. A single report may contain multiple data sets if it needs information from different tables or systems.

3. Parameters

Parameters allow users to control what data the report retrieves. For instance, instead of generating a report for all employees, users can filter it for a specific department or date range.

 4. List of Values (LOV)

List of values is a feature used in report parameters that provides users with a predefined list of selectable values when running a report. Instead of manually typing input values, users can choose from a drop-down list, which improves accuracy and usability.

5. Layout Template

The Layout Template defines how the report looks. It determines the visual structure, formatting, tables, headers and branding.

6. Output Format

BI Publisher supports several Output Formats including:

• PDF
• Excel
• Word
• HTML
• CSV

Organizations usually choose the format depending on how the report will be used. For example, financial reports are often generated as PDF files, while data-analysis reports are often exported to Excel.

Step-by-step process of creating a BIP report

Creating a BI Publisher report in Oracle Fusion involves several steps. Although the exact interface may differ slightly depending on the environment, the general process remains the same.

Step 1: Identify the business requirement—Every report starts with a requirement. For example, business users need a report that fetches all the Ap Invoice Lines Validation Report from Accounts Payables. Functional consultant will provide a MD50 with all the required tables and columns, Parameters, List of Values, Output format and conditions. Understanding the business need helps determine what data is required and how it should be displayed.

Step 2: Create the data model—Once the requirement is clear, the next step is to build the data model. This involves writing an SQL query to extract data from the relevant tables. The query is then uploaded or created inside the BI Publisher Data Model editor. Create a data model by selecting required columns, tables, joins, created Lov, parameters. During this stage, parameters can also be defined. For example:

• Supplier Name
• Invoice Number
• From and To Date
• Business Unit

These parameters allow users to filter the report dynamically.

Step 3: Test the data model—After creating the data model, it should be tested to ensure it retrieves the correct data without any duplicates. BI Publisher allows developers to run the query and preview the XML output by using save as sample data. The XML output acts as the data source for the template.

Step 4: Designing the report template—The next stage involves creating the report layout template. Typically, developers download the XML sample data and utilize BI Publisher to build the template structure. With the help of the BI Publisher plugin, various elements such as fields, tables, repeating sections, and formatting components can be inserted based on the XML data structure. After completing the layout design, the template file is uploaded back into BI Publisher. A new template is then added, set as the default layout, and the changes are saved.

Step 5: Create the report definition—Once the data model and template are prepared, the next step is to create the report definition. In this stage, the data model is associated with the template to generate the final report. The report definition acts as a link between these components and determines the supported output formats for the report.

Step 6: Run and validate the report—In the final step, the report is executed to ensure that the data is displayed correctly, and the layout matches the business requirements. If any issues are identified, necessary changes can be made to the SQL query or the report template.

Once the report is verified, the report path and testing documentation are shared with the functional consultant. The functional consultant performs functional testing and then forwards it to the end user for validation.

After successful validation, the report is migrated to the test instance for User Acceptance Testing (UAT). When the users complete testing and provide their approval, the report is finally migrated to the production instance for live use.

Templates used in BI Publisher

BI Publisher provides multiple template options depending on the reporting needs. For example, an RTF layout can be designed based on business requirements, including elements such as company logo, customer information, billing address, shipping address, and outstanding invoice details.

RTF Templates—RTF templates are commonly used in Oracle Fusion reporting. They are typically designed in Microsoft Word using the BI Publisher Desktop plugin.

Advantages of RTF Templates:

• Simple and quick to design
• Supports tables, formatting, and structured layout
• Suitable for most standard reports

Because of their flexibility, many payroll and HR reports are developed using RTF templates.

Excel Templates—Excel templates are useful when reports need a spreadsheet-style presentation. They are commonly used for:

• Financial reports
• Data analysis
• Exporting large datasets

Excel templates also support formulas, charts, and organized tables, making them ideal for analytical reports.

XSL Templates—XSL Templates offer advanced formatting and transformation capabilities. They are typically used when reports require:

• Highly complex layouts
• Advanced data transformations
• Specialized formatting rules

Because of their technical nature, XSL Templates are usually implemented by experienced developers.

PDF Templates—PDF Templates are often used for documents that require fixed and consistent formatting. Common examples include:

• Invoice templates
• Purchase order templates
• Official business documents

Using PDF Templates helps maintain a consistent layout even when system changes occur.

Scheduling BI Publisher reports

BI Publisher also includes a report scheduling feature that allows reports to run automatically. Instead of manually executing reports every day, organizations can schedule them to run at predefined times.

Examples:

• Payroll reports → generated monthly
• Financial summaries → generated quarterly
• Procurement reports → generated every Monday

Scheduling helps reduce manual effort and ensures reports are generated regularly and reliably. Users can configure:

• Output format
• Delivery method
• Execution schedule

This capability is particularly valuable for large organizations that generate a high volume of reports on a regular basis.

Bursting in BI Publisher

Bursting in BI Publisher is a feature that divides report data into smaller sections, generates separate documents for each section, and sends those documents to one or multiple destinations. The report's query is executed only once, and the resulting data is then separated into segments based on a specific key field.

Each segment of the data produces an individual document that can be delivered independently.

With the bursting functionality, a single report can be split using one element from the data model while the delivery of each generated document can be controlled using another element. Based on the delivery configuration, different templates, output formats, delivery channels, and destination locations can be assigned to each segment of the report.

Split-by element

The split-by element determines how the report data is divided. It represents a field within the dataset that controls how the report is separated into individual sections.

For example, when processing a batch of invoices, the report can be split using a field such as CUSTOMER_ID so that each customer’s invoice is generated as a separate document. To ensure correct splitting, the dataset should be sorted or grouped by this field.

Deliver-by element

The deliver-by element controls how delivery settings and formatting rules are applied to each generated document.

Continuing with the invoice example, each customer may have different delivery preferences. Therefore, the same field (CUSTOMER_ID) can also be used to determine how and where each invoice is delivered.

Delivery query

The delivery query is a SQL query defined within the BI Publisher to create the delivery XML file. This query provides the necessary information required for report formatting and distribution, including template selection, output format, and delivery destination.

Common delivery channels

Reports generated through bursting can be delivered through several channels, including:

• Email
• FTP server
• Printer
• Fax
• Content server

Bursting is commonly used in HR and Finance departments because it enables secure, automated distribution of reports to multiple recipients.

Conclusion

Oracle Fusion BI Publisher is a robust reporting solution that enables organizations to convert raw application data into well-structured and professional reports. By integrating data models, report templates, and automated delivery options, BI Publisher allows businesses to generate and distribute reports efficiently.

Understanding the fundamental components of BI Publisher reports, learning the report-creation process, and utilizing advanced features such as scheduling and bursting can greatly enhance an organization’s reporting capabilities.

For developers and consultants working with Oracle Fusion applications, knowledge of BI Publisher is a valuable skill. Mastering this tool not only improves reporting efficiency but also helps automate many repetitive reporting activities.

As organizations increasingly rely on data-driven decision making, reporting tools like BI Publisher will continue to play a critical role in modern enterprise systems.

CLICK HERE TO CONNECT WITH US ABOUT USING BIP REPORTS.