As published on the Release Notes for Oracle Forms 12.2.1.4, Oracle Reports has been deprecated as of Fusion Middleware 12c Release 2 (12.2.1.3.0).
No further development of Oracle Reports is planned for the future. If future releases are made available, Oracle is not planning any functional enhancements for Oracle Reports other than critical bug fixes and changes necessary to make it compatible with a new supporting technology stack. For more information, refer to the Reports Statement of Direction available here. 

The purpose of this blog post is to showcase various alternatives you can explore for your Oracle Reports using Oracle APEX, which is the world’s most popular low-code platform for enterprise apps and enables you to build scalable, secure enterprise apps with world-class features that can be deployed anywhere. In addition, APEX is a fully supported no-cost feature of Oracle Database, which means that if you are using an Oracle Database you can start using APEX with no additional licensing cost. APEX provides the following report printing configuration options:

  • None (Native) – Select this option if you do not use an external print server. This option enables you to use the basic printing functionality, which includes CSV, HTML, PDF, and XLSX.
  • Oracle Document Generator Pre-built Function – Select this option if you are using Oracle Document Generator Pre-built Function. This option enables you to take report query results and convert them from JSON to PDF format using Oracle Document Generator Pre-built Function. Select this option to upload your own DOCX templates for printing reports within APEX. 
  • Oracle BI Publisher – This option requires a valid license of Oracle BI Publisher (also known as Oracle XML Publisher). It enables you to take report query results and convert them from XML to RTF format using Oracle BI Publisher. Select this option to upload your own customized RTF or XSL-FO templates for printing reports within APEX. Oracle BI Publisher supports Adobe Portable Document Format (PDF), Microsoft Word Rich Text Format (RTF), or Microsoft Excel format (XLS), or Extensible Markup Language (XML). If you choose to use other third-party rendering engines, other output formats can also be configured.
  • APEX Office Print – Select this option if you are using APEX Office Print. This option enables you to take report query results and convert them from JSON to PDF/MS Office formats using APEX Office Print. Select this option to upload your own MS Office templates for printing reports within APEX. 
  • Apache FOP – Select this option if you are using Apache FOP on an external J2EE server. This option enables you to use the basic printing functionality, which includes creating report queries and printing report regions using the default templates provided in APEX and using your own customized XSL-FO templates.

Having said that, when it comes to reports, there are two types of reports you can find:

  • Simple plain reports with a column header and the data. Also known in Oracle Reports as Tabular, Group Above, Matrix.
  • Pixel-perfect defined reports. Also known in Oracle Reports as Form, Form Letter.

Let’s review each of these types of reports in APEX using a sample Oracle Reports query, available in this Creating a Report Manually Using Reports Builder tutorial:

SELECT ALL DEPARTMENTS.DEPARTMENT_NAME, EMPLOYEES.FIRST_NAME, EMPLOYEES.LAST_NAME, EMPLOYEES.SALARY, LOCATIONS.CITY, COUNTRIES.COUNTRY_NAME, EMPLOYEES.HIRE_DATE
FROM OEHR_EMPLOYEES EMPLOYEES, OEHR_DEPARTMENTS DEPARTMENTS, OEHR_LOCATIONS LOCATIONS, OEHR_COUNTRIES COUNTRIES
WHERE EMPLOYEES.HIRE_DATE > to_date('01-01-2000','dd-mm-yyyy')
AND ((EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID)
AND (DEPARTMENTS.MANAGER_ID = EMPLOYEES.EMPLOYEE_ID)
AND (DEPARTMENTS.LOCATION_ID= LOCATIONS.LOCATION_ID)
AND (LOCATIONS.COUNTRY_ID=COUNTRIES.COUNTRY_ID))
ORDER BY DEPARTMENTS.DEPARTMENT_NAME ASC

Simple Plain Reports

APEX features several components to help you display the data depending on your needs:

  • Faceted Search or Smart Filters allows users to filter different columns and report to get only the data that they need. You can use this feature in conjunction with a classic report, cards, map, or calendar region.
  • Interactive Reports provide powerful features for customizing your reports such as searching, filtering, sorting, highlighting, group-by, pivot, aggregations, calculations, charting, and more.
  • Interactive Grids include most customization capabilities available in Interactive Reports plus users can rapidly edit multiple rows of data — as simple as clicking on a cell and editing its value.
  • Classic Report features a simple report page based on the formatted result of an SQL query.
  • APEX_REGION.EXPORT_DATA and APEX_DATA_EXPORT APIs to programmatically generate PDF, CSV, Excel, HTML, JSON and XML files.

For a tabular report, you can use a Faceted Search / Smart Filters Page and a Classic Report that allows users to filter the data even more, show charts and download the data. Simply use the Wizard to create pages for your APEX app:

Figure 1. Creating a new Faceted Search page based on a SQL Query 
Figure 1. Creating a new Faceted Search page based on a SQL Query 

There are two ways to generate reports:

  • Download: You can enable this option in the Classic Report region, which allows users to download the report contents to a CSV file.
  • Print: You can also enable this option in the Classic Report region, which allows you to select the report output format (PDF, Word, Excel, HTML, and XML) and the report layout.
    Use Report Layouts in conjunction with a report region or report query to render data in a printer-friendly format which the remote print server supports. Depending on the type of remote print server, a report layout can be designed using:
    • The Template Builder Word plug-in and uploaded as a file of type RTF or XSL-FO.
    • A Word document using a special template syntax.

For a remote print server, you can select Oracle Analytics Publisher, Document Generator Function or APEX Office Print. Any of them, will allow you to download the data using a custom report layout.

Figure 2. Download and Print properties
Figure 2. Download and Print properties

For a Group Above Report, you can use either an Interactive Report or an Interactive Grid. Create the page using the wizard and then run the page to customize the report to meet your requirements:

Figure 3.Customizing the reports
Figure 3. Customizing the reports

In case you don’t have a report region on your page, you can still export the data using the APEX_DATA_EXPORT API by creating a process on your APEX page, for example:

declare
    l_highlights     apex_data_export.t_highlights;
    l_context        apex_exec.t_context;
    l_export         apex_data_export.t_export;
    l_print_config    apex_data_export.t_print_config;
begin

    l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => 'SELECT ALL DEPARTMENTS.DEPARTMENT_NAME "Department Name", EMPLOYEES.FIRST_NAME "First Name", EMPLOYEES.LAST_NAME "Last Name", 
        EMPLOYEES.SALARY "Salary", LOCATIONS.CITY "City", COUNTRIES.COUNTRY_NAME "Country Name", EMPLOYEES.HIRE_DATE "Hire Date"
FROM OEHR_EMPLOYEES EMPLOYEES, OEHR_DEPARTMENTS DEPARTMENTS, OEHR_LOCATIONS LOCATIONS, OEHR_COUNTRIES COUNTRIES
WHERE EMPLOYEES.HIRE_DATE > to_date(''01-01-2000'',''dd-mm-yyyy'')
AND ((EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID) 
AND (DEPARTMENTS.MANAGER_ID = EMPLOYEES.EMPLOYEE_ID) 
AND (DEPARTMENTS.LOCATION_ID= LOCATIONS.LOCATION_ID) 
AND (LOCATIONS.COUNTRY_ID=COUNTRIES.COUNTRY_ID))
ORDER BY DEPARTMENTS.DEPARTMENT_NAME ASC' );

l_print_config := apex_data_export.get_print_config(
        p_body_font_color             => '#4B4540',
        p_page_header                 => 'Report of Employees',
        p_page_header_font_color      => '#4B4540',
        p_page_header_font_size       => 14,
        p_page_header_font_weight     => apex_data_export.c_font_weight_bold,
        p_page_footer                 => 'Your Company',
        p_page_footer_font_color      => '#4B4540',
        p_page_footer_font_size       => 14,   
        p_page_footer_font_weight     => apex_data_export.c_font_weight_bold,
        p_border_width                => 1,
        p_border_color                => '#4B4540');                            

    l_export := apex_data_export.export (
                        p_context      => l_context,
                        p_format       => apex_data_export.c_format_pdf,
                        p_print_config => l_print_config );

    apex_exec.close( l_context );

    apex_data_export.download( p_export => l_export );

exception
    when others then
        apex_exec.close( l_context );
        raise;
end;
Figure 4. Using the APEX_DATA_EXPORT API to export the SQL Query data
Figure 4. Using the APEX_DATA_EXPORT API to export the SQL Query data

To learn more about how to use the APIs, please read Exploring Report Printing in APEX 20.2.

Pixel-Perfect Reports

The Release Notes mentioned at the beginning of this blog post state that Oracle recommends migrating to Oracle BI Publisher for reporting purposes. Let’s review the alternatives you might have for pixel-perfect reports:

Oracle Analytics Publisher

To configure Oracle Analytics with your APEX apps, follow these steps:
1.    Configure your Oracle Analytics instance on Cloud or on-premises as a Print Server on your APEX workspace.
2.    Create your report layout. The layout can be created using the Oracle BI Publisher plug-in for MS Word, which makes report layout design easy by providing a simple drag-and-drop interface.
3.    Create your report query.
4.    Invoke your report from your page.

Document Generator pre-built Function: A recent function to generate PDF documents from a combination of JSON data and MS Word templates. In APEX 24.1, you can now easily integrate the Document Generator Service by configuring it as a remote print server. You can develop your printing templates in a Word document, which the PDF engine combines with your app data to render the final output. Learn more: Seamless PDF Generation with Oracle APEX and OCI Document Generator

APEX Office Print (AOP): A flexible engine enabling Oracle APEX to generate PDF, Word, Excel, PowerPoint, and HTML documents. Also, you can use APEX Office Edit (AOE), which allows you to create and edit documents directly inside your APEX apps. Learn more: https://www.apexofficeprint.com/docs/category/getting-started

JasperReportsIntegration provides an interface to use the JasperReports reporting engine in an Oracle database application, specifically with Oracle APEX. Learn more: https://github.com/daust/JasperReportsIntegration/blob/main/src/doc/github/installation-quickstart.md

Conclusion

Oracle APEX is a full enterprise application platform that enables you to meet your business requirements, whether you are modernizing your Forms apps using APEX or creating your apps from scratch on Cloud or on-premises. Try and explore APEX for FREE on apex.oracle.com.