50

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?

Alex K
  • 22,315
  • 19
  • 108
  • 236
Manuel Leduc
  • 1,849
  • 3
  • 23
  • 39
  • How? I've look around for it but I really don't get it. – Manuel Leduc Sep 20 '11 at 09:19
  • 2
    I don't see how passing a query as a parameter solves the problem. You're still limited to one query per report. – GenericJon Sep 20 '11 at 10:05
  • 2
    @Alex K. Well if I've understood the question correctly, Manuel is asking how to put multiple queries into a single report. AFAIK this can only be done by using sub-datasets. – GenericJon Sep 20 '11 at 21:04
  • Thanks for your comments. Then, is there a methodology I can use to write properly a jasperrepport document without needing to have sub-sub-sub-report to achieve asked behaviour. – Manuel Leduc Sep 21 '11 at 07:14
  • I'm having this same issue, can you help me? https://stackoverflow.com/questions/49903520/using-componentelement-with-subdataset-in-jasperreports – user9333933 Apr 18 '18 at 17:10

2 Answers2

71

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.

GenericJon
  • 8,746
  • 4
  • 39
  • 50
  • 3
    "_The behaviour is like having one or more subreports embedded into a single report file._". Well, it's **almost** like having subreports. Because subreports can return values into the main report variables, and subdatasets can't. – Gustavo May 28 '15 at 14:54
  • 1
    @Gustavo: subdatasets (i.e. tables, crosstabs, lists, ...) **can** return values into the main report variables. There is a "Return Values" button in List/Table/Crosstab properties (Dataset tab). – GoranM Feb 05 '16 at 15:59
0

Its Better to have Sub report call or create a procedure to get final data with select.

Shreekrishna Shankhwar
  • 29
  • 5