Using iReport with Query Output

Because Oracle has stopped delivering Crystal Reports with PeopleSoft, I heard a few discussions about whether or not to license it for a new installation.  So, it’s an perfect timing to explorer an alternative tool.

Let me introduce JasperReports.  It is open-source, Java-based report generation tool.  The reports are XML files, but they have a tool called iReport that, in my opinion, is very similar to Crystal Reports.

Here’s the descriptions from their website:

iReport:

iReport is the free, open source report designer for JasperReports. Create very sophisticated layouts containing charts, images, subreports, crosstabs and much more. Access your data through JDBC, TableModels, JavaBeans, XML, Hibernate, CSV, and custom sources. Then publish your reports as PDF, RTF, XML, XLS, CSV, HTML, XHTML, text, DOCX, or OpenOffice.

Jasper Reports:

JasperReports is the world’s most popular open source reporting engine. It is entirely written in Java and it is able to use data coming from any kind of data source and produce pixel-perfect documents that can be viewed, printed or exported in a variety of document formats including HTML, PDF, Excel, OpenOffice and Word.

So, as an introduction, let me give you a walk through introduction of using iReport to design a report based on a Query.

First, you need to export your Query results to an XML file:

Now, save the XML in a location that you will remember, and move on to installing iReport.  Simply download it from their website.  I downloaded iReport-4.1.3.tar.gz and unzipped it.  In the bin folder, you will find the iReport program that you can launch:

First step toward creating your report is to build your datasource.  Click the “Report Datasources” button on the toolbar.

Click the “New” button on the right side to add your new datasource.

Since you have run the query to an XML file, you have to choose “XML file datasource”.

Enter your details for the datasource:

• Name: enter any name to help you remember what it is
• XML File: the location where you saved the data from the query
• Date pattern: yyyy-MM-dd

Now, you have a data source.  So, use File > New… to create your new report.

If you want to build everything yourself, you can choose a blank letter template.  I am going to have some fun and choose the “Coffee” template.

Next, select the datasource that you added earlier.

Unfortunately, you won’t have any fields to select at this point in the process.  You’ll have to configure the fields later after building the report.

Also, the Group by screen isn’t of much help without fields, so just slide past it, too.

Now, you are finished with the wizard:

Now, you should have a report with everything but fields.

The first change we need to make is to add the “query”.  Right click on the report top-level in the Report Inspector and select “Edit Query”.

Now, change the query language to XPath and enter “/query/row” as the query:

Now, select the tags from the Row tag that you want to use as fields.  You can just drag them from the right side over to the fields list.

Once you get all the fields added, you can click OK.

Now, you can drag your fields across to the report.  I think it helps to drag the Header area and Detail area larger.

It should take just a few minutes to get the layout the way you want it:

When your done, you can view the final result with the “Preview” button.