Passing SQL "In" Parameter List in Jasperreport

passing SQL IN parameter list in jasperreport

Jasper Report has a special variable $X for that:

select * from customer where $X{IN,customer_role,roles}

should work. See here and here.

How to correctly pass a SQL query as parameter in Jasper Reports?

In case using parameters at defaultValueExpression you should operate with them as a common Java variable.

For example:

<parameter name="fieldFilter" class="java.lang.String">
    <defaultValueExpression><![CDATA["someValue"]]></defaultValueExpression>
</parameter>
<parameter name="query" class="java.lang.String">
    <defaultValueExpression><![CDATA["SELECT * FROM some_table WHERE some_field = '" + $P{fieldFilter} + "'"]]></defaultValueExpression>
</parameter>
<queryString>
    <![CDATA[$P!{query}]]>
</queryString>

or like this:

<parameter name="intFieldFilter" class="java.lang.Integer">
    <defaultValueExpression><![CDATA[2017]]></defaultValueExpression>
</parameter>
<parameter name="strFieldFilter" class="java.lang.String">
    <defaultValueExpression><![CDATA["someValue"]]></defaultValueExpression>
</parameter>
<parameter name="query" class="java.lang.String">
    <defaultValueExpression><![CDATA[String.format("SELECT * FROM some_table WHERE some_field = '%1$s' AND trunc(date_field, 'yyyy') = trunc(to_date(%2$d, 'yyyy'), 'yyyy')", $P{strFieldFilter}, $P{intFieldFilter})]]></defaultValueExpression>
</parameter>
<queryString>
    <![CDATA[$P!{query}]]>
</queryString>

Jaspersoft/ Jasperreports: How to pass parameter list to IN clause?

I've found that this can be achieved with:

SELECT COLUMN1
FROM TABLE
WHERE $X{IN, COLUMN1, parameterList}

Then parameter list has to be a Java Collection and defined as follows:

Jaspersoft list parameter screenshot

Reference: https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/using-parameters-queries

jasper report passing parameter from list index

I was able to crack this down by adding two additional parameters age1 & age2 and passing the value to the added parameters from the list here are the details.

<parameter name="trafficPeriod" class="java.util.Collection"/>
    <parameter name="transactionType" class="java.util.Collection"/>
    <parameter name="transactionStatus" class="java.util.Collection"/>
    <parameter name="roamingPartners" class="java.util.Collection"/>
    <parameter name="age" class="java.util.List" isForPrompting="false"/>
    <parameter name="age1" class="java.lang.Integer" isForPrompting="false">
        <defaultValueExpression><![CDATA[$P{age}.get(0)]]></defaultValueExpression>
    </parameter>
    <parameter name="age2" class="java.lang.Integer" isForPrompting="false">
        <defaultValueExpression><![CDATA[$P{age}.get(1)]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT
  CLIENT_CODE,
  PARTNER_ORG,
    PARTNER_CODE,
  SERVICE_TYPE,
  TP_ID,
  TRAFFIC_PERIOD,
  SETTLEMENT_TYPE,
  SDR_NET,
  TRAN_CURR_NET,
  TRAN_CURRENCY_ID,
  DUE_DATE,
  TRANSACTION_TYPE,
  STATUS,
  NOTE_ID,
  AGE
FROM vw_transaction_search
WHERE $X{IN, client_service_id, clientServiceIds}
AND $X{IN, PARTNER_SERVICE_ID, roamingPartners}
AND $X{IN, traffic_period, trafficPeriod}
AND $X{IN, transaction_type, transactionType}
AND $X{IN, status_id, transactionStatus}
AND AGE BETWEEN $P{age1} AND $P{age2}
ORDER BY client_code,
  partner_code,
  traffic_period,
  age]]>
    </queryString>

How to pass parameters to query?

There are two syntax expressions for parameter referencing in JasperReports: $P{} and $P!{}.

• $P{paramName} syntax is using mostly for setting WHERE input parameters values. The replacement algorithm is "smart", its implementation uses java.sql.PreparedStatement: for java.lang.String parameter the engine will replace $P{parameterName} with quoted value, for java.lang.Integer - with numeric value and so on.

The sample:

| Parameter name |  Parameter type   | Parameter value |
|:---------------|-------------------|:---------------:|
|   eventName    | java.lang.String  |  Olympic Games  |
|   eventType    | java.lang.Integer |       2         |

Original expression (to replace):

SELECT startDate, endDate, rating FROM events WHERE name=$P{eventName} AND type=$P{eventType} 

The result will be:

SELECT startDate, endDate, rating FROM events WHERE name='Olympic Games' AND type=2 

• $P!{paramName} syntax is using mostly for doing the "simple" replace.

The sample:

| Parameter name |  Parameter type   | Parameter value |
|:---------------|------------------:|:---------------:|
|   tableName    | java.lang.String  |     events      |
|   eventName    | java.lang.String  |  Olympic Games  |
|   channel      | java.lang.String  |     'BBC'       |
|   type         | java.lang.String  |     sport       |

Original expression (to replace):

SELECT startDate, endDate, rating FROM $P!{tableName} WHERE name='$P!{eventName}' AND channel=$P!{channel} AND type=$P!{type} 

The result will be:

SELECT startDate, endDate, rating FROM events WHERE name='Olympic Games' AND channel='BBC' AND type=sport

---

For more information you can read this Using report parameters post and look at this Query sample.

---

In your case the right expression may be like this:

SELECT name, phone, email FROM company WHERE $P!{clause} = $P{key} ORDER BY $P!{order}

where $P{key} is a java.lang.String parameter

or like this (it depends on $P!{clause} value)

SELECT name, phone, email FROM company WHERE $P!{clause} = $P!{key} ORDER BY $P!{order}

where $P{key} is a java.lang.String parameter

Passing an entire sql query as a parameter in jasper reports

I figured out the answer with some help from my colleague. I had to change the parameter ($P{QUERY}) into ($P!{QUERY}) in the jasper report's xml file (ex; report.jrxml)

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

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.

Example:

<parameter name="param" class="java.lang.String">
    <defaultValueExpression><![CDATA["'val1', 'val2'"]]></defaultValueExpression>
</parameter>
<queryString>
    <![CDATA[SELECT * FROM table_name c WHERE c.attr2 IN ($P!{param})
</queryString>

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

SELECT * FROM table_name c WHERE c.attr2 IN ('val1', 'val2')

Passing parameters to `In` clause

I think the right solution will be:

SELECT * FROM `medication` WHERE $X{IN,idMeidication,parameter_name}

without $P!{}

In this way i use it in my reports.

Jasperreport parameter selection from SQL query

Here is the documentation for adding input controls to reports in JasperReports Server:

http://community.jaspersoft.com/documentation/jasperreports-server-user-guide/adding-input-controls

Scroll down to the section that deals with query-based input controls. Your input control will have a name, such as deliverer. In your report, you must add deliverer as a parameter and reference it in your query, as you have done in your comment.

---

Related Topics