BusinessObjects Board

Create Previous Month Predefined Filter in Universe

building semantic layer
#1

Hi,

We have a predefined filter in our universe “Previous Month” which is:

{fn month(@Select(XX\Report Date))} =
{fn month(convert (SMALLDATETIME, {fn CURDATE()}))}-1

which only works if you also use “This Year” filter at the same time, when designing the report, otherwise the results return the previous month for all years in the database.

“This year” is: {fn year(@Select(XX\Report Date))}=
{fn year(convert (SMALLDATETIME, {fn CURDATE()}))}

I need to create a filter in the universe for previous month, which will take into account the year, so that in January I don’t have the problem of using these two objects “Previous Month” and “This Year” as it won’t work. We want to schedule these reports via BCA and don’t want to have to recreate them in January for this problem.

Please can you give me some feedback as how to go about creating one filter for this, as I keep getting errors.

(As an aside - how come Business Objects dosn’t create this code for us as a standard filter- surely every company uses Date filters??)

Many thanks :wink:

fox (BOB member since 2008-07-07)

#2

What database are you using?

I’ve created two dates in the Dates Class - Start of Previous Period and End of Previous Period. I’ve then created a predefined condition for ledger date as per below:

@Select(Ledger Dates\Ledger Date) BETWEEN  @Select(Calendar Dates\Start of Previous Period) AND @Select(Calendar Dates\End of Previous Period)

Hope that helps,
Mark

#3

Thanks Mark,

I think that is going to work. I just need to create the two objects “1st Day of Previous Month” and “Last Day of Previous Month”.

I can create an object for “Last Day of Previous Month”

convert (SMALLDATETIME, {fn CURDATE()})-{fn dayofmonth(convert (SMALLDATETIME, {fn CURDATE()}))}

But the “1st day of Current Month” is being problematic.

convert (SMALLDATETIME, {fn CURDATE()})-{fn dayofmonth(convert (SMALLDATETIME, {fn CURDATE()})-1)}

For some reason, this returns no data when testing in BO but it works as a select statement in query analsyer?

I then want to minus a month from that to get the object “1st Day of Previous Month”

Any other help is appreciated :wink:

fox (BOB member since 2008-07-07)

#4

What DBMS are you on? Looks a bit like AS400 but I’ve not used that for a while.

There’s info in one of the sticky threads about it I think.

#5

Hi,

Sorry we using SQL Server and BO 11.5

fox (BOB member since 2008-07-07)

#6

Yay, I found some code which is working perfectly. Thanks all.

(dateadd(month, datediff(month, -1, getdate()) - 2, -1) + 1)

fox (BOB member since 2008-07-07)

#7

This was a great thread!

Has anyone done this against an Oracle database?

jl

jleblanc :us: (BOB member since 2007-05-02)

#8

That’s what I did.

All details in this post:

#9

Awesome!

Thank you for the information and your prompt response!

jl

jleblanc :us: (BOB member since 2007-05-02)

#10

Hi Buddy,

are you using it as an object in a universe? when I use the above formula, I am keep gettting “SQL can not be preapared error”

I am using to get the 1st day of the current month as:

dateadd(month, datediff(month, -1, getdate()) - 2, -1) + 1)

I am looking for last day of the current month as well. We are using SQL server. Any help is greatly appreciated.

Thanks,
-rk

rit372002 :us: (BOB member since 2006-11-02)

#11

These should help you:

select
	cast(convert(char(7),getdate(),23)+'-01' as datetime) -- first day of curr month
,	dateadd(d,-1,cast(convert(char(7),getdate(),23)+'-01' as datetime)) -- last day of last month
,	dateadd(m, 1,cast(convert(char(7),getdate(),23)+'-01' as datetime)) -- first day of next month
,	dateadd(m,-1,cast(convert(char(7),getdate(),23)+'-01' as datetime)) -- first day of last month