Saturday, 28 June 2025

Building a Custom Report in Oracle Fusion Using BI Publisher

 

Building a Custom Report in Oracle Fusion Using BI Publisher

Oracle Fusion BI Publisher (BIP) is a powerful reporting tool that allows you to create highly formatted, data-rich reports based on Oracle Cloud data. 

Whether you need custom invoices, payroll slips, financial summaries, or data extracts, BIP gives you control over both data and layout.

In this blog post, you'll learn how to create a custom report in Oracle Fusion using BI Publisher — step-by-step.

What You’ll Need

  • Oracle Fusion access with BI Administrator or Report Developer roles

  • Familiarity with SQL queries and subject areas

  • BI Publisher Desktop (MS Word plugin) for template design (optional but recommended)

Step-by-Step: Create a Custom Report

Step 1: Define Your Report Requirements

Before you begin, clearly identify:

  • What data you need (tables, views, business objects)

  • How often the report should run

  • Who needs access (users, roles)

  • Output format (Excel, PDF, RTF, CSV)

Step 2: Create the Data Model

Navigation: Tools > Reports and Analytics > Browse Catalog

  1. Go to Catalog → Create → Data Model

  2. Choose SQL Query as your data source

  3. Example Query (e.g., open purchase orders):


SELECT poh.segment1 AS PO_NUMBER,
       pol.line_num,
       pol.item_description,
       pol.unit_price,
       pol.quantity_ordered,
       poh.creation_date
FROM po_headers_all poh
JOIN po_lines_all pol ON poh.po_header_id = pol.po_header_id
WHERE poh.authorization_status = 'APPROVED'

  1. Click View Data to test

  2. Optionally, add Parameters (e.g., supplier, date range)

  3. Save the Data Model

Step 3: Create a Report Layout

  1. Click on the Data Model → Create Report

  2. Choose:

    • Use Existing Template (RTF, Excel, PDF) or

    • Create a Blank Report for layout

  3. To use MS Word-based design:

    • Download BI Publisher Desktop plugin

    • Open MS Word → Load Sample XML → Design template using form fields

    • Upload .RTF layout back to BI Publisher

  4. Save report in a shared folder like:

    /Shared/Custom/<ReportName>

Step 4: Run or Schedule the Report

  • From the Catalog, click More > Schedule

  • Choose output format: PDF, Excel, CSV

  • Set recurrence: daily, weekly, monthly

  • Define output destination: Online, Email, FTP

Step 5: Assign Roles and Permissions

Ensure appropriate access:

  • Navigate to the report > Click More > Permissions

  • Grant view/run/schedule permissions to roles (e.g., Buyer, AP Analyst)

Optional Features You Can Add

  • Bursting: Distribute report to multiple users based on data (e.g., each department gets its own report)

  • Sub-Templates: Reuse header/footer in multiple reports

  • Translation Templates: Create multi-language versions of the same report

Tips and Best Practices

  • Use database views or subject areas for data stability

  • Keep layout clean and avoid large embedded images

  • For large data sets, prefer CSV or Excel output

  • Always test with filters to prevent full-table scans

Posted by at