BusinessObjects Board

Moving Date Filters

Hi everyone,

I have had a request for some filters as a user wants to schedule a report but am struggling to find something that fits, any help would be very much appreciated :smiley:

Here goes:

Say I run a report today, 17/02/2011, I need the data to always come back from the Monday of the current week, so on Monday (14/02/2011) I need the dates 14/02/2009 - 14/02/2010, on Tuesday (15/02/2011) I need the dates 14/02/2009 - 15/02/2010 and so on.

However, if the report ran on a Sunday (21/02/2011) it would provide 21/02/2009 - 20/02/2010 as it would be providing a complete year which is the minimum I need.

So the number of days covered will depend on what day of the week you run the report.

On a Sunday you get 365 days, Monday and up to Saturday you would get 371 days.

Hope that makes sense it has been sending me bog-eyed :crazy_face:

Thanks for your help in advance, it is much appreciated!

Best wishes,

Rolls.


dunbarr (BOB member since 2009-09-17)

You didn’t say what database this is - makes a difference for people providing you with some sample code. In Oracle the add_months function would be a good starting point…take 24 months from your start date…


Nick Daniels :uk: (BOB member since 2002-08-15)

Creating objects with the appropriate relative dates would be a good idea. You can then use these objects as required

Check out this topic for details.

Regards,
Mark

Hi Nick,

Apologies, I am working in Oracle :oops:

Hi Mark,

Thanks for the link :smiley:

I can get the bit to work where I get the dates with this bit of code:

@Select(Core Dates\Recorded Date) BETWEEN add_months(Next_Day((sysdate-7), ‘MONDAY’), -24) AND
add_months((sysdate-7),-12)-(1/(606024))

which will increase as the days go up (although it starts at 15/02/2011 instead of 14/02/2011? I’m working on that) but am still stuggling with how to get the last bit:

Any ideas would be much appreciated?

Cheers,

Rolls.


dunbarr (BOB member since 2009-09-17)

Hi,

You can have a case statement and based on the current day you can select your statement.

modify your current statement for selecting 365 days (On Sunday). and in the case statement based upon the current day select the statement1 or statement2.

Regards,
Saurabh


saurabhg :india: (BOB member since 2007-04-02)

Hi Saurabh

Thanks for this, I will have a look at using CASE, did you have any code in mind that would do this specifically?

Many thanks,

Rolls.


dunbarr (BOB member since 2009-09-17)