BIRT report optional parameter script

I am using SpagoBI, and I am trying to create report with optional parameter. I have problem with beforeOpen() script. Here is the query.

select C."CUSTOMERNAME", C."CITY", D."YEAR", P."NAME"
from "CUSTOMER" C, "DAY" D, "PRODUCT" P, "TRANSACTIONS" T 
where C."CUSTOMERID" = T."CUSTOMERID"
and D."DAYID" = T."DAYID"
and P."PRODUCTID" = T."PRODUCTID"
and C."REGION" in (?)

and the script

if (params["cityparam"].value != null){ 
  this.queryText = this.queryText + "and C.\"CITY\" in ( ?,'" +params["cityparam"].value + "')"; 
}
else{
  var str = reportContext.getParameterValue("regionparam");
  q3 = this.queryText + "and C.\"CITY\" in  (?,( select \"CUSTOMER\".\"CITY\" from \"CUSTOMER\" where \"CUSTOMER\".\"REGION\" in  ('"+ str +"')))";
  this.queryText =q3; 
}

I have 2 parameters, regionparam and cityparam the second one is optional. I am trying to modify the query in this way, that when cityparam isn't set, I am comparing C."CITY" to all it's possible values in selected region. The generated query works in my PGadmin. But there are problems in SpagoBI studio. It's says:

Subquery returned more than 1 value. This is not permitted

Is there any BIRT master? I would be greatfull for help. Thanks.

Here is a technique I'm using for handling optional parameters for BIRT reports in SpagoBI. By rewriting the query, we can utilize a single query without modifying it based on parameter values.

Steps

1. Rewrite the query so that optional parameters may be null OR the database field is equal to some value. For every optional parameter, you'll have two '?' in the query. The first test is against null and the second test is a test for a value to match a field. For required parameters, you'll still only have one '?' in the query.

2. In the BIRT Data Set's parameters, for optional parameters, define two matching named parameters to correspond to the first and second '?' in the query for that parameter. Required parameters will only have 1 named parameter mapping to them.

Below are simplified samples from an existing report.

Example SQL query (SQLServer) with three optional parameters for user status, last login, and role

SELECT
  ar.role_name,
  au.user_id,
  au.Lname, 
  au.FName, 
  au.Email, 
  au.Last_Login,      
  au.status,
  au.Creation_Date
FROM account_user au                          WITH (NOLOCK)
INNER JOIN account_role ar                    WITH (NOLOCK)
 ON ar.account_id = au.account_id 
 AND ar.role_id = au.role_id
WHERE au.account_id = 9999
 AND ( (? IS NULL) OR (AU.status = ?) ) 
 AND ( (? IS NULL) OR (AU.last_login <= ?) ) 
 AND ( (? is null) OR (ar.role_id = ?))
ORDER  BY role_name, Lname, Fname

Here is what the BIRT Data Set's Parameters look like, for three optional parameters.