Integration JasperReports into Oracle E-Business Suite (OEBS)

Some years ago I was asked to design a report in MS Excel format in OEBS. Also received xls file had to be zipped and sent by email. Last two requirements could be easy implemented using Java. Since I had already decided to use Java I attempted to integrate JasperReports into OEBS too. In my oppinion JasperReports is more powerful reports' generator than Oracle Reports 6. Withal JasperReports has build-in export a report in xls format.

In those days I used OEBS 11.5.10.2. It had jdk 1.4.2_04 on board. There were some troubles with choice of JasperReports version. Although JasperSoft declared that last version compatible with jdk 1.4 was 3.7.1, in my case only 3.2.1 version shew stable work. Unfortunately I can't find respective link on Jasper Forum now to prove this assertion.

Well, let's do, step by step, all things we need to receive a simple report in xls format.

Invoking RESTful web service in Oracle PL/SQL

UTL_HTTP package allows to invoke web services via pure Oracle PL/SQL. Here the invoking of RESTful web service will be shown. Obviously, server where Oracle is deployed must have an access to host with web service.

To perform of the invoking the following steps should be done

SUM for strings in Oracle

At times I have needed in an aggregate function to build a list of varchar values in single row inside a group. Something like SUM aggregate function but for strings. I wanted to have for each group a list of strings separated by defined delimiter.

Parent/child dimensions in Jasper Analysis

Here I want to share my experience of using a dimension based on tree-like table. As database engine I used Oracle.

As known tree-like table is a table that has pair of columns, usually called as ID and PARENT_ID. Values of PARENT_ID column in children rows reference to values of ID column of their parents. Usually tree's element without any parent is called root, an element that has children is called node and an element without any children is called leaf.

Simulation of outer joins in Jasper Analysis

JasperReports Server (Pentaho Mondrian behind the scenes) doesn't use outer joins when parses schema file to build analysis view. Today, at least.

Here I am describing the one of possible ways to change JasperServer behaviour as though it can handles outer joins. We don't have to make changes in any configuration, the solution just encloses manipulations with SQLs have used in schema definition.