TIBCO Jaspersoft software tutorial: Integrating JasperReports Server with an application in Oracle APEX

In the first previous article about Jaspersoft’s software – TIBCO Jaspersoft Studio tutorial: Creating templates and integration with JasperReports Server – I’ve shown you the basics of Jaspersoft Studio (such as creating report templates), and also covered the subject of integrating it with JasperReports Server. Now it’s time to integrate the finished template into the application. The next stage of the project is to make the “document printing from user interface” service available. For the example of a simple application prepared in Oracle APEX technology, I will describe the method of integration with JasperReports Server. 

In this part of the article, you will find information on how to use the service – REST Web Service API. You will also learn how to use everything you made so far  (following the previous parts) in your application. Examples of integration will be presented in an application created in Oracle APEX. 

Getting ready

I assume you have read the previous article. You should have JasperReports Server installed and configured, and there should be a valid VAT invoice template in the repository. Besides that, you still need an invoice user, which you can add to Jasper according to Jaspersoft instructions.

• Login: invoice,
• Password: invoice,
• Role: ROLE_USER.

Before you continue reading this article, I recommend you to install the invoice schema for the Oracle database. You can find the schema in the Download section below. There you can find the necessary structures and data printed on the invoice.

Preparing the application

In the following section, you will prepare an application using the Oracle APEX technology. If you do not have APEX installed on your database instance, visit Oracle’s website, download the current version, install it, and then follow the steps below. If you have problems installing APEX, please refer to the documentation on Oracle’s website. It is recommended to have both – APEX and JasperReports installed on your local machine, which will make this tutorial easier.

1. Log in to the website: http://localhost:port/apex/apex_admin, entering the appropriate value in place of port.
2. Create a new Workspace, name it INVOCING.
3. Add a new user, for example: invoice as workspace administrator.
4. Log in to http://localhost:port/apex/ (again, enter the appropriate value in place of port) by typing INVOICING, invoice, invoice in the form.

If you have correctly logged in to APEX Builder, you can create a new application or import the one I provide here.

APEX, a sample application inside the Builder.

Integrating JasperReports Server with an Oracle APEX application – Printing a document

You need one PL/SQL process under the Button component. Below is the code that, with a little modification, should implement the document printing process.

DECLARE
  v_blob                 BLOB;
  v_file_name            VARCHAR2 (25) := 'invoice.pdf';
  v_vcContentDisposition VARCHAR2 (25)  := 'inline';

  v_invoice_id VARCHAR2(10) := :INVOICE_ID; -- your NumberField Item
  v_hostname   VARCHAR2(30) := :YOUR_HOSTNAME; -- your hostname, eg: localhost
  v_port       NUMBER       := :YOUR_PORT; -- port for your JasperReports Server, eg: 8081
  v_username   VARCHAR2(50) := :JASPER_USER; -- jasperreports server username 
  v_password   VARCHAR2(50) := :JASPER_PASSWORD; -- jaspereports server password

  v_jasper_string VARCHAR2(30) := v_username || ';' || v_password;

  v_login_url  VARCHAR2(100) := 
    'http://' || v_hostname || ':' || v_port || '/jasperserver/rest/login';
  -- modify below URL before use!
  -- you should modify the line below; change /Pretius/ to your own name
  -- before you add a line try your URL in a web browser
  v_report_url VARCHAR2(100) := 
    'http://' || v_hostname || ':' || v_port || '/jasperserver/rest_v2/reports/reports/Pretius/' || v_file_name;
BEGIN

  -- log into jasper server
  v_blob := apex_web_service.make_rest_request_b(
    p_url => v_login_url,
    p_http_method => 'GET',
    p_parm_name => apex_util.string_to_table('j_username;j_password',';'),
    p_parm_value => apex_util.string_to_table(v_jasper_string,';')
  );

  -- download file
  v_blob := apex_web_service.make_rest_request_b(
    p_url => v_report_url,
    p_http_method => 'GET',
    p_parm_name => apex_util.string_to_table('invoice_id',';'),
    p_parm_value => apex_util.string_to_table(v_invoice_id,';')
  );

  --OWA_UTIL.mime_header ('application/pdf', FALSE);  -- view your pdf file
  OWA_UTIL.MIME_HEADER( 'application/octet', FALSE ); -- download your pdf file
  HTP.p('Content-Length: ' || DBMS_LOB.GETLENGTH(v_blob));
  HTP.p('Content-Disposition: ' || v_vcContentDisposition ||'; filename="' || v_file_name || '"');
  OWA_UTIL.http_header_close;
  WPG_DOCLOAD.DOWNLOAD_FILE(v_blob);

  APEX_APPLICATION.STOP_APEX_ENGINE;

EXCEPTION
  WHEN OTHERS THEN
    RAISE;

END;

The code above is a call to the REST Web Service API provided from your JasperReports server. First, the server requires authentication (line 24.) and from that, the code below follows:

http://localhost:8081/jasperserver/rest/login?j_username=invoice&j_password=invoice

In the next part, the address is called, which allows you to download the document from the specified template (line 32.). Notice how the invoice number is passed to the invoice_id parameter. The result is an URL address like the one below and Oracle BLOB is returned.

http://localhost:8081/jasperserver/rest_v2/reports/Pretius/invoice.pdf

ACL Permissions

The above code will not work correctly if the invoice user on the Oracle database is not associated with a localhost computer in the Access Control List (ACL). The following code, with some corrections, adds the invoice user and localhost to the ACL list.

DECLARE
  v_acl_name VARCHAR2(30) := 'apex-network.xml';
  v_acl_desc VARCHAR2(100) := 'Connection from APEX to Jasper Server for Invoicing';
  v_acl_pric VARCHAR2(30) := 'APEX_050000'; -- change if you installed newer APEX
  v_acl_host VARCHAR2(30) := 'localhost'; 
  v_acl_port_lower NUMBER := null; -- change if needed
  v_acl_port_upper NUMBER := null; -- change if needed
BEGIN
  dbms_network_acl_admin.create_acl(
          acl => v_acl_name,
          description => v_acl_desc,
          principal => v_acl_pric,
          is_grant => true,
          privilege => 'connect'
        );

  dbms_network_acl_admin.add_privilege(
          acl => v_acl_name,
          principal => v_acl_pric,
          is_grant => true,
          privilege => 'resolve'
        );

  dbms_network_acl_admin.assign_acl(
          acl => v_acl_name,
          host => v_acl_host,
          lower_port => v_acl_port_lower,
          upper_port => v_acl_port_upper
        );

  COMMIT;
END;
/

Executing

If you have downloaded and installed the shared application, now go to: http://localhost:port/apex/f?p=ID, where port is the port number the APEX listens on, and ID is the ID of the imported application. You should see a login screen and then a page like the one on the screen below.

Fill in the required details.

Fill in the form by providing the information requested by the field names, and then click the Print invoice button, which will download the file for the indicated invoice number. You can preview the downloaded file here.

If you followed all the steps as instructed, you should see the VAT invoice document on the screen. From here I encourage you to look deeper into the moment when the document is printed. I suggest you check how the application behaves when we change one line of the provided PL/SQL code. For example, change:

v_file_name VARCHAR2 (25) := 'invoice.pdf';

To:

v_file_name VARCHAR2 (25) := 'invoice.xlsx';

JasperReports Server can handle many different data formats. For the inquisitive, I provide an archive with the document in the most popular formats. You can find it here.

Downloads

• APEX application – APEX JasperIntegr,
• Invoice PDF – invoice.pdf,
• Database schema – ora-schema-invoice.zip,
• The invoice in the most popular formats – Invoices Pack.

Performance and security of JasperReports Server

An installed and pre-configured print server fully accomplishes the mandated task. However, many questions arise. Is the adopted solution efficient and secure? How many requests can be handled simultaneously? Can anyone preview a file they are not supposed to see, by just calling the document URL? Let’s try finding some answers.

The purpose of testing

My purpose was to test response times and resilience to a large number of simultaneous requests to JasperReports Server. When the server is installed, the default settings are set to a maximum of 100 concurrent users. See the Jaspersoft documentation for information on how to change these settings to make the server accept a larger number of simultaneous requests from users.

JasperReports Server Requirements

According to the Jaspersoft documentation, the minimum and recommended server requirements when installing JasperReports Server are as follows:

However, according to Jaspersoft, the recommended parameters should be taken as an example. In practice, a production system may require more or less resources than assumed in the documentation. It depends on the load, the number of active users, queries to the database and its configuration, as well as on whether the database and the JRS application are installed on the same machine.

The tested JRS is installed on a local computer. The PostgreSQL (JRS repository) and Oracle (invoice data) databases are also on the same computer.

Tools

Apache JMeter program was used to analyze the performance of JasperReports Server You can download it here (I used version 2.13). The invoice template provided in the previous part of the article was used as a sample report.

Test cases

The following test scenario was conducted (description in Gherkin):

Test results

The following is a summary of the aggregated results from Jmeter.  The summary aggregates all individual URL requests.

Test 1. Scenario (1): REST Web Service API – export invoice to PDF

Settings: Tomcat (context.xml): maxActive = 100; PostgreSQL (postgresql.conf): max_connections = 100

Test 2. Scenario (1): REST Web Service API – export invoice to PDF

Settings: Tomcat (context.xml): maxActive = 1000; PostgreSQL (postgresql.conf): max_connections = 1000

Test summary and thoughts

Before I summarize conclusions, I need to point out that the tests were performed on HP ProBook 6570b laptop with a standard configuration of Tomcat, Oracle, and PostgreSQL services, and not in laboratory conditions. The data should be considered illustrative. It’s not objective data about the system.

The purpose of this test was to show how JasperReports Server behaves with a maximum pool of concurrent requests being served. You can see that JRS does quite well when we set the value 1000 for the maximum number of simultaneous connections. However, as the maximum pool increases, the average request handling time increases. This is due to the work of the database, which due to the configuration, allocates fewer resources per connection. It’s different with 100 simultaneous connections to the database. The average time of handling one request is lower, but the problem arises when trying to handle the maximum number of simultaneous connections. The number of unhandled requests (HTTP code 500 or 403) increases with the higher number of connected users.

There are many ways to improve server performance when, for some reason, a large number of simultaneous connections are expected or when trying to transfer a large amount of data over HTTP.

1. always write optimal SQL queries. Pay attention to the query cost and optimize where possible.
2. If your templates return a lot of content, think about compressing HTTP packets to reduce queues on the application server. More on this topic here.

3. if you often run the same report, think about caching for static files like CSS files, images. Read more about it here.
4. Tomcat Server by default monitors application changes on disk and deploys again when there are changes. This behavior puts load on the CPU and RAM and it’s recommended to disable this service. Read more about it here.

I encourage you to read the Jaspersoft documentation for more details. Also, you can cluster multiple JasperReports Servers together, whether it’s the commercial or free version. This means that JRS can run in high-availability mode. The condition is that the JRS instances are identical (same version and configuration). More about this can be found in the documentation.

Security

Making sure that users see only what they should see is an important security issue. A single instance of JasperReports Server can work on multiple data from different sets which creates uncertainty about whether users will have integral access to the resources intended for them. In this situation, we want users to only see the data that is intended for them. This is an important security issue to ensure the integrity of the views we provide to our users. Fortunately, JRS allows this configuration to protect our data from unauthorized access.

• Users have their own integral permissions, which in turn we can bind by combining accounts into groups. Each account is protected by a password.
• The administrator defines which objects users and user groups have access to.
• A JasperReports Server instance can support multiple organizations. Each organization is isolated from the others (except for public resources). Entire user groups can belong to an organization, and in addition, each organization can have its own administrator. The service is only available in the Professional version. More information can be found here.

User authentication

In JasperReports Server, each group can contain any number of users, and each user can be in multiple groups at the same time, with all the permissions of the groups to which they belong. The question remains: how to authorize users in the system? JasperReports offers many possibilities. Some options:

• LDAP authorization,
• Central Authentication System (CAS),
• Java Authentication and Authorization Service (JAAS),
• Anonymous login,
• others such as SiteMinder, Container security.

JasperReports – alternative methods for printing documents

While searching for the best solution related to reporting to selected formats, I found several other tools from different companies. The free solution offered by TIBCO Jaspersoft is an interesting alternative to commercial solutions. However, it’s worth mentioning what other products – both commercial and free – are available on the market. In this part of the article, I will present a few options you can use in your application, and each of them is worthy of attention.

Eclipse BIRT

Open-source software for creating and visualizing data in the form of reports. You can also use a web browser. Similar to Jaspersoft Studio, it allows you to create reports in a dedicated environment based on Eclipse IDE.

http://www.eclipse.org/birt/

Oracle BI Publisher

A very powerful tool that, among other things, allows you to create templates in MS Office and download reports in many different formats. Its downside is definitely the price, which most smaller companies won’t be able to afford. Definitely, a product meant for big players only.

http://www.oracle.com/technetwork/middleware/bi-publisher/overview/index.html

PL/PDF

Product of Oranext company. Like BI Publisher it allows you to create templates from MS Office. Free to download and use for 28 days. Allows you to print PDF documents from PL/SQL code using the available API. An expensive solution, but cheaper than BI Publisher.

http://www.plpdf.com/

Oracle Reports

Oracle’s tool that allows you to create and print reports to a variety of formats.

http://www.oracle.com/technetwork/middleware/reports/overview/index.html

ORDS – Oracle REST Data Services

Running independently of APEX on a Java application server, for example, Tomcat. One of the capabilities of this solution is printing the contents of an APEX page to a format of your choice, such as PDF.

http://www.oracle.com/technetwork/developer-tools/rest-data-services/overview/index.html

PL-jrxml2pdf

Allows printing PDF reports directly from PL/SQL code. Supports some of the functionality of JasperReports. It uses .jrxml files – templates created with Jaspersoft iReport Designer. This tool has not been supported since 2016.

APEX Office Print

A project of United Codes company from Belgium. It is a powerful solution for applications designed in Oracle APEX. It is based on document templates in Microsoft Word, Excel, or PowerPoint. It can export your documents to PDF, Microsoft Office files, or HTML. 

http://www.apexofficeprint.com

Conclusion

This concludes the second part of our Jaspersoft tutorial. Between this article and the previous one, you should now know everything you need to, to set things up and use Jaspersoft’s software with Oracle APEX applications. As you can see, these solutions are pretty powerful and secure. They can be a good alternative to various other technologies – even ones you have to pay for.