BusinessObjects Board

Previous Month reporting object

building semantic layer
#1

Hi Gurus,

I’m trying to create a reporting object to be used in my Query Filters so that I can schedule a report dynamically without having to change the dates manually each time. The report is supposed to be able to report data from whole previous month if I run it by this month. For example, a date field which equals to “Previous Month”

I have created an object in Universe with this syntax however it looks it doesn’t seem to work correctly.

CASE WHEN month(dbo.fn_adjusted_date(DATEDIFF(s,'1970-01-01 00:00:00', GETDATE())))=1 THEN 12 ELSE month (dbo.fn_adjusted_date(DATEDIFF(s,'1970-01-01 00:00:00', GETDATE())))-1 END

Appreciate if anyone can guide me. Thanks!

jordy :south_africa: (BOB member since 2014-12-29)

#2

Hi,

What database do you use?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#3

Two objects START_OF_PREVIOUS_MONTH and END_OF_PREVIOUS_MONTH using the appropriate bits of code in the sticky here: DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

Then in your report, put a filter where date_object between START_OF_PREVIOUS_MONTH and END_OF_PREVIOUS_MONTH

debbie

Debbie :uk: (BOB member since 2005-03-01)

#4

Getdate() suggests SQL Server

Correct.

Jordy, for clarification, you would have a query filter something like:

Transaction Date between … and …

Replace the dots above with the objects that you have created based on the sticky topic, as recommended by Debbie.

Regards,
Mark

#5

I was going to reply sooner but yea, that’s exactly what I needed! Thanks all :slight_smile:

It’s SQL Server btw.

jordy :south_africa: (BOB member since 2014-12-29)