How-toOracle Fusion Cloud (BI Publisher and OTBI)Reporting / BI Publisher / OTBI

How to Create a Report in Oracle Fusion

Question
how to create report in oracle fusion

Also searched as

  • oracle fusion create bi publisher report step by step
  • create otbi analysis oracle fusion
  • how to schedule report in oracle fusion
  • oracle fusion custom report data model template

Short answer

In Oracle Fusion you create a report either as an OTBI analysis (ad hoc, drag and drop on a subject area) or as a BI Publisher report (a data model plus a layout template, for pixel-perfect and scheduled output). Both start from Navigator > Tools > Reports and Analytics, then Create, and both need the BI author roles.

Applies to: Oracle Fusion Cloud applications with Oracle Transactional Business Intelligence and BI Publisher

How to create a BI Publisher report

  1. 1Confirm that your user has the BI authoring roles, such as BI Author or BI Administrator style roles, in addition to the functional role that grants data access.
  2. 2Open Navigator > Tools > Reports and Analytics, then choose Browse Catalog to open the BI catalog.
  3. 3Create a data model: New > Data Model, add a data set of type SQL Query, choose the ApplicationDB_FSCM or relevant data source and write a SELECT using the tables or views you may read.
  4. 4Test the query, set parameters as bind variables with list of values where needed, and save the model in a folder under Shared Folders > Custom.
  5. 5Create the report: New > Report, pick the data model and use the wizard or upload an RTF, Excel or XDO template as the layout.
  6. 6Run the report with parameters, view as PDF, Excel or HTML and fix the layout.
  7. 7Schedule it: open the report, choose Schedule, set the recurrence, output format and delivery to email, FTP or the content server.
  8. 8Set permissions on the folder so only the intended roles can run it.

OTBI analysis or BI Publisher report

Choose an OTBI analysis when a business user wants an answer quickly using a Fusion subject area, with joins, security and descriptive flexfields already handled. It is a good fit for dashboards and drill-down. See the existing comparison of OTBI and BI Publisher for details.

Choose BI Publisher when you need formatted output such as invoices, checks, labels or statutory reports, when you need to join across areas that no subject area covers, or when you need burst delivery to many recipients. The price is that you write SQL and must respect data security yourself.

A small SQL data model

Direct SQL against transaction tables is allowed in BI Publisher data models on the application database, but it bypasses the subject area security layer, so apply the same restrictions your policies demand. Prefer delivered views and keep queries selective with parameters.

SELECT poh.segment1 po_number,
       poh.creation_date,
       poh.document_status
FROM   po_headers_all poh
WHERE  poh.creation_date >= :p_from_date
ORDER  BY poh.creation_date DESC

Scheduling and moving reports between environments

A scheduled job is run as the user who submitted it, so if that user is disabled the schedule fails. Use a service account for production schedules, and keep the output format and delivery targets in the schedule rather than in the data model.

To promote a report from test to production, archive the catalog folder from the Catalog page and unarchive it in the target environment, then recheck data source names and folder permissions. Do this in a controlled way, since catalog content is not part of configuration packages by default.

Try it here, free

Oracle Fusion BI Publisher Report Specification Template

Open full tool

Document a BI Publisher report before you build it: purpose, data model, parameters, layout, schedule and security in one template.

Common pitfalls

  • !Writing a heavy SQL with no date parameter, which runs long and gets stopped by the timeout.
  • !Storing reports in a personal folder, so they vanish when the user leaves.
  • !Granting folder access to a broad role and exposing payroll or cost data.
  • !Scheduling as a named user whose password policy or account lifecycle will break the job.
  • !Hardcoding IDs that differ between test and production environments.

How an ERP-grounded AI assistant handles this

An assistant grounded in your Fusion data model can draft the SELECT and parameters from a plain request, explain what each table contributes and flag where the query bypasses data security. Running privately, it works from your schema and sample metadata without sending report content to a public service.

See ERPray

Frequently asked questions

What is the difference between OTBI and BI Publisher?

