Help with parameters for "dynamic" SQL query

[esskay]

Hello!

I just wonder the possibilites for using parameters in a query, my current SQL query is

select date_format( closed_on, '%Y' ) as period, count(case_id) as number

            from cases

            where closed_on >= P{from_date}

            and closed_on <= P{to_date}

            group by period

            order by period;

 

 

What I want to change is the possibility of making some kind of drop down or text field to change the "%Y" to anything of choice, for example "%Y-%m" or "%Y-%v".

Regards,
esskay.

[cmatthews]

 It is possible to do almost whatever you want and paramterize the SQL in any sort of advanced ways. I have some reports where I let the user choose the aggregate function type like SUM/AVG/COUNT/MIN/MAX on value results. You still pass the parameter through jasper to the SQL in the same way just set the parameter before report generation.

Something like this,

select date_format( closed_on, '$P!{myDateLogic}' ) as period, count(case_id) as number

            from cases

            where closed_on >= $P!{from_date}

            and closed_on <= $P!{to_date}

            group by period

            order by period;

 

Whenever you use paramters in the query they must have the "!", then they will be replaced by whatever the parameter contains prior to passing the query onto the data provider.

 

You really can get away with doing a lot of cool and powerful stuff this way.

 

Good luck,

 

Clark