Multiple options for date input controls: 1. Default 2. User defined

[tugrul083]

Hi All,

I'm using a report with start and enddate parameters. I want the users to have the options of;

1. Using default start and enddates defined in the parameters OR in the SQL (ie. startdate: sunday before. enddate: last sunday)

2. User defined start and enddates.

Implementing either of these alone is dead easy. However I can't get my head around finding a way to give the user these 2 options. Ideally user would select from a menu like below:

A. Use default dates

B. Enter your start and enddates

I am badly stuck and this is pretty urgent work. Any help / suggestion / idea would be greatly appreciated. 

Many thanks,

Tugrul

 

[ajinkya_c]

Hi,

This can be possible with optional prompt.

For example if my database is oracle then i create my optional propmt(input controls) like if i provide startdate and enddate then it takes these value to the where conditiion, if user not provide values, that is startdate enddate null then my where condition is like Startdate=(next_day(sysdate,'SUN')-7) and Enddate=(next_day(sysdate,'SUN')). similarly use date function of respective database to findout sunday dates. 

You can manage these two where condition based upon starttdate and enddate input control null or not null. Use P!{} parameter for optional where condition.

Hope this may help.

Thanks,

Ajinkya

 

[tugrul083]

Thanks for the reply Ajinkya.

 

I get the idea. But I need more info on the usage of P!{} parameter.

 

I'll have P{startdate} and P{enddate} parameters. When these are left NULL, the SQL will be something like:

 

select FORCE INDEX (FromIndex)

WHERE

CASE WHEN $P{startdate} IS NULL

THEN

(Fromx> "XX-YY-ZZ" )

AND Fromx < "AS-DF-FG"

ORDER BY FromX) as a1,

ELSE

(Fromx>"$P{startdate}")

AND

(Fromx<"$P{enddate}"

ORDER BY FromX) as a2,

END

Problem is that I think I need a third parameter that will give the user the option to select a date or leave it empty. I can't see how to achieve this...