Input Parameters: Conditional SQL?

[fusion27]

Hi guys,

I'm building out a report and have a question about input parameters.  Based on the input of a boolean parameter, I'd like to make the SQL conditional.  Here's my sql.

When the Jasper Report is built, I prompt the user with 4 input parameters:

1. Start Time (dttm_begin)
2. End Time (dttm_end)
3. Job Position Number (pos_no)
4. yes/no Qualified candidates (flg_shortlist)

I understand how to make 1,2 and 3 work.  The problem I'm having is with #4. 

When the user indicates "true" for flg_shortlist I want #41 in my where clause to make the SQL submitted to the database read like this:

AND c.title = ''

When the user indicates "false" for flg_shortlist I want #41 in my where clause to make the SQL submitted to the database read like this:

AND c.title = 'Y'

Thanks for your help.

Regards,
Casey Wise

[mdahlman]

Casey,

You probably need SQL like this:

AND c.title = $P!{shortlist_sql}[/code]

Of course for that to work you need to add the new parameter shortlist_sql. Its default value should look like this:
$P{flg_shortlist}.equals("true") ? "'Y'" : "''"

Those single quotes inside double quotes are hard to read, but it should work. I normally put the entire clause into the extra param. I find it easier to read and maintain:
$P{flg_shortlist}.equals("true") ? "AND c.title = 'Y' " : "AND c.title = '' "

Regards,
Matt

[fusion27]

Matt, thanks a million for reading my post and taking the time to respond.  The University of Akron's HR dept thanks we too.  We all owe you a beer.   Worked like a charm!

Stacks of thanks,
Casey Wise