OTBI is an ad hoc analysis tool over subject areas, ideal for quick questions and dashboards. BI Publisher produces formatted, scheduled, burstable reports from data models, including your own SQL.

Where do I find the report builder?

Navigator > Tools > Reports and Analytics, then Browse Catalog to reach the BI catalog where you create data models, reports and analyses. Your user needs the BI authoring roles.

How do I schedule a report in Fusion?

Open the report in the catalog and choose Schedule. Set the parameters, recurrence, output format and delivery destination. The job runs as the submitting user, so use a service account for production.

Can I use SQL directly?

Yes, in a BI Publisher data model on the application database, subject to your role's access. It bypasses subject area security, so apply your own restrictions and avoid unfiltered queries.

Related

How-to

OTBI vs BI Publisher: Which One Should You Use

Use OTBI when a business user needs ad hoc, self-service, real-time analysis with no fixed layout. Use BI Publisher when you need a pixel-perfect, scheduled, high-volume, or externally distributed document such as an invoice, check, or statutory report. Most Fusion implementations end up using both, not one instead of the other.

How-to

Fix Wrong Row Counts When Joining Across OTBI Subject Areas

Most subject areas in Oracle Fusion OTBI are self-contained and only support reliable analysis within their own folders; dragging a folder from a second subject area into the same analysis without a genuine shared join key silently produces duplicated or missing rows because OTBI has no way to correctly relate the two fact grains. Fix it by staying inside one subject area where possible, or by explicitly using a subject area that Oracle designed to cross domains, rather than combining arbitrary folders yourself.

Error fix

Clear An Oracle Fusion ESS Job Stuck In Blocked Status

A Blocked status on an Oracle Fusion Enterprise Scheduler (ESS) job almost always means an earlier instance of the exact same job is still running or stuck, and the job definition does not allow simultaneous requests. Find and finish, or cancel, the earlier request first; only then does the Blocked request move to Running.

How-to

Oracle ERP API Documentation: Where It Is and How to Start

Oracle publishes its ERP APIs per product on docs.oracle.com. For Fusion Cloud ERP and SCM there are REST API guides, SOAP web service guides, FBDI file imports and BI Publisher reports; for NetSuite there are SuiteTalk REST and SOAP, SuiteQL and RESTlets. Pick the product first, then the style of API, then match your authentication and the exact release version.

Error fix

Fix FBDI Import Failures From Interface Table Validation Errors

When a Fusion FBDI import job errors out, the cause is almost always bad data in the interface tables (an invalid value set, a wrong date format, or a missing required column), not the ESS job itself. Open the process's Interface Errors Report or the Correct Import Errors spreadsheet to see the exact rejected rows and reason codes, fix the source data, and resubmit from the interface tables instead of re-running the whole FBDI load.

How-to

Paginate And Authenticate Oracle Fusion REST API Calls Correctly

Oracle Fusion Cloud REST APIs authenticate with HTTP Basic Authentication, an integration user's credentials, over HTTPS by default, and paginate with limit and offset query parameters rather than a page number. Loop on the hasMore flag in the response until it returns false to retrieve a full result set.

AI for ERP

Fusion Cloud ERP AI: OCI Generative AI Service vs. a Private LLM

How Fusion Cloud ERP AI supports GenAI implementation: OCI Generative AI Service for Fusion-only use cases, a private LLM for sensitive or cross-system data.

AI for ERP

AI for Power BI on ERP Data: Answers Beyond the Dashboard You Already Built

Add grounded natural-language answers on top of Power BI semantic models and live ERP tables, with row-level security respected and models served on-prem.

Stuck on Oracle Fusion Cloud (BI Publisher and OTBI)?

Talk to engineers who work inside Oracle Fusion Cloud (BI Publisher and OTBI) every week, and who build private AI that answers these questions from your own ERP data.

Talk to an ERP engineerBrowse ERP Help
All ERP Help articles

Keep exploring

Free tools

ERP fixes and how-tos

Runs with your ERP

AI on your ERP

Integrations

Comparisons

Software