How to pass a parameter of array/collection type at Jaspersoft Studio and use it at query?

Question

I'm trying to pass as a parameter, an array of values to get a report from my DW, using an "IN" clause.

The problem is that, when I set the parameter as "Array" or "Collection", it don't prompt on Jaspersoft Studio Preview screen, and I can't use it.

The first time, I've tried to pass the parameter as a String, where $P{convenio} was a string, passed to complete "IN":

//Select and joins here...
WHERE ("o"."status_operacao" IN ('EFETUADO', 'SUSPENSO'))
  AND ("c"."codigo" IN ( $P{convenio}))
  AND ("o"."codigo_produto" = 1)

It doesn't worked, returning an error "missing EOF at ')'", but there's no error of closure.

The last option was using $X{}, so I've set de $P{convenio}, as an Array/Collection type, and turned the query like this:

//Select and joins here...
WHERE ("o"."status_operacao" IN ('EFETUADO', 'SUSPENSO'))
  AND ($X{IN, "c"."codigo", convenio})
  AND ("o"."codigo_produto" = 1)

But now the param isn't prompting, and the report brings me everything.

Alex K · Accepted Answer · 2019-05-13T20:58:16.703

JasperReports engine supportes nested types - we can use typed collections.

Show collection at JSS - using dialog to set value

For example we can declare parameter of Collection type like this:

<parameter name="collectionParam" class="java.util.Collection" nestedType="java.lang.Long"/>

Small example of report with typed Collection:

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Blank_A4_2" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
    <parameter name="collectionParam" class="java.util.Collection" nestedType="java.lang.Long"/>
    <title>
        <band height="30" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="550" height="30"/>
                <textFieldExpression><![CDATA[$P{collectionParam} != null && !$P{collectionParam}.isEmpty() ? $P{collectionParam}.toArray()[0] : "empty"]]></textFieldExpression>
            </textField>
        </band>
    </title>
</jasperReport>

We can initialize collection at Jaspersoft Studio (JSS) like this:

1st step - calling parameter dialog:

2nd step - adding element to the Collection:

We can check (build) report via Preview action:

- as a result we are showing first element of Collection with help of expression:

<textFieldExpression><![CDATA[$P{collectionParam} != null && !$P{collectionParam}.isEmpty() ? $P{collectionParam}.toArray()[0] : "empty"]]></textFieldExpression>

Initializing parameter of Collection type using default value expression

We can initialize parameter of Collection type with help of defaultValueExpression and for example with java.util.Arrays.asList() method:

<parameter name="collectionParam" class="java.util.Collection">
    <defaultValueExpression><![CDATA[java.util.Arrays.asList("a", "b", "c")]]></defaultValueExpression>
</parameter>

Using at query

The parameter of Collection type can be used at query with help of $X{IN, <column_name>, <parameter_name>} expression.

Example:

<parameter name="param" class="java.util.Collection">
    <defaultValueExpression><![CDATA[java.util.Arrays.asList("val")]]></defaultValueExpression>
</parameter>
<queryString>
    <![CDATA[SELECT * FROM table_name c WHERE $X{IN, c.attr, param}
</queryString>

This expression will be transformed at runtime by JasperReports engine into query like this:

SELECT * FROM table_name c WHERE c.attr IN ('val')

We can also use parameter of String type at query in couple with IN operator using$P!{} expression.