Dynamic SQL Query

[sumit_1]

Hello ,

I am new to Japer and using ireport designer. I want to design the report to display details in  bar chart.

Report will have input as duration , "Today","Month to date", "Last Month", "Last 6 month" , "Date Range" (this will prompt for start date and End date")

Depending on the input , the sql query will be different  for the date range and thus query for each of the above mentioned input will be different.

Please suggests us on how to proceed on this.

Thanks

Sumit

 

 

[spierepf]

Greetings,

We've done this using a single select input control where different numbers represent different time periods:

0 -> arbitrarty time interval defined by frm_date_time to to_date_time

1 -> Yesterday

2 -> Today

3 -> Month to date.

We then use a BETWEEN condition where the end points are CASE-WHEN-ENDs:

report_sales_detail.tran_date BETWEEN (CASE $P{Date_Range_List} WHEN 0 THEN $P{frm_date_time} WHEN 1 THEN CURDATE() WHEN 2 THEN (CURDATE() - INTERVAL 1 DAY) WHEN 3 THEN (CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY) END)) AND (CASE $P{Date_Range_List} WHEN 0 THEN $P{to_date_time} WHEN 1 THEN (CURDATE() + INTERVAL 1 DAY) WHEN 2 THEN CURDATE() WHEN 3 THEN (CURDATE() + INTERVAL 1 DAY) END))

Note that I may have the parentheses wrong in the above. I cut it out of one of our existing reports.

There is no reason you couldn't extend this to include last-month and last-6-months...

Let me know if you need further explanation.

Peter.