Multiple queries in a single jasper document

Question

Until now I have always been constrained by one thing with jasper-reports.
I can only write one data query in each document.
When I need to write another query I must create a subreport, pass its needed parameters and so on.

But I'm definitely not convinced that it's the good way to do it.

So is there another way to fire multiple data queries in a single jasper document?

Answer 1

It is possible to use execute multiple queries from a single report by using a subDataset and datasetRun. The behaviour is like having one or more subreports embedded into a single report file.

Define a subDataset like this:

<subDataset name="dataset1">
    <parameter name="someParam" class="java.lang.String"/>
    <queryString><![CDATA[SELECT column1, column2 FROM table1 WHERE column1=$P!{someParam}]]></queryString>
    <field name="column1" class="java.lang.String"/>
    <field name="column2" class="java.lang.String"/>
</subDataset>

Subdatasets can have parameters, fields, variables and groups just like a report can. Each subdataset can have it's own query, and the report can have as many subdatasets as you want.

To use a subdataset, you need to define a datasetRun. This can only be done inside particular elements: charts, crosstabs, tables and lists. We will use a list as it behaves almost exactly like another detail band.

This code defines a list that uses our subdataset:

<componentElement>
    <reportElement x="0" y="0" width="100" height="40"/>
    <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
        <datasetRun subDataset="dataset1">
            <datasetParameter name="someParam"><datasetParameterExpression><![CDATA["some value for column 1"]]></datasetParameterExpression></datasetParameter>
        </datasetRun>
        <jr:listContents height="40">
            <textField>
                <reportElement x="0" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression class="java.lang.String"><![CDATA[$F{column1}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="20" width="100" height="20"/>
                <textElement/>
                <textFieldExpression class="java.lang.String"><![CDATA[$F{column2}]]></textFieldExpression>
            </textField>
        </jr:listContents>
    </jr:list>
</componentElement>

Some notes:

• The jr:listContents element is analogous to a detail band element. You can place almost any other elements inside.

• The datasetRun element is much like a subreport element. It can have a dataSourceExpression or connectionExpression inside, which will change where the data comes from. If neither of these are present, the report datasource is used.

• The same subDataset can be used by many datasetRuns, so you can easily run a query multiple times with different parameters.