When deciding on a printing solution for Oracle Application Express (APEX) you should consider things like “is a license required”, “how are layouts handled”, “what output formats are supported”, and “overall ease of use”.Let’s briefly look at these Oracle APEX printing options one by one.
Oracle BI Publisher 
For many years, I used Oracle Reports to handle my APEX reporting needs. Long marketed as “any data, any format, anywhere”, Oracle, however, has made no bones about Oracle Reports not being a strategic option going forward. That way forward is Oracle BI Publisher. It is in every way Oracle’s flagship reporting tool, however, it comes with a hefty price tag.
Oracle has made it easy for business users to maintain a variety of graphs and reports. While developers would still need to expose necessary data points, the presentation layer can be created using a MS Office add-on. Business users can then markup an Office document and reference these fields as variables.
Pros:
- Oracle’s strategic direction
- Templates can be maintained by end users using MS Office
- Plenty of examples used with APEX
- Interactive dashboards in addition to printing
Cons:
- High cost of licensing when compared to other options
- Office plugins may be an obstacle for some end users
Architectural Diagram:
From the field:
“BI Publisher is the preferred Oracle solution for pixel-perfect reporting. Also used for Mobile Application Designer and for direct SQL reporting in OBIEE, BI Publisher is a strategic reporting solution across multiple product lines. When it comes to printing in APEX, I’d use BI Publisher, because I know it’s capabilities, and I know it has legs.”
Stewart Bryson, Red Pill
JasperReports
JasperReports provide you very powerful reporting capabilities. You can produce pixel-perfect documents that can be viewed, printed or exported in a variety of document formats including HTML, PDF, Excel, OpenOffice and Word.JasperReports are 100% Java and 100% free of charge.
Using JasperReports is quite straightforward. A third-party graphical design tool called iReport is commonly used to aid in report design. The resulting report is saved in the form of a JRXML file which is a specialized XML format. The compiled version of the JRXML report file has a .JASPER extension and is submitted to the JasperReports server for execution.
- Free. No license required
- Requires some server setup
PL-jrxml2pdf
PL-jrxml2pdf is a PL/SQL-program which takes a report-definition in jrxml-format and generated a PDF-result from it. It’s pure PL/SQL, so there’s no need for any middleware.
The process is very straightforward. First, design your reports using iReports which results in an JRXML file. then use PL-jrxml2pdf as runtime-engine to generate PDF from PL/SQL.
The software comes with an Oracle APEX-application which installs all required objects and samples. Also the APEX-application allows you
– to manage all PL-jrxml2pdf-objects
– to run reports
– to view logs written when running a report
Pros:
- Free. No license required
- Output limited to PDF
PL/PDF is a collection of PL/SQL packages to create PDF/XLSX/DOCX documents directly from your PL/SQL program without having to install third party drivers or software. Originally, there was no real GUI for PL/PDF and layouts were administered via a series of API calls. Recently, PL/PDF has come out with the PL/PDF Reporter which allows templates to be created using Microsoft Word.
It’s capable of generating PDF, DocX and XLSX files as well as processing XLSX sheets into your Oracle database.
Pros:
100% PL/SQL
Installs into same DB as APEX
Cons:
Maintenance of long report layouts can be tedious
From the field:
“PL/PDF is a great choice when you’re faced with complex business rules. For example, you need to create a report based on a subset of 5 templates, and depending on which products are included in a template, you may have to attach additional documents or even send out notifications. Since the core is PL/SQL, it’s trivial to handle situations like this with PL/PDF, and I typically keep it at the top of my list of recommended tools.”
Scott Spendolini, Sumneva
Oracle REST Data Services (ORDS)
Oracle Application Express in conjunction with the Oracle Rest Data Services (ORDS)allows you to configure a classic report region, interactive reports and report queries to print by exporting it to an Adobe portable document format (PDF), Microsoft Word rich text format (RTF), Microsoft Excel format (XLS), or Extensible Markup Language (XML). By taking advantage of region report printing, your application users can view and print reports that have a predefined orientation, page size, column headings, and page header and footer.
Pros:
Free
No need to configure external printing engine
Cons:
Complex layouts require XSLT editors such as Altova Stylevision or Stylus Studio
Architectural Diagram:
From the field:
“FOP is built into ORDS (previously known as APEX Listener), meaning you get out-of-the-box PDF printing with APEX with no additional configuration required. When compared to third-party solutions, the built-in printing options provide you with full access to your current session state and application data – no separate database connection or authentication needed.”
Marc Sewtz, Oracle Corporation
APEX Office Print (AOP)
This is the newest kid on the block and making quite a splash. Part of the reason is it comes from the mind of Dimitri Gielis, part of the reason is it’s that good. It is the only PrAAS option available. PrAAS is my own acronym that stands for PRinting As A Service. AOP can also be installed on-premise.
Like many of the printing tools, APEX Office Print segregates the data preparation from the data presentation process. The data preparation is a query that aggregates and organizes the data in JSON format.There is HUGE power with the JSON data structures. You can accommodate multiple detail data sets or “loops” within your report.
One of the most amazing tools is AOP’s ability to “print” an APEX interactive report. In your MS Word or MS Excel template, you can put the phrase {&interactive}. The actions taken in your interactive report will be shown in your report. Did you use sorting? Hide / Show columns? Aggregate and have control breaks? Well, these will all appear in your report.
Your reports can be PDF, Word, Excel or PowerPoint
Pros:
- Ease of installation and setup
- Templates can be created using Word, Excel or Powerpoint and without the need of plugins
- Can be called via the provided APEX Plugin or PL/SQL API
- Works with any technology that can call a REST API. It doesn’t require a database of any kind to work.
- Assist template creation with auto creation of initial template based on data provided
Cons:
- Minimum APEX version 4.2
- License required
Architectural Diagram:
From the field:
“APEX Office Print is the only solution integrated with APEX e.g. print of Interactive Report.
Very simple to use! Very simple to setup!”Dimitri Gielis, Apex R&D
Summary
| BI Publisher | JasperReports | JRXML2PDF | PL/PDF | ORDS | AOP | |
| License | Yes | No | No | Yes | Yes | Yes |
| Template | RTF | JRXML | JRXML | PL/SQL | XSL-FO | DOCX XSLX PPT |
| Output | ANY | ANY | PDF DOCX XSLX |
PDF WORD XSLX |
PDF DOCX RTF XSLX PPT |
|
| Ease of Use | * * | * * | * * * | * | * | * * * |
[/vc_column_text]
Another option would be Eclipse BIRT …
I dont know other tools then jasper reports integration, with this tools I was making pdf with more then 600000 pages as report for one or two hours waiting but with tones of gygabytes (: