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.
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.
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.
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.
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.
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
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; /
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.
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.
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.
According to the Jaspersoft documentation, the minimum and recommended server requirements when installing JasperReports Server are as follows:
Resources | Minimum | Recommended |
HDD | 10GB | 40GB+ |
RAM | 4GB | 8GB+ |
CPU | 2 cores | 2.5GHz + multiple cores |
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.
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.
The following test scenario was conducted (description in Gherkin):
Id | Module element / Action | Description |
1 | REST Web Service API – printing a file | A user logs to the system via URL
|
A user opens the URL with the PDF file
|
||
The PDF file appears on the screen.
|
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 ID | Number of users | Average [ms] | Median [ms] | Min [ms] | Max [ms] | % of errors | Bandwith | KB/s |
1 | 1 | 478 | 478 | 478 | 478 | 0 | 2,1 | 17,5 |
2 | 10 | 1584 | 1647 | 853 | 1811 | 0 | 4 | 33,4 |
3 | 25 | 3277 | 3411 | 588 | 4369 | 0 | 5,2 | 43 |
4 | 50 | 4774 | 5027 | 2363 | 6135 | 0 | 7,4 | 61,4 |
5 | 75 | 8355 | 8868 | 4611 | 10338 | 0 | 7 | 58,4 |
6 | 100 | 9212 | 9940 | 3318 | 12552 | 26 | 7,4 | 70,6 |
7 | 150 | 12075 | 13099 | 4315 | 18997 | 50,67 | 7,9 | 62,7 |
8 | 200 | 11923 | 10199 | 4983 | 22099 | 65,5 | 8,9 | 77,8 |
9 | 300 | 14813 | 13653 | 2285 | 26933 | 64,67 | 10,6 | 155,2 |
10 | 500 | 18334 | 18262 | 1914 | 35283 | 81,4 | 12,2 | 114 |
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 ID | Number of users | Average [ms] | Median [ms] | Min [ms] | Max [ms] | % of errors | Bandwith | KB/s |
1 | 1 | 419 | 419 | 419 | 419 | 0 | 2,4 | 19,9 |
2 | 10 | 1812 | 1784 | 1399 | 2221 | 0 | 3,6 | 30,3 |
3 | 25 | 3181 | 3441 | 1876 | 4250 | 0 | 5,5 | 45,6 |
4 | 50 | 5520 | 5781 | 2418 | 6822 | 0 | 7 | 58,2 |
5 | 75 | 8674 | 9224 | 5524 | 10726 | 0 | 6,7 | 55,6 |
6 | 100 | 14861 | 15273 | 7643 | 17065 | 0 | 5,7 | 47,8 |
7 | 150 | 23251 | 24459 | 7227 | 26967 | 0 | 5,4 | 45,2 |
8 | 200 | 28066 | 30138 | 11705 | 34233 | 0 | 5,7 | 48 |
9 | 300 | 34291 | 38923 | 1713 | 46599 | 0 | 5,9 | 49,2 |
10 | 500 | 60748 | 64059 | 1118 | 87200 | 0 | 5,4 | 45,3 |
11 | 1000 | 98819 | 99418 | 977 | 160420 | 0 | 5,9 | 48,9 |
12 | 1100 | 102113 | 102518 | 16893 | 166046 | 0 | 6,2 | 51,5 |
13 | 1500 | 135774 | 135476 | 1125 | 223970 | 0 | 6,1 | 50,7 |
14 | 2000 | 176384 | 175358 | 43115 | 293255 | 0 | 5,9 | 49,6 |
15 | 3000 | 276898 | 268815 | 788 | 456270 | 0 | 5,4 | 45,5 |
16 | 4000 | 387391 | 372773 | 1308 | 613975 | 0 | 4,9 | 40,7 |
17 | 5000 | 567229 | 540106 | 1917 | 828373 | 0 | 3,8 | 31,5 |
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.
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.
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.
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:
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.
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.
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
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.
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
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
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.
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
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.