You can get multiple reporting tools in Oracle Fusion, but each comes with limitations:
- OTBI (Oracle Transactional Business Intelligence): While it enables template-driven reporting, it often lacks the flexibility required for rapid, SQL-based customization.
- BI Publisher (BIP): Offers robust standard reports, but meeting unique business needs typically requires IT teams to develop and maintain dozens, and sometimes more than a hundred, custom reports using an Oracle Fusion custom report builder.
How to develop custom BI Publisher reports most efficiently is the focus of this article. We will also introduce the CloudSQL SQL Editor for Oracle Fusion, which simplifies the development and testing of SQL-based BI reports, improving efficiency, reducing overhead for developers, and minimizing manual errors and repetitive work.
What Are Custom Reports in Fusion Context?
Every business has unique reporting requirements that go beyond the standard capabilities of OTBI or standard BI reports provided by Oracle. As a result, business teams often demand numerous reports, sometimes approaching a hundred, which must be developed and maintained by IT teams.
These are known as custom reports, and many organizations rely on Oracle Fusion custom report builder to meet these needs effectively.
What Is the Process of Custom Reports Development?
The development of custom reports in Oracle Fusion typically follows a structured approach:
Implementation & Discovery
When a company implements Oracle Fusion, it conducts discovery sessions, often with the help of a third-party partner, to capture all business requirements, including reporting requirements.
Requirement Analysis
Standard reports are reviewed first to determine which needs are already met. Gaps are identified, and a list of custom report requirements is created.
Report Specifications
For each custom report, a functional specification is written, detailing data sources, joins, business logic, and expected output.
Development
IT teams (sometimes with third-party outsourcing partners) develop the report. This involves creating the SQL query, data model, and report layout in BI Publisher or another Fusion-supported tool.
Testing
Reports are validated for accuracy, performance, and alignment with requirements. This process typically goes through at least three stages:
- Unit testing by developers
- System integration testing by functional consultants or business analysts
- User acceptance testing by key users
Defects are resolved, and minor changes are made during each testing cycle.
Deployment
Once approved, the report is migrated to the production environment and made available to end users.
Also Read: Oracle Fusion Ad Hoc Reporting Made Easy with CloudSQL
BI Publisher Report Development
While Oracle BI Publisher (BIP) is one of the powerful reporting tools in Oracle Fusion, the process of developing a new report is time-consuming and resource-heavy, involving multiple steps:
- Designing and building the data model
- Writing and validating the SQL queries
- Using a drag-and-drop editor to design the report layout
- Running the report, testing output, and refining repeatedly
However, creating a custom report can feel complex without an Oracle Fusion custom report builder. We have written a full step-by-step tutorial to create a custom report in BIP.
How Can CloudSQL Help with BI Report Development?
CloudSQL is purpose-built to simplify and speed up the creation of custom reports in Oracle Fusion. It addresses Step 6 of the BI report development process, the stage that typically consumes 60% to 80% of total development time. With CloudSQL, SQLs can be written and tested directly within the tool before being moved to BI Publisher, making it a powerful Oracle Fusion custom report builder for faster and more efficient reporting.
Key Advantages Include
- Feature-Packed SQL Editor: A modern SQL editor with auto-complete, formatting, error-highlighting, and execution history, designed for rapid query building.
- Single Click Execution: Once an SQL is written in the editor, all that is to be done is click on the execute button. Results will be populated on the results grid very quickly. This is extremely useful in the development stage, where developers will build SQLs step by step and test them multiple times, often hundreds of times, for a medium-complexity report.
- Robust Result Grid: Once the results are available, the developer will be able to sort, filter, and group the data for enhanced viewing. Additionally, a single record view offers the facility for in-depth analysis.
- Built-in Database Browser: Explore Fusion’s data model effortlessly with a database browser that provides table structures, joins, and foreign key references, making it easier to understand and query ERP data.
- Public SQL Library: Leverage a shared repository of pre-built SQLs from the CloudSQL web app to jumpstart report creation, reducing development time and effort.
- Direct BI Server integration: CloudSQL can edit SQLs on data models directly from the BI server using our integration
- AI-Assisted SQL Error Resolution: With integrated AI support, CloudSQL helps developers quickly identify and fix SQL errors. This reduces debugging time and ensures smoother, faster report development.
CloudSQL offers a powerful, user-friendly interface for SQL development in Oracle Fusion. It simplifies connection setup, SQL writing, and validation with features like auto-complete and formatters. But creating a custom report involves multiple iterations and can be tricky. For a detailed walkthrough, check our full guide on building a custom report in CloudSQL.
Pro Tip: Avoid starting SQL development from scratch; always reuse existing SQLs. Many modules already have SQLs available through standard or custom BI reports, as well as from Oracle or other trusted forums. In addition, CloudSQL Online offers a public library where our community has contributed hundreds of SQLs for Oracle users.
Feature Comparison: Custom Report with CloudSQL vs Without CloudSQL
| Feature | CloudSQL | BI Publisher without CloudSQL |
| Code Editor | Powerful SQL Editor | Basic text window or external tools like Notepad++ |
| Execution | One-click execution | Copy from Notepad++ and paste into BI Publisher editor |
| Result Viewing | Advanced result grid with sort, search, filter, group-by, and record view | Limited to 200 records; requires export to Excel for further analysis |
| Error Analysis | Intelligent error assistance with AI-powered fixes | Displays only error messages |
| Report Defect Fixing | Directly edit data models in CloudSQL | Requires external editing in Notepad++ |
| Report Layout Creation | Manual | Manual |
| Pricing & Licensing | CloudSQL subscription | Included |
Also Read: Running Your First SQL with CloudSQL
Use Cases & Personas
A transportation company in the Middle East has decided to replace its existing in-house ERP system with Oracle Cloud ERP, covering both Financials and HCM pillars. An in-house IT project team was assembled, supported by some external contractors for the implementation.
During the discovery phase, 90 reporting requirements were identified. Of these, 60 could be fulfilled through existing standard reports, while 30 required new development. During the build phase, these 30 reports were assigned to the IT team.
The IT team, consisting of four developers, took on this workload. On average, each developer was responsible for seven reports, with an estimated development cycle of around two weeks per report. Traditionally, this would have meant several months of effort before all requirements were met.
By adopting CloudSQL, the team was able to streamline the entire development process, reducing build time by nearly 30%. This not only accelerated report delivery but also allowed developers to spend more time on higher-value activities such as optimization, testing, and collaborating with business users.
Frequently Encountered Issues & Solutions
When building custom SQL reports in Oracle Fusion, users often face recurring challenges. CloudSQL provides built-in features and smart workarounds to address these effectively:
Debugging Complex SQL Errors
Issue: Large SQL scripts (e.g., 500+ lines) can fail with vague errors like:
“ORA-00923: FROM keyword not found where expected” (often due to something as small as a missing comma).
Solution: CloudSQL’s AI Assistant analyses and automatically fixes such errors, pinpointing the exact issue in seconds.
Use Formatting to Improve Readability of SQL
Issue: Large SQLs passed from one developer to another are not easily understood for further refinement.
Solution: Use CloudSQL’s formatting feature to improve the readability of SQLs
Performance Bottlenecks
Issue: Large queries with big result sets slow down report building during development.
Solution: CloudSQL allows you to limit fetch counts during testing or use the background run feature, so development is faster and doesn’t overload the system.
Frequently Asked Questions (FAQs)
What are custom reports in Oracle Fusion?
Every business has unique reporting requirements that go beyond the standard capabilities of OTBI or BI Publisher. As a result, business teams often demand many reports, sometimes close to a hundred, which must be developed and maintained by IT teams. These are known as custom reports.
How does CloudSQL compare to BI Publisher for the custom report-building process?
BI Publisher requires creating a data model, building layouts, and multiple testing cycles, often taking days or weeks. CloudSQL skips part of these steps by letting you write and run SQL directly, enabling faster development of custom reports.
How much time is saved with CloudSQL?
The time savings vary depending on the requirement and the developer. For example, a typical report development takes about 2 weeks. Of this, 2 days are spent on report layout creation, and the remaining 8 working days are dedicated to SQL development.
During those 8 days, a developer typically executes the SQL around 400 times (based on CloudSQL statistics). Without CloudSQL, each execution takes about 3 minutes, while with CloudSQL, it takes less than 30 seconds. The enhanced SQL editor further speeds up development. As a result, SQL development time is cut in half, from 8 days to 4 days, reducing the total cycle from 10 days (8 + 2) to just 6 days (4 + 2).”
