Hi Friends
I have a week Object in the Universe. In the report I want to filter for last 12 weeks.
Any help would be greatly appreciated.
Thanks, Amr
amr007 (BOB member since 2004-06-25)
Hi Friends
I have a week Object in the Universe. In the report I want to filter for last 12 weeks.
Any help would be greatly appreciated.
Thanks, Amr
amr007 (BOB member since 2004-06-25)
Right-click on the week column in your report and Insert Filter.
But I expect you are wanting more than that?
scott copeland (BOB member since 2002-08-15)
Hi AMR - you should try searching/posting this in the Reporter section, but to briefly give you a heads up - you will need to get the System current week to perform this calculation or you will have to calculate it yourself.
I’m not sure what RDBMS you are using and these type of calculations can get quite nasty
e.g. Here is an example of a measure that uses the System date to automatically always provide the Current Week amounts. It is in ORACLE.
nvl(sum(decode(least(to_number(to_char(sysdate,'rrrrww'))-to_number(to_char(TIME_DEPARTURE.FULL_DATE,'rrrrww')),0),0,decode(greatest(to_number(to_char(sysdate,'rrrrww'))-to_number(to_char(TIME_DEPARTURE.FULL_DATE,'rrrrww')),0),0,FCT.CARGO_FFE, 0) )),0)
Be carefull of the period that occurs in the 1st 12 weeks of the year !!
Rgrds
MikeD
MikeD (BOB member since 2002-06-18)
Thanks MikeD
I am using Sql Server 2000 Database. and i want to do this calculation at report level.
regards, amr
amr007 (BOB member since 2004-06-25)
(I’ve not done much reporting lately )
You obviously don’t have any objects that you can use to do this, so how are you going to set your initial query up ?
It’s probably best to ask your Designer to create a Week data object & also to provide a current week object …
I ask this because on a report level you have the
DayNumberOfWeek() and CurrentDate()
formulas that you can use to create flags to control what data is being displayed.
This is probably a long winded approach (ie. report exports please lend a hand).
Use current date and day number of week to get current week
If current week > 12, subtract 12 from current week and test if the data date (week) is greater ie. if true then report.
If current week < 12 ( ie falls in last year) u’re gonna have to decide whether you need to display this data - if so, things could get a tad iffy …
Not sure if i’m on the right track here - or if i’m giving u any idea’s …
Rgrds
MikeD
MikeD (BOB member since 2002-06-18)