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

Multi-select parameter

Tom_A

I am trying to add a multi-select parameter to my data set. When I try to link the data set to the parameter, it isn't available in the Linked To Report Parameter dropdown box. This only happens when I attempt a multi-select. Is that working as intended? I am using version 2.3.0 of the RCP Designer.

Find more posts tagged with

Comments

pboos

Hello Tom,

You can accomplish this by assigning the multiselect parameter to a filter. Attached is a sample report. Let me know how this works for you.

Tom_A

Thank you, that works and is very helpful. Is it possible to filter on a nested query where the value I am filtering on isn't part of the final result set?

mwilliams

Tom,

You can also do the multi-select parameter by adding the where statement to your SQL query in your beforeOpen script using the IN clause. If you have lots of data, using the parameter in your query may be faster than using a filter. If you're not bringing in much data, a filter will most likely work just as good!

As for your next question, can you explain a little more in detail?

Tom_A

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="75102" data-time="1301327944" data-date="28 March 2011 - 08:59 AM">
Tom, 
 
You can also do the multi-select parameter by adding the where statement to your SQL query in your beforeOpen script using the IN clause. If you have lots of data, using the parameter in your query may be faster than using a filter. If you're not bringing in much data, a filter will most likely work just as good! 
 
As for your next question, can you explain a little more in detail? </blockquote>
 
I have a complicated query where I am selecting values from a subquery. I would like to add a multi-select parameter or filter to the subquery and to the overall/outer query (I don't know what the proper term would be). For simplicity, let's say my query looks like this: 
 
Select A, B, C from 
(select A, B, C, D, E 
from TABLE 
where F in ('?','?','?')) 
where C in ('?','?','?') 
 
Does BIRT only allow me to filter on the C but not on the F in the subquery? 
 
Also, where do I find the beforeOpen script? I don't know what that is...

mwilliams

Can you set up a simple example doing this type of query with the sample database? I was unsuccessful in trying. It may not be possible with the sample db. However, if this type of query works with your database, then F only needs to be a field in "TABLE" and it should work just fine.

As far as the beforeOpen method, if you select your dataSet in the data explorer and click on the "script" tab under the design window, you should see the scripting page and have the different methods available in a drop down.

Tom_A

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="75503" data-time="1302033129" data-date="05 April 2011 - 12:52 PM">
Can you set up a simple example doing this type of query with the sample database? I was unsuccessful in trying. It may not be possible with the sample db. However, if this type of query works with your database, then F only needs to be a field in "TABLE" and it should work just fine. 
 
As far as the beforeOpen method, if you select your dataSet in the data explorer and click on the "script" tab under the design window, you should see the scripting page and have the different methods available in a drop down. </blockquote>
 
Before I attempt the example, I would first like to understand the beforeOpen method because that might help solve my problem. 
 
Do I put the where clause in the actual data set query like I would for a single select parameter? Or how would I write that in the script window? How would it be done in the attached?

mwilliams

For a multi-select parameter, you can either put the where clause into your query in the dataSet editor with something like:

select blah
from blah
where blah IN ('****')

In your beforeOpen script, you'd then just do something like:

this.queryText = this.queryText.replace("****", params["multiSelect"].join("','")) to replace the dummy "****" with the actual list created by joining your parameter array.

Or, you could just do this in your query:

select blah
from blah

and in your beforeOpen, you'd do something like:

this.queryText = this.queryText + " where blah IN ('" + params["multiSelect"].join("','") + "')" to completely add the where clause.

Does this help?

Tom_A

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="75514" data-time="1302037171" data-date="05 April 2011 - 01:59 PM">
For a multi-select parameter, you can either put the where clause into your query in the dataSet editor with something like: 
 
select blah 
from blah 
where blah IN ('****') 
 
In your beforeOpen script, you'd then just do something like: 
 
this.queryText = this.queryText.replace("****", params["multiSelect"].join("','")) to replace the dummy "****" with the actual list created by joining your parameter array. 
 
Or, you could just do this in your query: 
 
select blah 
from blah 
 
and in your beforeOpen, you'd do something like: 
 
this.queryText = this.queryText + " where blah IN ('" + params["multiSelect"].join("','") + "')" to completely add the where clause. 
 
Does this help? </blockquote>
 
Woohoo, I got your second suggestion to work. Thank you. Now I will see about getting a multi-select on the sub-select and will post here if (i.e. when) I run into trouble.

Tom_A

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="75503" data-time="1302033129" data-date="05 April 2011 - 12:52 PM">
Can you set up a simple example doing this type of query with the sample database? I was unsuccessful in trying. It may not be possible with the sample db. However, if this type of query works with your database, then F only needs to be a field in "TABLE" and it should work just fine. 
 
As far as the beforeOpen method, if you select your dataSet in the data explorer and click on the "script" tab under the design window, you should see the scripting page and have the different methods available in a drop down. </blockquote>
 
I tried an to put together an example but it doesn't seem to work with the sample database. This was the idea though: 
 
select f_name, l_name, o_num 
from(select c.contactfirstname f_name, c.contactlastname l_name, o.ordernumber o_num 
from customers c 
inner join orders o 
on c.customernumber = o.customernumber 
where c.country = 'USA') 
where o_num > 10200 
 
I would like the multi-select parameter on the where c.country = 'USA' line. It doesn't make sense to write the query that way in the above context but it illustrates the idea.

Tom_A

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="75514" data-time="1302037171" data-date="05 April 2011 - 01:59 PM">
For a multi-select parameter, you can either put the where clause into your query in the dataSet editor with something like: 
 
select blah 
from blah 
where blah IN ('****') 
 
In your beforeOpen script, you'd then just do something like: 
 
this.queryText = this.queryText.replace("****", params["multiSelect"].join("','")) to replace the dummy "****" with the actual list created by joining your parameter array. 
 
Or, you could just do this in your query: 
 
select blah 
from blah 
 
and in your beforeOpen, you'd do something like: 
 
this.queryText = this.queryText + " where blah IN ('" + params["multiSelect"].join("','") + "')" to completely add the where clause. 
 
Does this help? </blockquote>
 
It took me a while to catch up but your replace solution was exactly what I needed. Thank you very much for the help!

mwilliams

Not a problem. Glad to help. Sorry for the delayed response, I've been caught up in other work!