nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Report Parameters in IN() clause

Priya R

Hi,
 
I'm using a "Text Box" for report parameter "PublicID" where user enter comma separated string values like 100005678,100006789,100007890, 100008765.
 
How do I add single quotes around each comma separated string like below. Tried using string.split(,) and stored in arraylist and adding quotes around them but getting "Cannot find default value for object" error.
 
I need to use "where public_id IN ( '100005678','100006789','100007890','100008765') " in my query.
Any help is much appreciated!!
 
Cheers,
Priya

Find more posts tagged with

Comments

Matthew L.

This can be accomplished by adding a script in the "beforeOpen" script of the data set.
 
Set your query as follows:
<pre class="_prettyXprint _lang-sql">
where public_id IN ( '%SeeBeforeOpenScript%' )
</pre>
Then use the following script in the data set's beforeOpen script:
<pre class="_prettyXprint _lang-js">
this.queryText = this.queryText.replaceAll('%SeeBeforeOpenScript%',params["MyTextBoxParameter"].value.join("','" )); //String, add single quotes to the join method
</pre>
For an example, see the following:
<a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/36152-filters-not-adequate-for-large-data-what-then/?p=133966'>http://developer.actuate.com/community/forum/index.php?/topic/36152-filters-not-adequate-for-large-data-what-then/?p=133966</a>

Priya R

Hi,
 
Thanks for your reply. Still it is not working for me.
The example link you provided contains sample report that I'm not able to view or download. I'm getting "Sorry you don't have permission" error.
 
Cheers,
Priya

Priya R

Hi Matthew,
 
Join() will work only for multiselect parameters rite? I'm using a normal textbox where user enter comma separated values.
How do I use the values in IN (' ', ' ') query?
 
Thanks,
Priya

Matthew L.

Priya,
 
You are correct that the Join() method previously posted is for a multi-select parameter.
Please try the following script in the Data Set's beforeOpen method to see if this corrects the behavior for you on a text box string:
<pre class="_prettyXprint _lang-js">
this.queryText = this.queryText.replaceAll('%SeeBeforeOpenScript%',params["MyTextBoxParameter"].value.replace(/\,/g, "'\,'"));
</pre>

Priya R

Hi Matthew,
 
No luck.. "Cannot find default value for object" is the exception I'm getting.
 
Thanks,
Priya

Matthew L.

Can you post a simplified version of your design that replicates the issue so that I can look it over?

Priya R

Hi Matthew,
 
Please find attached a sample report.
 
Thanks,
Priya

Matthew L.

Priya,
 
I believe the issue you were seeing had to do with one of the importPackage lines (they are not needed though).
Using this modified code, your example worked without an issue:
<pre class="_prettyXprint _lang-js">
//importPackage(Packages.java.io);
//importPackage(Packages.java.lang);
//importPackage(Packages.java.util);
//importPackage(Packages.java.text);

if((params['City'].value != null) && (params['City'].value != ""))
{
var paramCity = params['City'].value.replace(/\,/g, "'\,'");
//System.out.println(paramCity);
this.queryText = this.queryText + " WHERE CLASSICMODELS.CUSTOMERS.CITY IN ('" + paramCity + "') "
java.lang.System.out.println("Console Out: " + this.queryText);
}
</pre>

Priya R

Hi Matthew,
 
Perfect!!! Working Fine now.. I wonder why the importPackage is giving this exception in the latest version of BIRT.
I have used this kind of console output method for older versions of BIRT and I never faced this issue.
 
Anyway, Thank you very much Matthew!!! Much appreciated.
 
Cheers,
Priya