Date prompt for Report studio

[perspolis09]

Hi.

I have two time stamp fields like start date Oct 5, 2009 2:50:06 PM and end date Oct 5, 2009 4:58:06 PM. i need to create a prompt having the selection Date range; start date OR end date, start date mandatory, end date optional in prompt page?

please Any help to achieve this is appreciated

thanks

[perspolis09]

Hi.

I have two time stamp fields like start date Oct 5, 2009 2:50:06 PM and end date Oct 5, 2009 4:58:06 PM. i need to create a prompt for this two time stamp like Date range; start date OR end date, start date mandatory, end date optional in, how I can get this done because while not used between filter usually,  is this possible? we have oracle.


please Any help to achieve this is appreciated

thanks

[Lynn]

Re-posting the same question less than an hour after your first post strikes me as a  bit presumptuous.

What have you attempted and at what point are you getting stuck? I'm not sure I understand your requirement. What does "while not used between filter usually" mean? Some data examples might help.

[RKMI]

Hi,

You can just add ?pStartDate? in one filter make it required and another ?pEndDate? filter which you make option in the same query this should work like an "OR" logic when there is a value for End Date it will get picked up else ignored. Also in the prompt page make the start date prompt required and end date not.

Thanks,
RK

[perspolis09]

Re-posting the same question less than an hour after your first post strikes me as a  bit presumptuous.

What have you attempted and at what point are you getting stuck? I'm not sure I understand your requirement. What does "while not used between filter usually" mean? Some data examples might help.

this is my point.

The definition means, therefore, that the end_date is not required->
- The user can enter either a term between start_date and end_date or
- The user gives a very early date> = start_date. Showing all events, therefore, the first day forward

any wink help:) thanks!

[perspolis09]

Re-posting the same question less than an hour after your first post strikes me as a  bit presumptuous.

What have you attempted and at what point are you getting stuck? I'm not sure I understand your requirement. What does "while not used between filter usually" mean? Some data examples might help.

Lynn can you send me the examples of your filter? THANKS!

[globalbear]

One way might be to put a third prompt on the prompt page that let you decide on if you should use greater than or between.

Make the third prompt a value prompt with static choises. Let greater than (or what you prefer to call it) as default value.

Use two different report filters instead of one:

filter 1: date >= p_start_date
filter 2: if (prompt_3 = 'Between') then (date <=p_end_date) else (1=1)

filter 1 is always used but filter 2 is only used when choosing Between in the third prompt. If you choose greater than the second filter is not applied and all rows having a date >=start_date are shown in the report.

[perspolis09]

One way might be to put a third prompt on the prompt page that let you decide on if you should use greater than or between.

Make the third prompt a value prompt with static choises. Let greater than (or what you prefer to call it) as default value.

Use two different report filters instead of one:

filter 1: date >= p_start_date
filter 2: if (prompt_3 = 'Between') then (date <=p_end_date) else (1=1)

filter 1 is always used but filter 2 is only used when choosing Between in the third prompt. If you choose greater than the second filter is not applied and all rows having a date >=start_date are shown in the report.

could you please explain more what do mean by 3 filter?  in I do not really understand it, if give me someone an example (Figure)

THANKS!!

[globalbear]

Very quick sample, I hope you see how I mean. Got to for the day.

[perspolis09]

Very quick sample, I hope you see how I mean. Got to for the day.

unfortunately this not help with my problem :(

[Lynn]

unfortunately this not help with my problem :(

OK, this really drives me nuts. WHY doesn't globalbears' suggestion help you with your problem???? Can you elaborate even the teeniest tiniest bit?

What type of prompt control are you using? If you use a calendar prompt control then a third prompt is needed, as globalbear suggests, to indicate if the end date should be used or not in your filter. This is because a calendar prompt control will result in the current date as the default. You could set the default to some far future default date such as Dec 31, 2099. If the user didn't change it then this filter might work assuming you don't have terms ending beyond whatever far future date you pick:

Code Select Expand


[TermStart] between ?BeginDate? and ?EndDate?
and
[TermEnd] between ?BeginDate? and ?EndDate?


If you use a type in prompt you could tell if it is null, but then you'll need to cast a value they enter into a date datatype which introduces another set of problems if the user enters an invalid date or an improperly formatted string.

P.S. In my earlier post I was not offering to provide examples. I was suggesting that your requirement would be clearer if YOU provide some examples. If you provide a few sample scenarios of what your data looks like and then show what the result set ought to be for different prompt values then it is easier for people to visualize what you are trying to achieve.

[perspolis09]

OK, this really drives me nuts. WHY doesn't globalbears' suggestion help you with your problem???? Can you elaborate even the teeniest tiniest bit?

What type of prompt control are you using? If you use a calendar prompt control then a third prompt is needed, as globalbear suggests, to indicate if the end date should be used or not in your filter. This is because a calendar prompt control will result in the current date as the default. You could set the default to some far future default date such as Dec 31, 2099. If the user didn't change it then this filter might work assuming you don't have terms ending beyond whatever far future date you pick:

Code Select Expand


[TermStart] between ?BeginDate? and ?EndDate?
and
[TermEnd] between ?BeginDate? and ?EndDate?


If you use a type in prompt you could tell if it is null, but then you'll need to cast a value they enter into a date datatype which introduces another set of problems if the user enters an invalid date or an improperly formatted string.

P.S. In my earlier post I was not offering to provide examples. I was suggesting that your requirement would be clearer if YOU provide some examples. If you provide a few sample scenarios of what your data looks like and then show what the result set ought to be for different prompt values then it is easier for people to visualize what you are trying to achieve.

If you read from the beginning I am very particular about what I have explained, but also
thank you lynn and for your reply, actually I got it solved, and I hope that this solution will help others :)

Filter 1- [START_TIMESTAMP]>= ?par_start_date?

Filter 2- NVL(cast([END_TIMESTAMP],date),to_date('01.01.1900','DD.MM.YYYY')) <= NVL(cast(?par_end_date?,date)
,to_date('31.12.2999','DD.MM.YYYY'))


-perspolis