passing SQL "IN" parameter list in jasperreport

sql jasper-reports
Priyanka picture Priyanka · Jun 3, 2011 · Viewed 23k times · Source

I am working on Jasper Reports and my query uses SQL 'IN' predicate.

SELECT customer_name AS NAME, 
       id_customer   AS ID 
  FROM customer 
 WHERE customer_role IN ($P{roles})

Here the role parameter can have 1 or more integer values and will be dynamically decided when generating the jasper report.

Can anybody please help me on how to set the value of 'roles' parameter thru Java program dynamically.

Answer

Aaron Digulla picture Aaron Digulla · Jun 3, 2011

Jasper Report has a special variable $X for that:

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

should work. See here and here.

Related questions

How to pass parameters to query?

I'm trying to write a query for my application, but I'm having some troubles. I need to pass as a parameter one field of my database, like: SELECT name, phone, email FROM company WHERE $P{clause} = $P{key} ORDER BY $…

Read More
How to join records from multiple object tables to a master table with a single query?

So I have a data model which is set up with a table that contains NAME, ID, and CONDITION columns for a series of objects (each object has a unique id number). The rest of the attributes for these objects …

Read More
optional where clause jasper reports

I am trying this query but without success. SELECT name, phone_office, billing_address_city, billing_address_street, billing_address_country FROM accounts WHERE ($P!{EmployeeID} is null or assigned_user_id = $P!{EmployeeID}) ORDER BY billing_address_country, billing_…

Read More