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; } } }

Creating a parameter that takes multiple values

boulos

Hi All, 
 
 
could you please tell me how can i create a parameter in My SQL that allows to take multiple values when i Run the Report. My SQL looks like the following: 
 
Select a,b,c,d from table where e in (x,y,z,t,...etc) 
 
x,y,z,t.,..etc are defined at run time. 
 
in birt 3.7.2, when i use "Select a,b,c,d from table where e in ?" i get an error 
 
Regards 
Boulos

Find more posts tagged with

Comments

kclark

Could you tell me a little more about what your trying to do? 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT a, b, c, d
FROM TABLE
WHERE d = ?
</pre>
 
Works fine for me, are you trying to do something like 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT a, b, c, d
FROM TABLE
WHERE d = ? and c = ?
</pre>

Tubal

If you are talking about a multi select parameter, I normally just edit the script in the dataset's beforeOpen event. 
 
So in your dataset you'd do something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT a,b,c,d FROM table WHERE e IN (/**text I will replace**/)</pre>
 
Then in the beforeOpen script, you'd do something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = this.queryText.replace("/**text I will replace**/",params["myParam"].join(","))</pre>
 
This will convert the multiselect parameter to a comma delimited string. 
 
Now..... if you have text, SQL wants quotes around each string. So you'd have to do it a little differently. 
 
Something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = this.queryText.replace("/**text I will replace**/","'" + params["myParam"].join("','") + "'")</pre>

boulos

<blockquote class='ipsBlockquote' data-author="'kclark'" data-cid="111157" data-time="1351866479" data-date="02 November 2012 - 07:27 AM">
Could you tell me a little more about what your trying to do? 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT a, b, c, d
FROM TABLE
WHERE d = ?
</pre>
 
Works fine for me, are you trying to do something like 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT a, b, c, d
FROM TABLE
WHERE d = ? and c = ?
</pre></blockquote>
 
Hi 
 
I am trying something like <pre class='_prettyXprint _lang-auto _linenums:0'>select a,b,c,d from TABLE where e in(x,y,z)</pre>. 
your first SQL implies only one value for d (since there is an "=" after the d) while i want to set multiple values for d 
 
Regards 
Boulos

boulos

<blockquote class='ipsBlockquote' data-author="'Tubal'" data-cid="111159" data-time="1351866882" data-date="02 November 2012 - 07:34 AM">
If you are talking about a multi select parameter, I normally just edit the script in the dataset's beforeOpen event. 
 
So in your dataset you'd do something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT a,b,c,d FROM table WHERE e IN (/**text I will replace**/)</pre>
 
Then in the beforeOpen script, you'd do something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = this.queryText.replace("/**text I will replace**/",params["myParam"].join(","))</pre>
 
This will convert the multiselect parameter to a comma delimited string. 
 
Now..... if you have text, SQL wants quotes around each string. So you'd have to do it a little differently. 
 
Something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = this.queryText.replace("/**text I will replace**/","'" + params["myParam"].join("','") + "'")</pre></blockquote>
 
thanks, i thought that it will be easier than this! i was looking for a flag such as "allow multiple values or something". but are you sure this is the standard way to do it? how can i do it if i have more than one multiselect parameter: 
 
select a,b,c from TABLE where e in (<values read from report's param_1> and d in (<values read from report's parm_2>) and f in (<values read from report's param_3>)

Tubal

Same way: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT a,b,c FROM table WHERE d IN (/**d**/) and e IN (/**e**/) and f IN (/**f**/)</pre>
 
then in your script: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = this.queryText.replace("/**d**/",params["param1"].join(",")).replace("/**e**/",params["param2"].join(",")).replace("/**f**/",params["param3"].join(","))</pre>
 
The /**x**/ is just a place holder so you'll know what you need to replace. It's going to be replaced anyway. You could technically put whatever you wanted there, as long as you replaced it in your script. 
 
I normally do it as an SQL comment so in case i don't get a result, it doesn't blow up my query. 
 
this.queryText is the SQL string that your dataset is storing. 
 
The this.queryText.replace(x,y) is a basic javascript function that replaces x with y in this.queryText. So you're just replacing x with y in your sql script before it fetches it's data. 
 
params["param1"].join(x) is a basic javascript function that turns your parameter array into a string, separated by x. So if x = "," it's going to turn your array into a comma delimited string. 
 
You could also do: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT a,b,c FROM table /**params**/</pre>
 
and then 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = this.queryText.replace("/**params**/","WHERE a IN(" + params["param1"].join(",") + ") AND b IN(" + params["param2"].join(",") + ")")</pre>