Universe level Prompts

[Former Member]

Hi,

I was created Universe level Prompts like below.

Example: Date Between @Prompt(Sysdate) and @Prompt(Sysdate-60)

I was created one report by using Date object which is having prompts and in the report properties i have checked REFRESH ON OPEN.

when i tried to open report, report is refreshing fine and giving data correctly when i click on refresh in prompt window by selecting the values.

But i want the report to be opened and refreshed without asking prompt window to select the values. Because always iam selecting Current date (28-06-2010) and Current Date-60 (EX: 28-04-2010) values.

Please help me out.

Thanks in Advance

SureshBabu

[Former Member]

If you always want to use Sysdate and Sysdate-60 (which, by the way, look like they are in the wrong order) then don't use the @Prompt() syntax. You only use that when you do actually want to prompt. The required syntax would simply be:

table.your_date between sysdate-60 and sysdate

Note that for many cases you will want to truncate the date first

table.your_date between trunc(sysdate-60) and trunc(sysdate)

You can also make objects called "Today" and "60 Days Ago" in your universe. These objects will allow users to create their own date gate conditions using any available date object.

[Former Member]

Hi Suresh,

You could have the below condition in one of your objects and use the same in the report, i dont think prompt is required to achieve this as you need data for last 60 days only.

where clause :

<datefield> between (sysdate-60) and sysdate

If the user need the date to be prompted for the selection then modify the above condition as below,

<datefield> between (@prompt(user input value)-60) and @prompt(user input value))

I hope this will help.

Thanks and regards,

Ravichandra K

[Former Member]

Although there is always more than one way to skin a cat , I do not recommend to allow user to input the value. There will be format error or typo error that keep the report from returning data, or worse, return an error message.

In the software development world, you should always have code to handle all possible situation. This is true in the report development world as well. You want to control every situation as much as possible. To avoid user error, the date prompt should always be an LOV (list of value). You can create LOV for daily dates, or weekly dates, or monthly dates, and so on.

If your prompt should always start with today's date as the report ending date, you can still create an LOV that defaults to today's date. But this gives the user the flexibility to choose yesterday's date or last week's date as report ending date, just in case they want to. Then you can calculate a rolling 60 days (backward) without the unnecessary second prompt for the report starting date. That is the best way to handle date prompt.

Hope this helps.