Problem with Allow Multiple Values for Parameter

I cannot get my query to work when I enable a parameter to allow selection of more than one value from a List Box. However, if I uncheck that option (ie the Allow Multiple Values check box in the Edit Parameter screen), my query works fine and delivers the data as I would expect.

Here's the part of the query that references the parameter:

substr(logging_entity,5,3) in (?)

Any thoughts??

Perhaps if I knew how BIRT was rendering my parameter before executing the query, I might be able to figure it out.

Ty

Find more posts tagged with

Comments

cypherdj

Ok, I actually posted the code to do this in several threads, but here you go: 
 
Basically remove the IN clause from your query, and remove the parameter. Then select your data set and select the script tab rather than the layout tab. It should default to the beforeOpen method. Replace myParam to the correct name of your parameter, replace myColumn with the actual column you want to check, substr(....). 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
myParamValues = params["myParam"].value;

if (myParamValues!=null && myParamValues!="") {
this.queryText = this.queryText
+ " AND myColumn IN (" + concatMultiSelectValues(myParamValues) + ")";
}

function concatMultiSelectValues(valuesArray) {
var concatValues = "";

for (idx=0; idx < valuesArray.length; idx++) {
// integer version
//concatValues = concatValues.concat(valuesArray[idx] + ", ");
// string version
concatValues = concatValues.concat("'" + valuesArray[idx] + "', ");
}

// remove last ", "
concatValues = concatValues.substr(0, concatValues.length - 2);

return concatValues;
}
</pre>
 
Hope that helps, 
Cedric

TyGR

Thanks Cedric.

I entered it in the "beforeOpen" script tab for my dataset and replaced the parameter and field name accordingly, but I'm getting some exceptions now:

ReportDesign (id = 1):
- Unhandled exception when executing script.

Error.UnhandledScriptError ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Unhandled exception when executing script.
at org.eclipse.birt.report.engine.script.internal.ScriptExecutor.addException(ScriptExecutor.java:199)

Chart NewChart:
- An exception occurred during processing. Please see the following message for details:
Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: ORA-00933: SQL command not properly ended

able (id = 330):
- Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: ORA-00933: SQL command not properly ended

odaconsumer.CannotGetResultSetMetaData ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: ORA-00933: SQL command not properly ended
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1107)

cypherdj

Can you post the main query along with the code you inserted in the beforeOpen method? 
 
What you could also do is add some logging, as follows: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
importPackage(Packages.java.io);
logFileName = "c:/tmp/birt.log";

out = new PrintWriter(new FileWriter(logFileName, true));

myParamValue = params["myParam"].value;
out.println("myParam=<" + myParamvalue + ">");

// modify queryText to add filtering based on myParam parameter

out.println("this.queryText=<" + this.queryText + ">");
out.close();

</pre>
 
This will output the query to c:/tmp/birt.log (or wherever you've specified your log to be. 
Then take SQL code and run it in your database to see what's wrong with the code. 
 
Hope that helps, 
Cedric