Report Designer Help

[rijwan]

Hi,
I am new in pentaho...
I developed one report which takes 2 parameter

1.Date
2.Gender

i have some case according to that i have to develop as many cases as many RPT file


date i want to pass null or some dates
gender i want to pass all ,male or female

in my table have only male and female in a gender column

in my sql i passed these two paremeter.


case 1:
date= 2009-12-31
gender=all
case 2:
date= 2009-12-31
gender=male
case 3:
date= 2009-12-31
gender=female
case 4:
date= null
gender=all
case 5:
date= null
gender=male
case 6:
date= null
gender=female

my single report is not fullfiling all these conditoin
because in

case 1:
case 4:
my table gender column having only male or female and i want both in such case my SQL is deferent
other cases
my SQL query is deferent

BUT I WANT SINGLE SQL FOR ALL CASES I WANT TO PUT IF-ELSE in my report
please help me how to resolve this

Thanks in Advance
Rijwan

[TomS]

Hi Rijwan,

could you please post your SQL query that far?

Best regards,

Tom

[rijwan]

Hi,
My SQL is
select * from account_statement where date = ${ userdate } and gender = ${ usergender}

here gender column having only male or female





Thanks
Rijwan

[TomS]

Hi,

I think, you just need some "OR"-Checks in your SQL-Statement...

Like this:

select *
from account_statement
where (
date = ${ userdate }
or '${ userdate }' is null
)
and (
gender = ${ usergender}
OR '${ usergender}' like 'all'
)

so both parameters can have a kind of "default/select all" value which is considered in the SQL...

HTH,

Tom