Integrate JasperReports with Oracle APEX to Produce Cost Free High-Fidelity Reports

In this post you will learn how to integrate Jasper Reports in your Apex application that will provide a cost free alternative for high-fidelity printing in Oracle APEX. For this purpose you will use a piece of free kit known as JasperReportsIntegration kit to run JasperReports reports from within an APEX application.

The reports are stored locally in the filesystem accessible to the J2EE container (Tomcat 8.5.34) running the JasperReportsIntegration J2EE application (JasperReportsIntegration.war). This application will connect to the desired Oracle schema using connection information deployed in the J2EE container.

Here are the steps for this lab:

Install Oracle XE and APEX

1. InstallOracle XE Database and upgrade APEX to the latest version.
2. Create user ERP in Oracle XE.
3. Log in to APEX admin workspace (http://localhost:8080/apex/apex_admin) and create a new workspace for the application using the ERP schema.
4. Create tables and populate these tables with data.

Download and Configure JasperReportsIntegration

1.  Download JasperReportsIntegration kit from here.

2.  Extract JasperReportsIntegration-2.4.0.0.zip to C:\JasperReportsIntegration

3. Open a command prompt and change directory to C:\JasperReportsIntegration\sql

4. C:\JasperReportsIntegration\sql>sqlplus sys as sysdba

5. SQL>@sys_install.sql ERP (here ERP is the app schema)

6. Exit SQLPLUS.

7. C:\JasperReportsIntegration\sql>sqlplus ERP/<schema password>

8. SQL>@user_install.sql

Install Apache Tomcat

1.  Downloadand install JDK. I installed jdk-7u55-windows-i586.

2.  Download and then launch apache-tomcat-8.5.34.exe file. I used 32-bit/64-bit Windows Service Installer for this lab.

3.  On the Configuration screen, set 8888 for HTTP/1.1 Connector Port. Enter admin in both User Name and Password boxes.

4.  After the installation, start Apache Tomcat services using its icon in the system tray.

5.  Open browser and type localhost:8888 and hit Enter.

6.  Click the manager webapp link (under Managing Tomcat section) and enter admin/admin for username and password. This will show the Tomcat Web Application Manager page.

7.  Close the browser and stop Apache Tomcat services using its icon in the system tray.

8.  Copy files ojdbc6.jar and orai18n.jar from C:\JasperReportsIntegration\lib to C:\Program Files\Apache Software Foundation\Tomcat 8.5\lib.

9.  Copy file JasperReportsIntegration.war from C:\JasperReportsIntegration\webapp to C:\Program Files\Apache Software Foundation\Tomcat 8.5\webapps.

10. Restart Apache Tomcat service.

11. From

C:\Program Files\Apache Software Foundation\Tomcat8.5\webapps\JasperReportsIntegration\WEB-INF\conf folder, open application.properties file and set the following parameters:

[datasource:default]

type=jdbc

name=default

url=jdbc:oracle:thin:@127.0.0.1:1521:XE

username=ERP

password=ERP

12.   Stop and restart Apache Tomcat service.

Grant Network Privileges

1.       Open a command prompt and switch to C:\JasperReportsIntegration\sql.

2.       C:\JasperReportsIntegration\sql>sqlplus sys as sysdba

3.       SQL>@sys_install_acl.sql ERP (here ERP is the app schema)

Everything is set. The next move is to create a report and integrate it with Oracle APEX, which is discussed in Generating High-Fidelity/PDF Reports in Oracle APEX Using JasperReports.

Watch latest Oracle APEX videos