I need to pass to my subreport a list of integers which will be used as parameter for a IN statement in my query like this:
SELECT * FROM my_tables WHERE $X{IN, table.id || '', PARAMETER_LIST}
I have tried various syntax to instance a list on my master report but none of them lead to
a functional report ; eg :
<subreportParameter name="PARAMETER_LIST">
<subreportParameterExpression><![CDATA[[1,2]]]></subreportParameterExpression>
</subreportParameter>
Is there a solution to my problem?
I've tried to use $X{IN, attributeName, parameterName} with List component
.
The main steps (parameters names are from the sample below):
Declare listParam
parameter as java.util.List
in main report
Declare quantityParam
parameter as java.util.List
in subDataset
Make mapping from listParam
parameter to quantityParam
parameter with help of ArrayList
constructor
Add $X{}
expression to subDataset
query.
The working sample with language
property as Java
:
<jasperReport ...>
...
<subDataset name="dataset1">
<parameter name="ProductId" class="java.lang.Integer"/>
<parameter name="quantity" class="java.util.List"/>
<queryString>
<![CDATA[SELECT
POSITIONS."DOCUMENTID" AS POSITIONS_DOCUMENTID,
POSITIONS."POSITIONNO" AS POSITIONS_POSITIONNO,
POSITIONS."PRODUCTID" AS POSITIONS_PRODUCTID,
POSITIONS."QUANTITY" AS POSITIONS_QUANTITY,
POSITIONS."PRICE" AS POSITIONS_PRICE
FROM
"PUBLIC"."POSITIONS" POSITIONS WHERE PRODUCTID=$P{ProductId} AND $X{IN, QUANTITY, quantityParam}]]>
</queryString>
<field name="POSITIONS_DOCUMENTID" class="java.lang.Integer"/>
<field name="POSITIONS_POSITIONNO" class="java.lang.Integer"/>
<field name="POSITIONS_PRODUCTID" class="java.lang.Integer"/>
<field name="POSITIONS_QUANTITY" class="java.lang.Integer"/>
<field name="POSITIONS_PRICE" class="java.math.BigDecimal"/>
</subDataset>
<parameter name="listParam" class="java.util.List" isForPrompting="false">
<defaultValueExpression><![CDATA[Arrays.asList("1", "2")]]></defaultValueExpression>
</parameter>
<queryString>
<![CDATA[SELECT
PRODUCT."ID" AS PRODUCT_ID,
PRODUCT."NAME" AS PRODUCT_NAME,
PRODUCT."COST" AS PRODUCT_COST
FROM
"PUBLIC"."PRODUCT" PRODUCT]]>
</queryString>
<field name="PRODUCT_ID" class="java.lang.Integer"/>
<field name="PRODUCT_NAME" class="java.lang.String"/>
<field name="PRODUCT_COST" class="java.math.BigDecimal"/>
...
<detail>
...
<componentElement>
<reportElement x="200" y="2" width="313" height="29"/>
<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" printOrder="Vertical">
<datasetRun subDataset="dataset1">
<datasetParameter name="ProductId">
<datasetParameterExpression><![CDATA[$F{PRODUCT_ID}]]></datasetParameterExpression>
</datasetParameter>
<datasetParameter name="quantityParam">
<datasetParameterExpression><![CDATA[new ArrayList($P{listParam})]]></datasetParameterExpression>
</datasetParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
</datasetRun>
<jr:listContents height="29" width="313">
<textField>
<reportElement x="0" y="0" width="100" height="20"/>
<textElement/>
<textFieldExpression><![CDATA[$F{POSITIONS_QUANTITY}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="100" y="0" width="100" height="20"/>
<textElement/>
<textFieldExpression><![CDATA[$F{POSITIONS_PRICE}]]></textFieldExpression>
</textField>
</jr:listContents>
</jr:list>
</componentElement>
</band>
</detail>
...
</jasperReport>
The sample is also works with report's language
property as groovy
.
I think that this solution can be applied to subreport.