SpagoBI multi value parameter

Question

I'm trying to create a multi-value parameter in SpagoBI.

Here is my data set query whose last line appears to be causing an issue.

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 _CITY_

I created before open script in my dataset which looks like this:

this.queryText = this.queryText.replace(_CITY_, " CUSTOMER.CITY in ( "+params["cp"].value+" ) ");

My parameter is set as string, display type dynamic list box.

When I run the report I'm getting that error.

org.eclipse.birt.report.engine.api.EngineException: There are errors evaluating script "
this.queryText = this.queryText.replace(_CITY_, " CUSTOMER.CITY in ( "+params["cp"].value+" ) "); 
":
Fail to execute script in function __bm_beforeOpen(). Source:

Could anyone please help me?

Where did you run the report?? In spagobi server or studio? or..?? — sushi, Jul 28 '16 at 09:07
In studio. I solved this problem. I had to pass "?" as a first element of my list box string array, then the rest of the elements. I have never seen example like this. Now I have the problem with deploying my report to server. **The type of parameter "CITYPARAM" is expected as "Object[]", not "java.lang.String".** I guess the problem is that my parameter in studio is set as list box. I don't know how to do this with text box, because typical replacing in beforeOpen script isn't working. The "?" in my querry is necessary. I'm glad You are here marc_s — LucasPG, Jul 28 '16 at 11:33
yes you are correct. it's because of parameter type. change parameter type to String. in beforeOpen script, try `this.queryText = this.queryText+" and CUSTOMER.CITY in (" + params["cp"].toString().replace(";", ",") + ")"` this worked for me. — sushi, Jul 29 '16 at 08:38

LucasPG · Accepted Answer · 2016-08-02T17:48:07.710

2

Hello I managed to solve the problem. Here is my code:

var substring = "" ;
var strParamValsSelected=reportContext.getParameterValue("citytext");
substring += "?," + strParamValsSelected ;
this.queryText = this.queryText.replace("'xxx'",substring);

As You can see the "?" is necessary before my parameter. Maybe It will help somebody. Thank You so much for Your comments.

edited Aug 02 '16 at 17:48

answered Jul 29 '16 at 10:01

LucasPG

423
1
6
22

score 0 · Answer 2 · edited Jul 17 '19 at 13:00

If you are using SpagoBI server and High charts (JFreeChart Engine) / JSChat Engine you can just use ($P{param_url}) in query, or build dynamic query using Java script / groovy Script

so your query could also be:

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 CUSTOMER."CITY" in ('$P{param_url}')

SpagoBI multi value parameter

2 Answers2

Linked