conditional prompt with dynamic operator <=> between

[system]

Hi,

As per requirement I have to create a conditinola prompt for Start date where user can enter value for Start Date < = > and Between.

For example, If user wants to see all data for start date which is greater than ‘27-May-2007’ or start date = ‘27-May-2007’ or start date <‘27-May-2007’ or start date between ‘25-May-2007’ and ‘27-May-2007’ using prompt.

Please suggest how to achieve this.

Thanks in advance.

Spriha

---

Spriha (BOB member since 2008-05-28)

[system]

All those prompt conditions (equal to, less than, greater than) can only be given in separate queries.
You can however give a range of date prompts by saying date_obj between ‘From Dt’ and ‘To Dt’

.

---

haider (BOB member since 2005-07-18)

[system]

Haider,

Thanks for quick reply but at a time user want only one condition to be executed like start date > Input date.

If I used different queries then all conditions will be executed which I donot want.

Is it possible to display result of only one data provider out of multiple data providers?

Thanks

---

Spriha (BOB member since 2008-05-28)

[system]

Please tell clearly what is your requirement.
How you want to define prompts in a query

.

---

haider (BOB member since 2005-07-18)

[system]

Hi Haider,

Report will be running on following prompts -
1.Country
2.Code
3.Start date

Now requirement is User can enter start date for different conditions -
1.<
2.=
3.>
4.Between

For example,
If user want all start date<‘27-May-2008’ then report will be executed for
country = ‘United States’
code = 'US"
start date<‘27-may-2008’

or

user want all start date = ‘27-May-2008’ then report will be executed for
country = ‘United States’
code = 'US"
start date = ‘27-may-2008’

Basically start date prompt will be considered as report refresh time.
Here User has some flexibility to choose for which condition of start date he wants to referesh reports.

Hope this time I am clear with my requirement

Please advise …

Thanks

---

Spriha (BOB member since 2008-05-28)

[system]

Then you need to have two different queries and set the prompt as desired.
You cannot have them in one single query as one is less than prompt date and other equal to. You can get data for either of them in a query

.

---

haider (BOB member since 2005-07-18)

[system]

Hi, please review this topic for some ideas:

120×120 10.4 KB

Hi I have the requirements like this I have two date fields for example start date and end date what my users want is they want operator prompt so that it contains <,>,= values .And they will eneter the number of days For example if they select the start date and ‘>’ operator and end date and number of days as 10 the the data should be fetched like startdate-enddate >23 days.How can i include this operator prompt so that it should generate the sql like … select col1,col2 from table 1 wher…

---

Dave Rathbun (BOB member since 2002-06-06)

[system]

@Spriha

Now requirement is User can enter start date for different conditions -
1.<
2.=
3.>
4.Between

Hi Spriha,

I still believe that the above 4 scenarios can be satisfied using the condition of this type:

start_date between @prompt1 and @prompt2

.

1. You get the 1st case (start date <…) when @prompt1=January 1, 1900 (or some similar date in the past) and @prompt2 is the date users need

2. You get the 2nd case (start date =…) when @prompt1=@prompt2= date users need

3. You get the 3rd case (start date >…) when @prompt1= the date users need and @prompt2=December 31, 2100 (or some similar date in future)

4. You get the 4th case (start date between …) when @prompt1= the start date of the period users need and @prompt2=the end date of the period users need

It all will work when you train your users and explain them what to enter for @prompt1 and @prompt2 in which scenario.

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Hi Dave,

This code works absolutely fine for ‘<’,’=’ and ‘>’. But when I add ‘between’ as well with two prompt text then 3 prompt window opens

1.For enter operator.
2.To Date
3.From date

Now if user wants to select Effective date = prompt date then Ok button is not enabled.(As second prompt text is not selected)

Please advise how to fix this issue.I mean to say if User select ‘<’ then he can get only single prompt if user select between then he will have 2 prompt

Thanks
Spriha

---

Spriha (BOB member since 2008-05-28)

[system]

Prompts are all independent. It is not possible to have the values of one prompt based on the result of another prompt unless you use cascading prompts.

What I mean is, if the user selects “between” or something else, there is no way to enable / disable the next set of prompts.

---

Dave Rathbun (BOB member since 2002-06-06)

[system]

Hi Dave,

Can you please suggects how to achieve using cascading prompts? I created a cascade object and edited its LOV with case statement

case when obj=’<’ then date <@prompt()
when obj=’>’ then date>@prompt()
when obj = ‘between’ then date between@prompt() and @prompt()

When I try to refresh object its display 3 prompt window (same what I was getting last time).

Thanks

---

Spriha (BOB member since 2008-05-28)

[system]

I don’t believe cascading prompts are a solution to this issue. They are designed to be a sequence of prompts, but they are not going to allow you to suppress a prompt if a value is selected.

---

Dave Rathbun (BOB member since 2002-06-06)