Hi All,
Currently I have a report that runs with entry of Start and End Dates. (prompts)
I would like to have the option to schedule the report and have it delivered via email to the users for current_date-1 for selected centers to automate.
Can this be done within the existing report or do I need to build a new report and how can I do this? Any help would be appreciated! Thanks in advance.
shivsiddh (BOB member since 2014-11-13)
This post should help:-
Mak 1 
(BOB member since 2005-01-06)
Thanks for replying, in the example above if I understand right, it will run a report for the default date unless the user manually puts a date in correct?
I needed to be scheduled to run automatically, each night, for the previous day. Right now the only way to run it for the previous day is to manually schedule it and select the previous date.
That is why I was looking to see if I can use current date-1 somewhere so that it automatically runs for that when report is scheduled daily.
shivsiddh (BOB member since 2014-11-13)
Yup :).
Mak 1 
(BOB member since 2005-01-06)
How do I use that example towards mine, how to set the default prompt to current date -1?
this is how my prompts look right now
shivsiddh (BOB member since 2014-11-13)
If you read the post properly, you will see you need a universe object for current date.
Mak 1 (BOB member since 2005-01-06)
In the post it says we don’t need any universe work
shivsiddh (BOB member since 2014-11-13)
Mak 1 (BOB member since 2005-01-06)
I think there is a confusion. I get that part where he says he uses the already existing current date object from the universe.
I need to schedule it to run daily and to display the data for the previous day. Hope it makes sense, again thanks for the help
shivsiddh (BOB member since 2014-11-13)
If you have an object in your universe, that returns the previous day, e.g. for Oracle.
trunc(sydate)-1The the logic is simply TableName.Date = trunc(sydate)-1
When scheduled each day, it will return the previous days data.
Mak 1 (BOB member since 2005-01-06)
Got it, But this cant be done directly at the report level in WEbI?
Wanted to try and avoid making changes to the universe.
shivsiddh (BOB member since 2014-11-13)
Hi, you can try using subqueries to work with your input date.
How would you like to query previous day ?
Dates = Today - 1, Dates < Today-1, others ?
Cheers,
Rogerio
rgoulart 
(BOB member since 2011-08-21)
Could you elaborate on this?
and I wanted Dates = today - 1.
Basically when the report is scheduled, I want it to return yesterday data.
shivsiddh (BOB member since 2014-11-13)
What is the greatest date in your database ? todays date ?
rgoulart (BOB member since 2011-08-21)
Yes todays date
shivsiddh (BOB member since 2014-11-13)
Okay,
the whole idea of my solution is going “slicing” the date object.
You need to build 3 queries :
The first one will return all your dates;
The second wll return all dates less than any dates from the first query, this will return all dates prior yesterday (including yesterday)
The third and last query will return the dates which are less or equal any dates from the second query AND dates that are greater or equal all dates from the second query.
I´m attaching a MsWord document showing you the screenshots of the queries
Cheers,
Rogerio
Dates BOB Forum.doc (183.0 KB)
rgoulart 
(BOB member since 2011-08-21)
I tried doing that but it is throwing me and error. This is how my 3 queries look (view attached doc)
Q1.doc (73.0 KB)
shivsiddh (BOB member since 2014-11-13)
what error are you getting?
rgoulart (BOB member since 2011-08-21)
I don’t think I am doing it right. I am getting this error
unexpected error WIH 444444.
I tried again and I got some database error. When I revert back to the original report there are no errors. These errors are only coming after I make the changes
shivsiddh (BOB member since 2014-11-13)