List as subreport parameter

2019-06-07 03:00发布

问题:

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?

回答1:

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.