crosstab with xml subdataset

[chriswesdorp]

Hi,

 

I am trying to create a crosstab table based on a XML datafile. However, for some reason when I follow the structure of the ShipmentsReport.jrxml I can not get the crosstab to work. I wondered if my crosstab definition was wrong so i create the crosstab in a summary band with the same XML. This looks like this.

 

The XML file

Code:

<tariffs>
  <tariff id="1">
    <name>some tariff</name>
    <currency>EUR</currency>
    <rows>
      <row>
        <amount>1</amount>
        <country>NL</country>
        <costs>1.00</costs>
      </row>
      <row>
        <amount>2</amount>
        <country>NL</country>
        <costs>1.30</costs>
      </row>
      <row>
        <amount>1</amount>
        <country>DE</country>
        <costs>1.10</costs>
      </row>
      <row>
        <amount>2</amount>
        <country>DE</country>
        <costs>1.45</costs>
      </row>
      <!-- many rows follow here -->
    </rows>
  </tariff>
</tariffs>

 

With the summary band this is the (particial) report definition:

Code:

[code] <!-- the query to get the costs --> <queryString language="xPath"><![CDATA[/tariffs/tariff[@id = 1]/rows/row]]></queryString> <field name="country" class="java.lang.String"> <fieldDescription>country</fieldDescription> </field> <field name="amount" class="java.lang.Integer"> <fieldDescription>amount</fieldDescription> </field> <field name="cost" class="java.lang.Float"> <fieldDescription>cost</fieldDescription> </field> <!-- the summary with crosstab --> <summary> <band height="100"> <crostab> <reportElement x="0" y="20" width="800" height="80"/> <rowGroup name="amount" width="50"> <bucket> <bucketExpression class="java.lang.Integer">$F{amount}</bucketExpression> </bucket> <!-- header definition --> </rowGroup> <columnGroup name="country" width="50"> <bucket> <bucketExpression class="java.lang.String">$F{country}</bucketExpression> </bucket> <!-- column header definition --> </columnGroup> <measure name="cost" class="java.lang.Float"> <measureExpression>$F{cost}</measureExpression> </measure> <!-- cell defition --> </crosstab> </band> </summary>

 

This piece of code works just fine. It nicely prints the the crosstab. However, I would like to print the additional information. Based on the shipment report I create a detail band, add the fields name and currency and create the crosstab with a subdata set. As datasource expression I use P{REPORT_DATA_SOURCE} to supply the XML to the subdata set.

 

This is the (partial code):

Code:

[code] <subDataset name="tariff_dataset"> <parameter name="tariffIdParam" class="java.lang.String"/> <queryString language="xPath">/tariffs/tariff[@id = $P{tariffIdParam}]/rows/row</queryString> <field name="country" class="java.lang.String"> <fieldDescription>country</fieldDescription> </field> <field name="amount" class="java.lang.Integer"> <fieldDescription>amount</fieldDescription> </field> <field name="cost" class="java.lang.Float"> <fieldDescription>cost</fieldDescription> </field> </subDataset> <queryString language="xPath"><![CDATA[/tariffs/tariff]]></queryString> <field name="tariff_id"> <fieldDescription>@id</fieldDescription> </field> <field name="tariff_name"> <fieldDescription>name</fieldDescription> </field> <detail> <band height="100"> <!-- the text fields --> <crosstab> <reportElement x="0" y="0" width="800" height="80"/> <crosstabParameter name="tariffIdParam"> <parameterValueExpression>$F{tariff_id}</parameterValueExpression> </crosstabParameter> <crosstabDataset> <dataset> <datasetRun subDataset="tariff_dataset"> <dataSourceExpression>$P{REPORT_DATA_SOURCE}</dataSourceExpression> </datasetRun> </dataset> </crosstabDataset> <! -- further report equals earlier example --> </crosstab> </band> </detail>

 

Now the report only prints the text fields but no crosstab. Is there a possibility to do this?

 

Chris

[chriswesdorp]

Sometimes you need to step away if thing aren't going as you planned. So I did. Starting again a little hour ago I got back to the subdataset and things became more clear. I had a look at the JasperReport source code.

 

I found that the subDataset is a new dataset instance and not relying on the report dataset (although it can use it). It is not really clear what the dataSourceExpression is but I had it replaced by just a datasetParameter providing the XML Document in the XML_DATA_DOCUMENT parameter (which was provided by the calling Java class).

 

Then with a parameter (als a datasetParameter) the subdataset does process the queryString and loops over the returned elements. For some reason when a dataset is provided with the datasourceExpression the queryString is not processed. Knowing this it resulted the following report parts:

 

Code:

	<subDataset name="tariff_dataset">
		<parameter name="tariff_id" class="java.lang.String"></parameter>
		<queryString language="xPath">/tarifss/tariff[@id = $P{tariff_id}]/ladder/row</queryString>
		<field name="fromZone" class="java.lang.String">
			<fieldDescription>fromzone</fieldDescription>
		</field>
		<field  name="amount" class="java.lang.Integer">
			<fieldDescription>amount</fieldDescription>
		</field>
		<field name="cost" class="java.lang.Float">
			<fieldDescription>cost</fieldDescription>
		</field>
	</subDataset>


<!-- partial crosstab -->
<crosstabDataset>
  <dataset>
    <datasetRun subDataset="tariff_dataset">
      <datasetParameter name="tariff_id">
        <datasetParameterExpression>$F{tariff_id}</datasetParameterExpression>
      </datasetParameter>
      <datasetParameter name="XML_DATA_DOCUMENT">
        <datasetParameterExpression>$P{XML_DATA_DOCUMENT}</datasetParameterExpression>
      </datasetParameter>

 

The subdataset now gets an XML Document, executes the XPath and the crosstab is printed.

 

Then I discovered (reading API docs and source code) it is also possible to create a Document from a node in the current Document. This is done also in the datasetParameter. As the expression is interperted as Java, inserting this code only provides the current node and you can skip the parameter as well.

 

Code:

[code] <datasetParameter name="XML_DATA_DOCUMENT"> <datasetParameterExpression>((net.sf.jasperreports.engine.data.JRXmlDataSource) $P{REPORT_DATA_SOURCE}).subDocument()</datasetParameterExpression> </datasetParameter>

 

Please don't forget to pass XML_NUMBER_PATTERN and alike parameters as well, otherwise JasperReports will use the defaults.

 

Hope this helps every one. Now you know how to use the same XML for both the master report and the crosstab.

[eorahil]

Thats THE thing. Thank you very much!