Help with multi select query

[cdevana]

I've a simple report that queries an oracle database.

select col1 from tab where col2 in ($P{Organization})

$P{} values are supposed to be obtained via the multi-select query. The multi-select query values are being populated without any problems. But the issue seems to be with passing the selected values to the parameter. I tried setting Organization datatype to be java.util.Collection. It throws the following error:

com.jaspersoft.jasperserver.api.JSExceptionWrapper: Report design not valid : 1. Parameter type not supported in query : Organization class java.util.Collection

Replacing the datatype to java.util.List also results in similar error.

Replacing the datatype to java.util.String results in an incompatible type for multi value error

 

Changing the sql to:

select col1 from tab where col2 in ($P!{param})

results in "The report is empty."

What am I missing? Any help would be appreciated

[cdevana]

After figuring out how to use the jasperServer plugin for iReport, I modified the query to look like this:

select col1 from tab where $X{IN, col2, param1}

I set Parameter Class as   java.util.Collection for param1

The Default Value Expression is set to   new ArrayList(Arrays.asList(new String[] {"null"}))

I get a different error this time:

com.jaspersoft.jasperserver.api.JSExceptionWrapper: Errors were encountered when compiling report expressions class file: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed, calculator_ProtocolReport_1271197532653_672865: 139: No expression for the array constructor call at line: 139 column: 82. File: calculator_ProtocolReport_1271197532653_672865 @ line 139, column 82. 1 error

com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.JRException: Errors were encountered when compiling report expressions class file:org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed, calculator_ProtocolReport_1271197532653_672865: 139: No expression for the array constructor call at line: 139 column: 82. File: calculator_ProtocolReport_1271197532653_672865 @ line 139, column 82.1 error

[pioppogatto]

I have a similar problem and I didn't solve it.

Didi you look at the examples in JasperServer Repository? There's a report called "cascading multi select example report" that does what you want.

Looking at the parameter the default value expression is

new ArrayList(Arrays.asList(new String[] {"USA"}))

maybe can it be your "null" value that is not right? How can you pass to that null value the result of your multi select query?

Antonio

 

 

[cdevana]

I changed the "null" to a valid value and it still gives me the same error.

I set up the query and the parameter properties to be almost exactly the same as the Cascading multi select example from JasperServer repository.

[pioppogatto]

Hi,

I've tried to do my report as th JS example, I think it's like yours but I did not solved the problem

When I put instead of "null" a know value my report works good so it means that the problem is not there. I've understood that the defaullt value expression is the value that you want in your list box (by the way you can change from "USA" to "Mexico").

In my report if I put the null value as default I aspect that the parametrs passed in the where of the query will be the result of the query in the input control ... but it's not like that ... :(((

Let's start again:

1) is your report provided with a parameter with the same same of the input control

2) wich is the Class parameter? In my report and in the examples is java.util.Collection

3) is there a where clause in your query ? In my report the query is like that

 

SELECT ul.* from ul WHERE $X{IN, field_provincia, provincia_multi_select}

 

It appears that the result of the where clause is null and so the report it has no value ....

 

[cdevana]

The report query is:

select *  from fx_message  where $X{IN, msg_receiver_org, Organization}

The Organization parameter is set to Collections java class with default expression to be new ArrayList(Arrays.asList(new String[] {"eastman001"}))

The input control for Organization parameter is of the same name and the values are obtained by a query. The input control is locally defined and the query is

select distinct Organization
                    from ((select distinct SenderOrg as Organization from TXSummary)
                          union
                          (select distinct ReceiverOrg as Organization from TXSummary))
                    where (Organization is not null)
                    order by lower(Organization) asc

The report query runs against one database and the input control query runs against a different database.

The input control query gives me a list of organizations without any problem. Issue arises when I try to pass values I select from the list.

[cdevana]

Problem fixed!

The issue was that in iReport under the report properties, the language selection was set to Groovy. I changed it to java and the error disappeared. It shows me an empty report, but that's a different issue to tackle!

Post Edited by cdevana at 04/14/2010 14:43