Question: How to use an XML dataset as chart data

[jbuberel]

I am using iReports v3.5.2.

I have successfully tested the use of a remote XML data source and XPath2 query as the primary query for a test report. This works without any problems.

Haveing established the ability to use a remote XML URL as a data source, I would now like to create a report that uses an SQL query as the primary data source, and in turn uses a remote XML data source for a chart placed into the details band.

The group parameter of my primary SQL query is ZIP code, and each detail chart will contain a set of timeseries data related to that ZIP code (historical price values). I have defined a report dataset named 'xml_dataset' and set the query language for this as 'XPath2'.

I now need to bind that XML dataset query to my chart. In the screenshot attached, you see the Chart Details dialog, which I need to specify the datasource for my chart. What I am trying to figure out is:

What is the expression that I would use to bind the chart to the 'xml_dataset' (defines two fields from an XPath expression: 'date' and 'c1') to a remote XML datasource that uses the ZIP code from the report-level SQL query:/uploads/projects/ireport/greenshot_2009-07-13_19-38-37.png

http://server.net/get/chart/data?zip=

which returns the XML that the 'xml_dataset' XPath query expects:

<data>    <row date="2009-03-20" c1="$631,023" />    <row date="2009-03-27" c1="$641,369" />    <row date="2009-04-03" c1="$656,561" />    <row date="2009-04-10" c1="$671,488" />    <row date="2009-04-17" c1="$687,984" />    <row date="2009-04-24" c1="$706,753" />    <row date="2009-05-01" c1="$724,792" />    <row date="2009-05-08" c1="$741,014" />    <row date="2009-05-15" c1="$753,726" />    <row date="2009-05-22" c1="$761,806" />    <row date="2009-05-29" c1="$771,441" />    <row date="2009-06-05" c1="$779,833" />    <row date="2009-06-12" c1="$787,525" />    <row date="2009-06-19" c1="$794,756" /></data>[/code]

 Any hints or suggestions?
Post Edited by jbuberel at 07/14/2009 04:03

 

[Giulio Toffoli]

there are several options.

Anyway here is what I did:

1. create a subdataset with two fields of type String: c1 and date.
2. the dataset run uses this expression as datasource expression:

new net.sf.jasperreports.engine.data.JRXmlDataSource(
    new java.io.File("C:\\Users\\gtoffoli\\Desktop\\value.xml"),
    "/data/row")

The first parameter is the file with the xml data, the second is the xpath expression to select the right nodes.

3. In the chart series definition we need to convert c1 in a number and date in a Date, here is how to do:
 

new java.text.SimpleDateFormat("yyyy-MM-dd").parse( $F{date})

new java.text.DecimalFormat("$#,##0").parse( $F{c1})

Attached the final report.

Giulio

[Giulio Toffoli]

I missed the part that the XML datasource must point to a remote file.... well, I'm sure you are able to complete that by yourself.

Suggestion:  JRXmlDataSource(java.lang.String uri, java.lang.String selectExpression)
 

Giulio

[jbuberel]

In fact, here is a complete HOWTO and tutorial on how to make this work, complete with parameterized remote URLs:

http://blog.altosresearch.com/use-xml-data-source-for-chart-generation-in-jasperreports/

-jason

[Giulio Toffoli]

Cool tutorial Jason!  Can I add it to the list of tutorials in the iReport wiki (http://jasperforge.org/plugins/mwiki/index.php/Ireport/Tutorials) ?

 

Giulio

[jbuberel]

Yes, go right ahead. That permalink should work.