CODEWITHSUNDEEP
javascriptsqlreportbirtspagobi
LucasPG
LucasPG

Reputation: 433

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.

Upvotes: 0

Views: 3582

Answers (2)

dbh
dbh

Reputation: 1627

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.

enter image description here

Upvotes: 1

LucasPG
LucasPG

Reputation: 433

Once again I managed to solve my problem. :) First of all delete You'r optional parameter from data set. We' ll be setting it inside our beforeOpen() script. Here's my 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 script

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

As You can see the "?" is necessary only when parameter is declared in data set. Without it We can compare single row to more than 1 values returned from subquery.

Upvotes: 0

Related Questions