on 2010 Jun 08 4:45 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
User | Count |
---|---|
66 | |
10 | |
10 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.