cancel
Showing results for 
Search instead for 
Did you mean: 

Scheduling the Webi report based on the current date

Former Member
0 Kudos

Hi ,

Can I schedule the report to give the results of the previous day (report Daily) and other one to give the results of the previous month (report Monthly)? How can I do it?

Thanks in advance.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Peter, I already responded to this question, but it is not showing up for some reason. Let's see if this shows up, and if not, then I will answer it again!

Bill

Answers (2)

Answers (2)

Former Member
0 Kudos

Checkout the "Magic Date" option suggested by Dave in the this thread. I think that's the route for you.

Anil

Former Member
0 Kudos

Hi Anil,

Thanks for you answer. It is very heplfull for the other part of the work on this project.

Laurent

Former Member
0 Kudos

Yes, you can schedule Daily, to run every N days, or Weekly to run only on specific days of the week (like only weekdays) and you can schedule monthly to run every N months at a particular start date and time.

To only run for the previous day or previous month, you would need to create a filter or possibly a filter on a variable, that would be something like RelativeDate(CurrentDate();-1), and something similar with the month number, possibly conditional when you hit January. Or you could use a prompt.

Of course you could also always set up the specific date ranges in the Universe for [Yesterday] and [Previous Month] and then have the report filter on those.

Thanks

Former Member
0 Kudos

Bill, could you explain me in more details how to set up the specific date ranges in the Universe for Yesterday to only run for the previous day? I was thinking to do something like this:

T_GVQSTATCG1_HOUR.DATE_YYYYMMDD= @Prompt(u2018Enter a Date)u2019,'Du2019,u201D,MONO,CONSTRAINED)-1

OR

sysdate-1 = @Variable(u2018Enter a Date)u2019)

Would this work? I tried to enter it into Date dimension in the univers but I had a message "incorrect definition". Could you correct a mistake, please?

Thanks very much.

Edited by: Laurent PETER on Jun 9, 2010 10:01 AM

Edited by: Laurent PETER on Jun 9, 2010 1:38 PM

Edited by: Laurent PETER on Jun 9, 2010 2:27 PM

Edited by: Laurent PETER on Jun 9, 2010 2:32 PM

Former Member
0 Kudos

Hi Peter, do you need it to be a prompt that is entered by the user? Or do you just need it to always be yesterday? I have to run to a meeting, but will give you the syntax for the universe object when I return.

Thanks!

Former Member
0 Kudos

Hi Bill,

I need it to always be yesterday and in another report to always be the previous month. The name of the universe object is T_GVQSTATCG1_HOUR.DATE_YYYYMMDD for the first report and for the other one T_GVQSTATCG1_DAY.MONTH_NAME. Do I need to create a new object or simply change the properties of the existing one? These two objects I gave you the names are default objects to show the date or month.

Thank you very much.

Edited by: Laurent PETER on Jun 9, 2010 4:57 PM

Edited by: Laurent PETER on Jun 9, 2010 5:27 PM

Former Member
0 Kudos

Hi Peter,

Well for yesterday, you can use something like:

CAST(CONVERT(VARCHAR, T_GVQSTATCG1_HOUR.DATE_YYYYMMDD, 110) AS DATETIME) = DATEADD(dd,-1,(select T_GVQSTATCG1_HOUR.DATE_YYYYMMDD from YourTable where Current_Day_Ind = 'Y'))

We have a date table in our data warehouse with a Current Day Indicator field. If you don't have something like that, then you should be able to just use:

 CAST(CONVERT(VARCHAR, DateAdd(dd,-1, GetDate()), 110) AS DATETIME)

You would use something similar for previous month, parsing out the month number from the date, and then subtracting 1, except of course for January, where you could just plug in a case statement to handle that.

This is assuming you are on SQL Server. Syntax may be different in Universe for Oracle or other.

Does that make sense?

Thanks

Former Member
0 Kudos

Hi Bill,

We use Universe based on Oracle database.

Thanks for your help.

Laurent Peter

Edited by: Laurent PETER on Jun 10, 2010 3:17 PM

Hi everyone,

The complete solution is to add predefined condition in the Universe using this synthax:

Previous month:

T_GVQSTATCG1_DAY.MONTH_N_IN_YEAR= to_char(add_months(sysdate,-2), 'MM')

Yesterday:

T_GVQSTATCG1_HOUR.DATE_YYYYMMDD = to_char(sysdate-1,'YYYYMMDD')

Regards,

Edited by: Laurent PETER on Sep 10, 2010 11:21 AM