Finding previous month's First and last day (Oracle 11)

[travisk]

We're on BOE 3.1 SP3, and Oracle 11g.

I have some monthly and weekly reports that currently use a field in the data and prompt users to enter a from and to date range.

For example, I manually run a report for a user and enter March 1st, and March 31st. However, we'd like to be able to schedule these reports to automatically figure out the previous month start and end date with no prompts. Then we can just schedule them to run monthly on the server rather than manually entering date values for all these reports. (the reports requiring this automation is growing.)

I have the SQL to find the first and last dates in Oracle, but I don't know how to get it into a BOE report, or if BOE has it's own date functions for figuring this out. This is one of the formulas I have:

select trunc(trunc(sysdate,'MM')-1,'MM') "First Day of Last Month",
trunc(sysdate,'MM')-1 "Last Day of Last Month"
from dual


However, i get parse errors when trying to use this anywhere in BOE.

I also need to find the previous week's start and end date. Say Sunday to Saturday. So, if I scheduled the report to run every monday and it ran today, it would automatically figure the

from date to be:
3/27/2011

To date: 4/2/2011

Ideally these would be dimensions in the universe that could be used as filter objects in a query. So when the report is ran it would be:

From date on report = FirstdayofpreviousMonth universe object.
To date on report = LastDayofpreviousMonth Universe object

So if I ran a monthly report today it would automatically know to run it for March 1st, to March 31st, 2011.

It seems like it's a fairly simple oracle function, but nothing I've tried in BOE works.

Please help?

Thanks!

[jwhite9]

The universe objects won't parse because they are not associated with a table, this is ok. Just create the objects with the desired formula to get the date value and add them to your query filter.

[travisk]

So the oracle SQL I posted will work within the universe though?

[jwhite9]

Your first day of month universe object would have its select property set to

[travisk]

Cool thanks, I'll give it a try.

[patriot3029]

Try this one also

First day of last month add_months(Last_day(sysdate)+1,-2)
Last day of last month add_months(last_day(sysdate),-1)

[sayannayas]

I have some useful DB scripts in my collection above a look here -- http://fundapass.blogspot.in/2012/09/db-scripts.html