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:

Running query results to XML

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:

Jaspersoft iReport Designer 4.1.3

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

Datasources Button

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

Database Window

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

iReport Datasource Type Window

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

Datasource Details

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

New Report Window

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.

New Report Location

Next, select the datasource that you added earlier.

Selecting Datasource for Report

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.

iReport Field Selection

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

Field Selection

Now, you are finished with the wizard:

Finished with New Wizard

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

Report Created

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”.

Editing the Query

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

Entering 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.

Selecting Fields

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

All Fields

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

Adding Fields to Report

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

Report Laid Out

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

Preview


Share

No Comments

1 Trackbacks

Leave a comment