HOWTO: Use remote XML data source for chart generation in JasperReports

July 14, 2009

by Jason Buberel

3 comments

From the very start we’ve been using the wonderful open source reporting engine JasperReports to produce the PDF reports that we offer. Of course, that work all started nearly 5 years ago, and we’ve been upgrading and updating ever since. For our next round of report enhancements (hint: configurable themes), we’re also transitioning our chart rendering to take advantage of some of the latest features of the JasperReport charting engine – there is no sense in reinventing wheels after all.

Never willing to leave well enough alone, we decided to push the envelope a bit (read as: use JasperReports in strange and undocumented ways) by building the new charting rendering code to use our XML web service APIs for data retrieval. After an afternoon of reading through the Java source code of the JasperReports engine (did I mention how much we appreciate open source development?) we managed to make it all work without having to resort to any especially arcane hackery. In order to save others from duplicating our efforts, below is a description of how we got it all working.

A basic outline of what we wanted to accomplish:

  • As the primary report data source, use a standard SQL query to retrieve basic information for a specify city and state.
  • Group the results of that outermost query by ZIP code.
  • For each ZIP code returned, render a chart of the median price.
  • Retrieve that chart data from a remote XML data API using the State/City/ZIP as the parameters to retrieve the correct data
  • Use a standard XPath query to extract the rows and columns of data from the XML reply, and feed that into the charting engine

Should be easy, right? To increase the challenge level, we also decided that should accomplish all of the above without having to write gobs of customization code. We’re in the business of providing real estate market data, not charting software, after all.

To get started, we created a basic JasperReport using that latest version of the GUI report builder tool, iReport (v3.5.2). For our proof-of-concept report, we have a simple SQL query that connects to our database over a standard JDBC connection. A simplified version of that query looks something like this:
SELECT state, city, zip, median_price, median_inventory
FROM stats
WHERE state = 'CA' and city = 'SUNNYVALE'
GROUP BY zip
ORDER BY zip ASC;

In the report, we defined just a few simple String fields to be used for looking up the price chart data:

  • $F{STATE}
  • $F{CITY}
  • ${ZIP}

With these fields defined, we then added a new dataset to the report (see screen shot below):
Adding a new dataset to the report

Our XML data service API will return values in a very simple format:
<data>
<row date="2009-01-01" c1="200222.11"/>
<row date="2009-01-02" c1="222211.09"/>
...
</data>

We named our new dataset ‘xml_dataset’, and configured the query language as ‘xpath2’ and defined the xpath query string as ‘/data/row’ to match our actual XML output:
Configuring the xml dataset

Next, we manually defined two fields, named ‘date’ and ‘c1’ (note: ‘c1’ stands for ‘column 1’ and is just a generic data column header). These each match the two attributes on each <row> element in the XML reply that we will process. The descriptions for each of these properties was then defined to be the XPath expressions ‘@c1’ and ‘@date’ to correspond to the actual attribute names:
c1 field definition
And the date field:
date field definition

Now comes the fun part – binding these fields to a chart that uses a remove URL to retrieve the XML. To start, we inserted a time-series chart object into the details band of our report, which (as mentioned earlier) was grouped by ZIP code (note the group bands – ‘city_summary_zip Group Header 1’ etc.)
insertion of chart into details band

We then configured this chart object as follows (right click on chart and select ‘Chart Data’), the two import options are highlighted:
Defining the chart data

Here is where it gets clever – the actual datasource expression. You can’t see it fully in the screen capture, but here is the text:
new net.sf.jasperreports.engine.data.JRXmlDataSource(
net.sf.jasperreports.engine.util.JRLoader.getLocationInputStream("http://server.com/getxmldata?state="+ $F{STATE} +"&city="+ $F{CITY} +"&zip="+ $F{ZIP}), "/data/row")

Let’s break that down a bit:

  • Create a new datasource of type JRXmlDataSource
  • The first constructor parameter will be an standard XML ‘Document’ object returned by the JRLoader.getLocationInputStream(String url) method. The value of that URL is our remote server XML feed, and uses 3 field values for state, city and ZIP which are defined by our outermost SQL query (see above).
  • The second parameter to the JRXMLDataSource constructor is the same XPath expression as we defined in our dataset ‘xml_dataset’.

Now, on the ‘details’ tab of the ‘Chart Data’ dialog we tell the chart how to handle our data:
Defining the data series
The details of that Time series are show below:
Price series definition

  • Series Expression: This just creates a new String with value “Price” which is the name that will be displayed on the chart for this data series, as the values are the ‘Median Price’ as defined in our query.
  • Time Period Expression: This takes each ‘date’ value extracted from the XML feed and converts it from a String into a Date object, which is required for the X-axis of a time-series chart.
  • Value Expression: This takes each column ‘c1’ value and parses a Java Double object out of the string value returned in the field ‘$F{c1}’. This can then be later formatted to show currency, etc.

Previous post:

Next post: