on 09-04-2009 1:00 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.