multi-select parameter in iReport 3.7

[klin0011]

Hi,

I am trying to run a report with a multi-select parameter in iReport 3.7 but cannot get it to run.

Parameter name is List of type java.util.Collection.

I have a column in my database called "id" of type int.

I passed the parameter value to the query like this:

SELECT
     id,
     name
FROM
     metadata
WHERE
    $X{IN, id, List}

I get error messages for executing SQL statment.

Any ideas on how to solve this?

[ckampshoff]

Hi,

Which error message do you get? And where did you get it?

Regards

Christina

[klin0011]

Hi Christina,

 

there is only a very big java stack trace saying the sql statement is wrong.

 

The statement

SELECT id,name FROM metadata WHERE $X{IN, id, List}

 

is expanded to:

SELECT id,name FROM metadata WHERE id IN([1,2])

 

And the stack trace in the sql classes say something like: "[" error in sql statement.

 

The Collection List is filled from another sql statement with id from type Integer. Therefore an "Input Datatype" was defined.

 

When printing the parameter in the main report we get something like [1,2,3,4].

 

When we change the sql query manually to

 

SELECT id,name FROM metadata WHERE id IN(1,2)

 

everything is working fine.

 

Regards

Ingrid

 

[ckampshoff]

Hi Ingrid,

that sounds like something is wrong with the filling of the parameter. A java.util.collection is always printed with the leading "[" and an ending "]" (if you print it via iReport). What about trying to cut off the [ ] before filling the parameter of the SQL-Query?

Regards

Christina.

[klin0011]

Hi Christina

if I use this query in iReport:

SELECT id, name FROM metadata WHERE $X{IN, id, List}

I get the following errors:
 

 

Code

 

Post Edited by klin0011 at 04/16/2010 13:47

[klin0011]

I tried is also with this query in iReport:

SELECT id, name FROM metadata WHERE id IN( $P!{Messpunkt})

and than I get this errors:

 

Code:

Regards

Ingrid

Post Edited by klin0011 at 04/16/2010 13:57

[ckampshoff]

Hi Ingrid,

I think it has something to do with the filling of your collection. It seems to me that there are no Integer values in the collection ("Operator existiert nicht: integer = character varying"). The same would cause the other error ("Syntaxfehler bei »[«"). How is the collection (you use in your query) filled? I never had such problems (errors) when I used a collection for a multi-select parameter.

Regards

Christina.

[klin0011]

Hi Christina,

I added the parameter "List" at the input data types at the server.
The properties for "List" are :

Name and Label: List
Input Control Details:
    -Type: Multi-select Query(Check Box)
    -Query Resource: Locally defined
        -The query (SQL): select id, name from metadata;
        -The locally defined datasource is my postgresql
    -Value Column: id
    -Visible query column: id

In the database "id" is defined as an Integer. I don't know if it's
possible to define somewhere the type of "id", so that the collection
can be filled with Integer-values.

Regards

Ingrid

[ckampshoff]

Hi,

I work on an oracle database with iReport 3.6.0 and jasperserver 3.5. I have multi-select querys for many reports and parameters on the jasperserver. I implemented the multi-selects with String and with BigDecimal and they work. I don't think that it won't work with Integer. I 'll do a test on it.

You can change the field types of the database fields in iReport. Go in the Report Inspector to Fields. There you can select the Field Class in the properties of the selected field. If you change something in the query and retrieve the new fields automatically from the db, it would revert your changes.

Regards

Christina.

[matusz84]

Hello,

I work on an mysql, this fix my problem:

id_pers in (select replace(replace("$P!{id_pers_list}","[",""),"]",""))

 

Regards

Mateusz

Post Edited by matusz84 at 08/20/2010 13:48