BIRT report optional parameter script

Question

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.

score 1 · Accepted Answer · answered Aug 03 '16 at 20:12

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

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.
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.

Wow. So easy. Thank You for answer. – LucasPG Aug 04 '16 at 07:43 — LucasPG, Aug 04 '16 at 07:43

score 0 · Answer 2 · answered Aug 03 '16 at 08:48

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.

have you used drill -through feature in birt report in spagoBI.. I have created a birt report using drill- through in spagoBI studio, It is running fine there, while deploy it on server. It is showing the error -An error has occurred. Retry later. If the problems persists, contact the system administrator. The url which is shown on the hyperlink is "GET /SpagoBIBirtReportEngine/run?__report=%2FD%3A%2FAll-In-One-S‌pagoBI-5.1.0_2101201‌5%2Fbin%2FRevenueDet‌ails.rptdesign&idCon‌tratto=77&__overwrit‌e=true HTTP/1.1" 404 687.. Please help – — dhS, Jul 25 '17 at 09:04
Probably something wrong with hyperlink You have created. Not sure, too little informations. — LucasPG, Jul 25 '17 at 12:24
https://stackoverflow.com/questions/45298476/drill-through-not-working-in-spagobi-server-in-birt-report — dhS, Jul 25 '17 at 15:27

BIRT report optional parameter script

2 Answers2