pass parameters to subreports

[2001 JI Open Discussion]

By: Tzanko - tsanstef

pass parameters to subreports

2002-06-19 10:10

Hi Teodor,

 

I have some troubles with the subreprts. I am passing $P{..} from the master report via <subreportParameter name="oDate"><subreportParameterExpression>$P{..}</subreportParameterExpression>

 

What happens is that I got an error when executing the query. It happens that these parameters are either not passed corretly or they are not passed at all. Here is the erro:

Parameter index out of range.

at org.postgresql.jdbc2.PreparedStatement.set(PreparedStatement.java:65

)

at org.postgresql.jdbc2.PreparedStatement.setNull(PreparedStatement.jav

:157)

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter

java:244).............

NESTED BY dori.jasper.engine.JRException: Error executing report query :

 

SELECT t1."name" as payee, sum(t2."paidamount") as amount, t0."date" as date, t

."note" as note FROM "checks" t0, "supplier" t1, "checkdetails" t2 WHERE t0."da

e">? and t0."date"<='?' and t0."reconciliationid"='?' and t1."id"=t0."supplier"

and t2."checkid"=t0."checkid" group by t0."date", t1."name", t0."note", t0."

heckid"

 

Here is the master report xml:

<parameter class="java.lang.String" name="oDate"/>

<parameter class="java.lang.String" name="cDate"/>

<parameter class="java.lang.String" name="rID"/>

<!-- subreports-->

<parameter class="java.lang.String" name="ccReconcile"/>

 

<queryString><![CDATA[sELECT * FROM miscs]]></queryString>

 

 

<title>

...

</title>

<pageHeader>

..

</pageHeader>

<columnHeader>

<band height="10">

 

</band>

</columnHeader>

<detail>

<band height="400">

 

<staticText>

<reportElement height="20" width="555" x="0" y="11"/>

<textElement textAlignment="Left">

<font size="12"/>

</textElement>

<text>Cleared Transactions</text>

</staticText>

 

<!--

=========================sub report - cleared checks

-->

<subreport isUsingCache="true">

<reportElement x="5" y="96" width="200" height="20" backcolor="#DDFFFF"/>

<subreportParameter name="oDate">

<subreportParameterExpression>

$P{oDate}

</subreportParameterExpression>

</subreportParameter>

<subreportParameter name="cDate">

<subreportParameterExpression>

$P{cDate}

</subreportParameterExpression>

</subreportParameter>

<subreportParameter name="rID">

<subreportParameterExpression>

$P{rID}

</subreportParameterExpression>

</subreportParameter>

 

<connectionExpression>

$P{REPORT_CONNECTION}

</connectionExpression>

<subreportExpression class="java.lang.String">

$P{ccReconcile}

</subreportExpression>

</subreport>

<!--

=========================end of sub report

-->

 

 

</band>

</detail>

<columnFooter>

..

</columnFooter>

<pageFooter>

..

</pageFooter>

<summary>

..

</summary>

</jasperReport>

 

 

By: Teodor Danciu - teodord

RE: pass parameters to subreports

2002-06-20 00:07

 

Hi,

 

The problem is with you SQL query

which is incorrect.

 

Why do you put table and table column names

between quotes???

 

SELECT t1."name" as payee (incorrect)

SELECT t1.name as payee (correct)

 

FROM "checks" t0, "supplier" t1, "checkdetails" t2 (incorrect)

FROM checks t0, supplier t1, checkdetails t2 (correct)

 

Attention:

You cannot pass table or table column names

as query parameters!!!

See the JDBC documentation about how to use

PreparedStatement objects.

This is how JasperReports executes the query.

 

Why do you put query parameters between

apostrophe?

 

t0."date"<='?' and t0."reconciliationid"='?' (incorrect)

t0.date <= ? and t0.reconciliationid = ? (correct)

 

(Those question marks are placeholders for query

parameters. You should have some $P{…}

reference there, but the idea is the same)

 

Correct all that and everything should be OK.

 

Good luck!

Teodor