cancel
Showing results for 
Search instead for 
Did you mean: 

Limit a range of date in a prompt filter

Former Member
0 Kudos

Hi,

I'm trying to built a prompt filter which permit my users to prompt a range of date (ex starting date - ending date).

But I would like to limit the range to a maximum of 50 days (from the starting date to the ending date)

Is it possibile to do it (in WI or BO 6.5 Designer) and how?

Thank you in advance

Riccardo

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi John,

Thanks. I tried to follow your approach but I haven't solved my problem yet. Apparently I have no differences:

I mean The LOV of end date prompt still contains ALL the dates not only the 50 days after the start date (that is my aim).

I think I should process the 1 prompt (START DATE) before and the 2 prompt (END DATE) after, with the condition, in the second prompt, that only the first 50 days after the START DATE are eligeables in the selection. What do you think?

Cheers

Riccardo

Former Member
0 Kudos

Hi Riccardo,

You can create an object with the definition similar to the below one (In Oracle)..

Select DATE From TABLE,
Where DATE < Substr (SYSDATE-1, 1, 10) and rownum <= 50

Then you can use this object to display the LOVs in the Date Prompt.

Hope this helps you

Rehards,

Rohit

Former Member
0 Kudos

Hi Rohit,

my DB is SQL SERVER I think there should be some differences according to your code (Oracle) anyway thanks, I'm going to try!Why do you think I need the SYSDATE, can you explain me how does your code work in this situation?

Thank you

Riccardo

Former Member
0 Kudos

Hi,

I have just used SYSDATE to limit the max date to the current date.

You can use your max date (ENDDATE) in place of SYSDATE.

This is just the logic to restrict the no. of rows to 50 at the DB level. So you will get only 50 dates from the DB.

I have already used it in Oracle in one of my reports.

I doesn't know anything about SQL server. I just thought if the logic can help you .

Regards,

Rohit

Former Member
0 Kudos

Hi Riccardo,

There is an easy way to do that in universes.

You have to create an firstobject based on your date:

mytable.mydate

Then create a secondobject with the same definition and a where clause like this:

mytable.mydate BETWEEN  @Prompt('1-Start Date','D','MyClass\MyObject',mono,constrained) AND   @Prompt('2-End Date','D','MyClass\MyObject',mono,constrained)

The List of Values used in the prompt is based on the first objects you created.

Last, edit the list of values of the second object and in the query panel editor, click on the "Options" button and change the number of rows option to limit the returned result.

In attachment, you will find a universe based on Club database.

See the object "Invoice Date (50 rows max)" in "Sales" class that illustrates the purpose.

Regards,

Didier

Answers (1)

Answers (1)

Former Member
0 Kudos

Riccardo,

This can be done at the Universe level. You don't mention the database platform so I'll give you the idea using SQL Server syntax and if you're on another platform you can change accordingly.

Based on the existing objects you can create another iteration of them but add to the filter (where) the 50-day limitation. So suppose you have two objects, Begin_Start_Date, and Ending_Date, create another Begin_Start_Date_50_Dys and another Ending_Date_50_Dys. For the Begin_Start_Date_50_Dys, add a where that states "between Begin_Start_Date_50_Dys - (getdate() - 50) and Begin_Start_Date_50_Dys" and for Ending_Date_50_Dys "between Ending_Date_50_Dys and Ending_Date_50_Dys + (getdate() + 50)". Now you can use your two new objects as filters.

Thanks,

John