$X function in JasperServer using list control

[jvway]

 Hi all,

I’m trying to implement a Radio button selection between two lists using iReport Pro 3.7.0 and JasperServer CE (Not really sure which version. In the 3’s, how do you tell?)

So, I’ve created the query below (In code windows). It works if I run in from MySQL QueryEditor with literals. It also works with the date parameters in JasperServer and iReport without the $X function. What I’ve done is create the parameter in iReport and tried it in multiple combinations but the documentation and forum posts focus on what I’ve done below.

I created the control in JasperServer as a Single-select List of Values (Radio). Then I entered the two lists in the interface. When run from JasperServer I get the errors below.

Question is am I using an improper control in JasperServer? It’s the one I want as there are only two choices at this point. Is there something wrong with the way I’m creating the function? I can run the In statement if it’s literal like: `opportunities`.`sales_stage` IN('Prospecting','M.A.N.','Proposal/Price Quote','Negotiation/Review','Solution ID/Won','Closed/Won')

Instance One:

iReport parameter = java.util.List

Jasper Server control = Type – Single-select List of Values (Radio)

List of values locally defined as a Label/Value pair in JasperServer.

 

Label	Value
Not Closed Lost	'Prospecting','M.A.N.','Proposal/Price Quote','Negotiation/Review','Solution ID/Won','Closed/Won'
Closed Lost	'Closed Lost'

net.sf.jasperreports.engine.JRRuntimeException: Invalid type + java.lang.String for parameter SelectType used in an IN clause; the value must be an array or a collection.

Instance Two:

iReport parameter = java.util.Collection

(Should this list be formatted differently for a java collection?)

com.jaspersoft.jasperserver.api.JSExceptionWrapper: Incompatible java.lang.String value assigned to parameter SelectType in the Client Revenue dataset.

Thanks for any help

Jerry

Code:

<![CDATA[sELECT `accounts`.`name` AS `Account Name`, `opportunities`.`name` AS `Last Oppt Name`, CONCAT( '$', FORMAT( SUM( `amount_usdollar` ), 2 ) ) AS `Total Revenue`, date_format( `date_closed`, '%Y-%m-%d' ) AS `Date Closed last Oppt`, COUNT( `opportunities`.`name` ) AS `Number of Oppts`, concat_ws( ' ', `contacts`.`first_name`, `contacts`.`last_name` ) AS `Contact Name` FROM `accounts_opportunities` AS `accounts_opportunities`, `accounts` AS `accounts`,`opportunities_contacts` AS `opportunities_contacts`, `opportunities` AS `opportunities`, `contacts` AS `contacts`WHERE `accounts_opportunities`.`account_id` = `accounts`.`id` AND `opportunities`.`id` = `accounts_opportunities`.`opportunity_id`AND `opportunities_contacts`.`opportunity_id` = `opportunities`.`id` AND `contacts`.`id` = `opportunities_contacts`.`contact_id`AND opportunities.`date_closed` >= date_format($P{StartDate}, '%Y-%m-%d') AND opportunities.`date_closed` <= date_format($P{EndDate}, '%Y-%m-%d')AND opportunities.deleted = 0 AND accounts_opportunities.`deleted` = 0 AND $X{IN, `opportunities`.`sales_stage`, SelectType} GROUP BY `accounts`.`name`ORDER BY SUM( `amount_usdollar` ) DESC]]>