Search code examples
jasper-reports

Correct way to use a Jasper Reports table with parameters?

I'm currently trying to make a report that uses a table to allow easy sorting for the users. I've explored a lot of questions and documentation, but I am still struggling with the correct way to build the report in JasperSoft Studio. I got it working in one way, but it is very slow and I'm sure it's not the correct method to go about it. Here is more or less how I'm doing it at the moment:

<subDataset name="Dataset1" uuid="6926ab77-6601-4046-a16e-ff19290b3c00">
    <parameter name="param" class="java.lang.Integer"/>
    <queryString>
        <![CDATA[select 
                fields
                from table
                where column = ($P{param})]]>
    </queryString>
    <!-- fields -->
    <field name="field" class="java.lang.String"/>
</subDataset>
<parameter name="param" class="java.lang.Integer"/>
<queryString>
    <![CDATA[select 
            fields
            from table]]>
</queryString>
<summary>
    <band height="66">
        <componentElement>
            <jr:table 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" uuid="933a99f0-f34a-457e-9b90-f641f54fe213">
                    <datasetParameter name="param">
                        <datasetParameterExpression><![CDATA[$P{param}]]></datasetParameterExpression>
                    </datasetParameter>
                    <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                </datasetRun>
                <jr:column width="100" uuid="ad2b846b-732a-4b83-aec0-959f19ce2972">
                    <jr:columnHeader style="Table_CH" height="30">
                        <staticText>
                            <reportElement x="0" y="0" width="100" height="30" uuid="84616414-4739-471e-b853-7a537e72c959"/>
                            <text><![CDATA[columnHeader]]></text>
                        </staticText>
                    </jr:columnHeader>
                    <jr:detailCell style="Table_TD" height="30">
                        <textField>
                            <reportElement x="0" y="0" width="100" height="30" uuid="bafa83be-5223-4f44-88dd-249256c22a72"/>
                            <textFieldExpression><![CDATA[$F{field}]]></textFieldExpression>
                        </textField>
                    </jr:detailCell>
                </jr:column>
            </jr:table>
        </componentElement>
    </band>
</summary>

Is it really necessary to run the query twice? If I try to remove either query and work around it, the table never receives any data. This problem seems so simple, there must be some method to it.

Solution

  • I don't see an issue with the way you use parameters with the subDataset and query.

    Your problem is indeed the fact that you are running the SQL query twice. To prevent that you need to have your main dataset's query empty:

    <queryString>
<![CDATA[]]>
</queryString>

    and then from the properties of the Report set the following:

    When No Data Type: All Sections No Detail

    This will force the rendering of all your bands, except the detail one, as I'm assuming you don't have one.