Using multi-select values in BIRT report

We are building our own UI, and taking the selected values in that UI and passing them as parameters to a BIRT report. We've done several this way, and the process works perfectly....EXCEPT, we need a listbox with multiselect capabilities to pass a list of selected values to a report for use in an IN clause. 
 
I've read about 50 different ways to "make this work" but so far, none of them seem to work at all. I found this post: <a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause/#description'>Using a Multivalue parameter in a in clause - Designs & Code - BIRT Exchange</a> and it seemed reasonably promising, but so far it does not appear to work. I have the following code in my BeforeOpen event: 
 
this.queryText = this.queryText.replace("4,8", params["reportSelectedJobIds"].value); 
 
I know there is text in my query to match the 4,8 part, and I've set the default value of the reportSelectedJobIds parameter to a much different set of values. The query works, but it returns values based on the original IN clause, not the substituted IN clause. 
 
What am I doing wrong? Is there a way to view the newly created SQL before it executes?? 
 
Thanks for any help anyone can provide....I cannot believe this is a feature that is THIS hard to implement. 
 
Paul Cooper 
Lead Developer, EMS Performance Improvement Center

Find more posts tagged with

Comments

jlspea01

Something that helps me in these cases is to look at the sql that is being generated in the logs. I do this by adding the following to the lines of my script:

importPackage(Packages.java.util.logging);
var fileHandler = new FileHandler("c:/tmp/birt.log", true);
var rootLogger = Logger.getLogger("");
rootLogger.addHandler(fileHandler);

function log(message) {
Logger.getAnonymousLogger().info(message);
}

and then, at various points of your query-rewrite, you can read the c:tmpbirt.log file and see what was getting rewritten.

A word of advice on rewriting sql, make sure that you use good parameter validation lest you accidentally wind up getting slammed by a sql injection attack. Here is an example of an onvalidate() for one of my report parameters that happens to be a comma seperated list of integers:

// , is the delimiter and space can be used to build a list
var myRegxp = /^[0-9, ]+$/;
myRegxp.test(params);

jlspea01

one more thing.... after you are happy with the query, you can get rid of the logging code... it just ads overhead and I have found that I can get a failure to lock on my log file using javascript logging.

If something really needs logging, you should think about doing java extensions and use log4j