BusinessObjects Board

previous month filter syntax for sql server 2008

Hi,

At universe level, I have to create predefined filters for previos month, previous 2 months and previous 6 months. In universe we have Monthyear object (date format like 1/1/2009) and monthyear-char object (character type and data like jan 2009). I know how to create these filters using sysdate but the data is not upto date. So I want to use one of those 2 objects to create those predefined filters.

Can any one provide right syntax?

I am using BO Edge XI3.1 sp2 and Sql server 2008.

Thanks,
Raj.


Raj2010 (BOB member since 2009-09-16)

Previous Month Begin Date:

DATEADD(mm,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE()),0))

Previous Month End Date:

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

This has been discussed lot of times, Please search the forum for additional objects.


BO_Chief :us: (BOB member since 2004-06-06)

Hi BO_Chief,

Thanks for your reply.

I am looking something like this:

to_char(trunc(@Select(Date \Last Update Date),‘MONTH’)-1,‘MM’)

this one works in Oracle but i am looking similar to this in Sql server.

Thanks,
Raj.


Raj2010 (BOB member since 2009-09-16)

I would recommend creating a derived table that has all the date ranges pre-defined. You can either define the derived table in the universe or as a view in the database – both work. It would look something like this

Select ‘All Days’ as DATE_RANGE, min(DATE) as DATE_RANGE_MIN, max(DATE) as DATE_RANGE_MAX from DATES_TABLE
union
Select ‘Today’ as DATE_RANGE, max(DATE) as DATE_RANGE_MIN, max(DATE) as DATE_RANGE_MAX from DATES_TABLE

Either this can be exposed as an object directly in the universe or as a condition object with a prompt. This would be the most flexible way of approaching this.


hbanerjee :singapore: (BOB member since 2010-08-17)

Have a look at this sticky thread - it should give you everything you need to base your logic on.