Oracle Reports and the Alternatives

Apr 24, 2017

„Oracle Reports released with Oracle Fusion Middleware’s 12.2.1.3.0 will be its terminal release. Oracle Reports will be supported in accordance with the Oracle Fusion Middleware Lifetime Support Policy. Customers are advised to use Oracle BI Publisher for their reporting needs.“

Oracle Reports Orientation DayAs mentioned in the statement of direction from October 2016 the next Oracle Forms release 12.2.1.3.0 will be the last which ships out Oracle Reports. So Reports will not be part of future Oracle Forms releases, which leaves customers with the question of what to do. Oracle intends to push Oracle Forms customers towards the usage of BI Publisher, which is Oracle’s preferred replacement for Reports. Therefore Oracle Forms 12c has been delivered with an much easier integration of the BI Publisher Reporting solution.

But are there any other alternatives out for Oracle Forms customers? In this blogpost we would like to give you an overview over the two most frequently mentioned solutions when it comes to Oracle Reports alternatives: Oracle BI Publisher and Jasper Reports.

BI Publisher

If Oracle is asked, then there is only one real Oracle Reports replacement: Oracle BI Publisher. But BI Publisher (in the past XMLPublisher) is not a one-to-one replacement for Reports – it’s rather a different concept for reporting that is quite different to Oracle Reports.

BI Publisher divides the reporting into data, layout and language.

This changes the flow of work when it comes to the creation of a report. The advantage is, that reports that use the same data source but have a different layout must not be created multiple times. For creating these different layout-templates the development environment or a Microsoft Word Add-in can be utilized. An additional advantage of BI Publisher compared to Oracle Reports is that BI Publisher supports many sources and export formats.

One is not limited to an Oracle Database as a source of data – you can use different other sources like WebServices, files or external systems. Those can be exported to a wide range of formats like: PDF, PPT, SML, Excel to different output channels (printer, filesystem, mail etc.). BI Publisher additionally offers a dashboard functionality with which users can create their own reports. All of those mentioned possibilities make BI Publisher to a not to cheap Enterprise Reporting solution.

If you have licensed the iAS-EE the use of BI Publisher is free of an extra charge.

R

Pros

  • Full support from Oracle
  • Multiple format output
  • Oracle Migration Assistant (not error-free)
  • Central reporting solution
  • Existing PL / SQL logic can be reused
  • Loose couple data, layout and distribution
  • Multi Language Support
  • Easy Integration in Forms/APEX & ADF
  • Reports Scheduling & Bursting
Q

Cons

  • License fees (for non iAS customers)
  • No fully automated migration of Oracle reports

Jasper Reports

Before I would like to go over the benefits of using JasperReports here some points of the history of the product:

}
2001 — Created and launched by Teodor Danciu
}
2004 — Teamed up with Panscopic, forming Jaspersoft JasperReports Server, providing  advanced report server capabilities, also open source
}
2014 — TIBCO acquired Jaspersoft

So you can see, that the product itself already has quite a history.

So why should you consider Jasper Reports as a serious alternative to Oracle Reports? Well there are quite a few good reasons, but the main one: It’s open source – well at least to a certain degree. Jasper Reports is developed and maintained by Jaspersoft. You can create your JasperReports using the main library and distribute them as long as you don’t change the source code. If you need further support from JasperSoft or different licensing, then you have to buy additional products.

The second main reason for Jasper is, that it is written in Java, which is widely used. JasperReports can handle various data source options: JDBC, Hibernate, XML, EJB, POJO. While there are many data sources possible, there are loads of export formats available: PDF, HTML, XLS, RTF, ODT, XML, CSV, TXT, DOCX.

R

Pros

  • Flexible layout
  • Multiple data sources – more than BIRT
  • Multiple format output
  • Jaspersoft Studio = WYSIWYG, Eclipse-based
  • Expressions language: Java, JavaScript, Groovy
  • Matrix reports, conditional formatting
  • Supported platforms: Windows, Linux, Mac OS
  • High quality page printing output
  • Plugins for version control, including Subversion
  • Generates reusable sub-reports
  • Charting, graphics
Q

Cons

  • Difficult to learn
  • SQL-designer available, but difficult to use

While using Jasper Reports has some benefits it comes with some negative aspects and problems when you are moving from Oracle Reports. The biggest problem for most Oracle Reports developers will be the fact, that Jasper is not supporting PL/SQL. So if there is a lot of logic or validation in your reports we recommend to move the PL/SQL Code to the database and refactor the validation within Java.
Another problem could be, that you need Java knowledge to utilize JasperReports: If you already have Java knowledge in your company your Java developers will find it easier to work with JasperReports, but if your developers are pure PL/SQL developers Java training to a certain extend is not a bad idea. Also Java resources are quite common (Java is still the most famous programming language).

ProblemSolution

No PL/SQL supportmove PL/SQL to DB/Java
No validation triggersrefactor logic with Java
Generally limited triggersrefactoring, sub-reports
No detailed filtering of query groupsrefactoring
No PL/SQL formula columnsrefactoring
Too many sub-reports can result in very poor performance because each sub-report opens its own database connection, thread and queryperfomance test + refactoring, if necessary

Summary

There is currently some pressure on Oracle Forms customers – we know that Oracle Forms 12.2.1.3.0 will be the last shipped with Reports. So there is the need for Oracle Reports developers  to look into the alternatives that the market provides. There are other alternatives available (BIRT, plpdf, etc…) while BI Publisher and JasperReports are probably one that most customers look into.

BI Publisher’s big advantage is that existing PL/SQL logic can be reused and if you have the iAS-EE license now free of use.

For JasperReports speaks, that it’s open source in parts and written in Java – if you plan to get away from Forms to Java at all I would consider using JasperReports for the Reporting. Both BI Publisher and JasperReports offer a wide range of input and output formats.

by Christian Piasecki & Daniel Kampf

Transform Your Oracle Forms Applications

PITSS can give you a complete analysis or your Oracle Forms and Reports applications. With an Oracle Forms upgrade analysis and the help of PITSS.CON, we’ll show you the opportunities for automated, potentially automated, and manual processes necessary to upgrade or migrate your code.