BI Publisher Fundamentals for Oracle Cloud

BI Publisher architecture

graph TD
    subgraph "Data Layer"
        SQL[SQL Query]
        REST[REST API]
        OTBI[OTBI Analysis]
    end
    subgraph "BI Publisher"
        DM[Data Model<br/>Defines data sources<br/>& parameters]
        TM[Layout Template<br/>RTF / XSL-FO / Excel]
        RP[Report<br/>Combines DM + Template]
    end
    subgraph "Output & Delivery"
        PDF[PDF]
        XLS[Excel]
        CSV[CSV]
        EMAIL[Email Delivery]
        FTP2[FTP/UCM]
    end

    SQL --> DM
    REST --> DM
    OTBI --> DM
    DM --> RP
    TM --> RP
    RP --> PDF
    RP --> XLS
    RP --> CSV
    PDF --> EMAIL
    XLS --> EMAIL
    CSV --> FTP2

    style DM fill:#1e3a5f,stroke:#3B82F6,color:#e2e8f0
    style TM fill:#1e293b,stroke:#F59E0B,color:#e2e8f0
    style RP fill:#0f2d1f,stroke:#22C55E,color:#e2e8f0

The report lifecycle end-to-end

sequenceDiagram
    actor Dev as Developer
    participant BIP as BI Publisher
    participant DB as Oracle DB / Fusion
    participant User as Business User

    Dev->>BIP: 1. Create Data Model with SQL
    Dev->>BIP: 2. Test — generate sample XML
    Dev->>Dev: 3. Design RTF template in Word<br/>(Template Builder plugin)
    Dev->>BIP: 4. Upload template, create Report
    User->>BIP: 5. Run report with parameters
    BIP->>DB: 6. Execute SQL with bind values
    DB-->>BIP: 7. Return data as XML
    BIP->>BIP: 8. Merge XML + template → output
    BIP-->>User: 9. Download PDF / Excel

Creating your first Data Model

-- Data model SQL with named bind parameter
SELECT
  po.po_number,
  po.creation_date,
  s.supplier_name,
  pol.line_num,
  pol.item_description,
  pol.quantity,
  pol.unit_price,
  pol.quantity * pol.unit_price AS line_amount
FROM po_headers_all po
JOIN po_lines_all pol    ON po.po_id = pol.po_id
JOIN ap_suppliers s      ON po.vendor_id = s.vendor_id
WHERE po.org_id           = :P_ORG_ID        -- required param
  AND po.status           = :P_STATUS        -- optional, default ALL
  AND po.creation_date   >= :P_FROM_DATE     -- date range
  AND po.creation_date   <= :P_TO_DATE
ORDER BY po.creation_date DESC, pol.line_num;

RTF Template essentials

Insert field (BIP field tag):

<?PO_NUMBER?>                          — output field value
<?xdofx:to_char(CREATION_DATE, 'DD-Mon-YYYY')?> — formatted date
<?xdofx:to_char(LINE_AMOUNT, '$999,999.00')?>    — formatted number

Repeating group (for table rows):

<?for-each:PO_LINES?>
  [table row: <?LINE_NUM?> | <?ITEM_DESCRIPTION?> | <?LINE_AMOUNT?>]
<?end for-each?>

Conditional content:

<?if:STATUS='OVERDUE'?>
  [highlighted overdue row]
<?end if?>

Group total:

Total: <?sum(.//LINE_AMOUNT)?>

Output formats compared

Format	Use case	Template type
PDF	Formatted documents, POs, payslips	RTF or XSL-FO
Excel	Finance teams, pivot-friendly data	Excel template
CSV	Data exchange, system imports	Data model only
HTML	Web display, email body	RTF or HTML
XML	Downstream processing	Data model only