Conditional Filters using IN clauses

morganm00

First of all, I'm using BIRT 4.2.2.

I'm trying to find a way to have a conditional filter using in-clause style logic.

Here's the scenario:
A view returns a bunch of student records with department ID and affiliated department IDs. Normally, the data set returns all students that are members of a set of department ID's passed as a query parameter. However, if a checkbox is selected, then it needs to return all students with a department ID or an affiliated department ID from the set of selected department IDs.

So, the query is either:
SELECT * FROM STUDENTS WHERE DEPT_ID IN param["deptlist"]
or, if the checkbox is selected:
SELECT * FROM STUDENTS WHERE DEPT_ID IN param["deptlist"] OR AFFILIATED_DEPT_ID IN param["deptlist"]

I've been trying to play with filters and the expression editor for hours trying to figure out a way to implement that seemingly simple query, but I keep hit walls either trying to get the "OR" implemented since filters are ANDed or the "IN" implemented since that doesn't seem possible to test the expression editor.

Does anyone have any ideas?

Thanks in advance.
-mike

kclark

Are you getting errors when trying to use that query? With the classicmodels DB I was able to use this query.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
select distinct *
from customers
where customernumber in ( 103, 112 ) or customername in ( 'Mini Wheels Co.', 'Blauer See Auto, Co.' )</pre>
<br />
Which is close to what you're doing without the parameters. If you're parameters are only passing a single value then you could try something like this in your script.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>var paramValue = params["deptlist"];
this.queryText = "SELECT * FROM STUDENTS WHERE DEPT_ID IN (" + paramValue + ") OR AFFILIATED_DEPT_ID IN (" + paramValue + ")"</pre>