cancel
Showing results for 
Search instead for 
Did you mean: 

WebI report's prompt question

Former Member
0 Kudos

Hi There

I have some WebI reports with prompt date, now I want to schedule these WebI reports, is there a way to set the run date as the prompt. for example, when you refresh the report, prompt will ask you give a date, like 03/01/2011, if I setup the schedule on every first date of the month, I want that running date 02/01/2011,03/01/2011 as the prompt date

is it a doable? or should I modify the report?

thanks

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

our DataMart FACT-HIST table store each record's change by effective from date and effective to date, in order to not to get duplicated records, in the condition we use effective from date <= @Prompt('Please enter a Report Date:','D',,,) and effective To date > @Prompt('Please enter a Report Date:','D',,,), so every month, when we refresh the report, give a date, like '03/01/2011', it will only grab the record which effecitve from date <=03/0/2011 and effective to date >03/01/20111

now If i want to schedule it, in the inforview schedule function, prompt section, what value I put so I dont have to change the report itself?

Former Member
0 Kudos

You need to set up a date object in the universe based on getdate() or something similar depending upon your universe.

I use SQL, so for any reports running on the 1st of the month, I want them to run for the previous month. I use month(getdate(-1) to get last months data for the report.

if I want the report to run for last week's data, I use getdate(-1) for yesterday and getdate(-7) for 7 days ago. I then set my report dates to be between getdate(-7) and getdate(-1).

The reports must be set to refresh on open.

Hope this makes sense.

Former Member
0 Kudos

the reason I am posting this question is I would like to find a simple way to use running date as prompt, because we have so many WebI report with prompt, before every month, we login to infoview and refresh reports and give a date, Now I want to schedule the reports and would like to know if it is possible not to change anything with the reports and pass the running date to the prompt

right now, our reports all use something like Date_Time <= @Prompt('Please enter a Report Date:','D',,,), I need to know if it is possible when we schedule the report, is there a way to pass the running date to this prompt? otherwise, I have to change all these reports if I want to schedule them

thanks

Former Member
0 Kudos

Try to put Defulat date in universe prompt defination as I have told you in my privious solution. You need not to change anything in teh report.

Former Member
0 Kudos

I am not sure if you need running date as prompt date or First date of every month as prompt date( doesnt metter it is running at any date)

If it schedule for every 1st of month then pur Current Date as default value in your prompt formule in the universe.

If the case is second one than In the prompt formula set the default value as the first date of every month.

- to get first date of every month you need to put the below formula

to_char(trunc(trunc(sysdate, 'MM') - 1, 'MM'),'DD-MON-YYYY')

If this default value will give you some Parsing error then thry below one.

Follow the below step:

1 .Build another object in unv = u201CDate Rangeu201D

code:

case when (Date= to_char(trunc(trunc(sysdate, 'MM') - 1, 'MM'),'DD-MON-YYYY') )) then u2018First of monthu2019

end

2. Now "Add First of month" in the defalutvalue for Prompt.

= @Prompt(u2019Date Pickeru2019,'Au2019,DateX,mono,constrained,persistent,{u2019First of monthu2019})

Create this prompt in the "Date Range" objects. or create a condition, What ever way you want it.

Please try this and see if this can work.