The customer is a small company so commercial products were not an option. The customer wanted to customize/layout the generated reports. The time available to develop a solution was very limited. After development, the customer needed to be able to make small layout changes independent of the developer (since the developer had other things to do). I first looked at the standard APEX Apache FOP implementation. Then I decided to take a look at JasperReports. The below image displays a report which was created in just a couple of minutes based on data from the sample HR schema present in most Oracle databases.
Apache FOP and APEX reports
In the following Youtube tutorial (by Marc Sewtz), several (Oracle oriented) options are described for generating reports from APEX.
http://www.youtube.com/watch?v=1PiI-D0JmNY
http://www.youtube.com/watch?v=UVasaddrZI4
The below screen capture from one of the slides in the tutorial shows how the PDF is generated from the data and which components are involved;
The actions required in short to get this working;
- install the APEX Listener
- export the report to XML
- create a schema for the XML
- create a FO-XSL transformation to generate a report from the XML
- import the FO-XSL in APEX and configure APEX to use the FO-XSL
In the tutorial, Altova StyleVision is used in order to create the FO-XSL file which is the actual report template.
This method has some drawbacks;
- the data which can be displayed in the report is limited to what APEX can produce in XML
- the APEX listener or a separate application server is required to create a PDF
- altering the report requires first to change the FO-XSL file and then uploading it in APEX
- previewing the report has to be done from APEX or with an exported XML file
Also a commercial product is used for editing the FO-XSL files. When looking a bit further for options of editing XSL-FO files; http://www.w3schools.com/xslfo/xslfo_software.asp the most commonly used are commercial. Since the customer was a small company, commercial products were not viable options. I decided to look for an alternative.
JasperReports
Based on the following post (http://www.openlogic.com/wazi/bid/188176/Comparing-Open-Source-Reporting-Tools-for-Use-in-the-Enterprise) and some customer experience, I decided to take a look at JasperReports (http://community.jaspersoft.com).
Below is an image from the JasperReports architecture (from http://java.dzone.com/articles/java-reporting-part-2) which illustrates it's functioning;
JasperReports Studio (the above image talks about iReport but this product has been succeeded(/rebranded?) by Studio) is an Eclipse based report designer which can be used to access data from the database directly by using JDBC.
Generating reports can be done in several ways;
- directly from JasperReports Studio
- by using a JasperReports library from a Java application
- in conjunction with JasperReports Server
To get started quickly I decided to go for the first option; directly generate reports from JasperReports Studio. Since JasperReports Studio allows creation/editing of the report template and report generation, no application server is required. This reduces the amount of components required to produce reports and the steps required to customize reports. A drawback is however that the query used to generate the report is not linked to what is visible in an APEX screen. You have to manually add the relevant query to a JasperReports report and add parameters to customize the query.
It is of course possible to integrate JasperReports with APEX but that would have required more time then was available. Such a method will not be described in this blog post. JasperReports however is largely Java based so wrapping a report in a servlet and letting APEX send the XML to that servlet will most likely not be that hard. A similar construction used with the Apache FOP solution would then be created.
A nice customizable report in a couple of minutes
Setup
For this example I used the HR schema present in most Oracle databases by default. I downloaded JasperReports Studio community edition and created a DataAdapter.
Oracle requires a specific JDBC driver. This driver can be downloaded from Oracle at; http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html). After downloading you can configure the classpath of the Oracle driver.
Creating a report
Create a new report.
Choose a template report. Simple Blue will do for this example.
Group by department_name
A simple report template is created
Make some small alterations to make the report a little more readable. I did very few alterations (just changed the labels) since this example is for illustrative purposes only. It is relatively easy to make it a lot nicer. Since the report query is SQL based, it is also easy to implement filtering (a WHERE clause) and ordering (an ORDER BY clause). In this example I'll only show filtering based on a parameter.
Click the preview button at the bottom of the report screen. You can save the report in various formats.
Add parameters to the report
Suppose I only want to see data from a specific department. Oracle APEX provides interactive reports to allow filtering. Because we are directly querying the database, this functionality is not available to us. JasperReports however provides report parameters to achieve the same.
Create a new parameter
JasperReports is very flexible. You can use Java types as parameters. When calling a report from Java you can programmatically fill the parameter from the calling program. When you want to be able to ask the user to fill in a parameter however from JasperReports Studio, you are limited to several datatypes (from the Help file, search for 'parameter'); String, Date, Time, Number and Collection. I'll use String for this example. Also I've dragged the properties window to below the outline window for the screenshot.
I next update the where clause in the source of the jrxml file. This also illustrates where you can easily tweak your query. At the top you can find the definition of the parameter which was just created.
For people who are afraid of code (you're reading this blog?!), it is also possible to do this with the IDE. Rightclick Fields in the Outline window. There you can drag and drop the parameter to the query or use the 'Filter expression' function to achieve the same.
Now you can click preview again and generate a report just for the Finance department.
A drawback using this method in comparison to reports directly from Oracle APEX is that you do not get a dropdown box with options.
For the customer I installed JasperReports Studio and they can manually make small changes to the report when it concerns just the layout. For more elaborate changes such as changing the report query, help from a developer is still required. Because of the loose coupling between the report tooling and the APEX application, no additional actions are requires for a report to be updated.
Conclusion
There are various tools for generating reports from the database. In case you are using APEX, the default implementation uses Apache FOP. Oracle BI publisher can also be used. As an alternative, I suggest to also look at JasperReports. JasperReports is a popular reporting tool and has a community edition. It allows creation of report templates from a provided IDE environment. Also it is easy to integrate JasperReports with Java. Depending on your requirements (especially non-functional), JasperReports might be a better fit for your architecture then the default Apache FOP implementation.
No comments:
Post a Comment