Parameter in SQL Query - WHERE Clause

Hello,
 
I have the following problem:
 
I would like to use a report parameter in my SQL Statement for my DataSet. It is called "FILTER" and a boolean type. I have the following DB table:
 
 
ID Name Gender
11 Lisa female
12 Max male
13 Linda female
14 Mary female
 
When the FILTER parameter is given as true, only the female should be selected.
So:
 
Select * from table where Gender = 'female'.
 
When the FILTER paramter is given as false, the male should be selected. The query would be:
 
Select * from table where Gender = 'male'.
 
 
 
How can I do that in BIRT? With a single SQL Statement or with another tool? The additional misery is that I render the report into CSV and not PDF or something, so that ONLY the bindings get exported and not things that stand in texts or something. So I have to do that all in the declaring of the DataSet.
 
Thanks already for your help!

Find more posts tagged with

Comments

mwilliams

In your data set, put the simple query:
 
<pre class="_prettyXprint _linenums:0">select * from table</pre>
 
In your data set's beforeOpen script (select data set in Data Explorer and then select script tab below design window) put something like this:
 
<pre class="_prettyXprint _linenums:0">if(params["myparam"]){
this.queryText = this.queryText + " where Gender = 'female'";
}
else{
this.queryText = this.queryText + " where Gender = 'male'";
}
</pre>

Miss_Anonymus

Thank you very much !! Works fine.

Kratika

Hi Jason,
 
i have 2 parameters, param 1 and Param 2 are drop downs. Param 2 depends on Param 1 selection. User may or may not select Param 2. How do i write dynamic query based on the param2 selection.