BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Realtime CMS Reporting from Infolytik (Opens a new window)  

General Notice: No events within the next 45 days.

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


 
Search this topic... | Search WebIntelligence XI... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> WebIntelligence XI  Previous TopicPrint TopicNext Topic
Author Message
travisk
Senior Member
Senior Member



Joined: 16 Jul 2008

Posts: 89



PostPosted: Mon Apr 04, 2011 10:00 am 
Post subject: Finding previous month's First and last day (Oracle 11)

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!
Back to top
jwhite9
Forum Enthusiast
Forum Enthusiast



Joined: 28 Jul 2006

Posts: 1371
Location: Rochester, NY


flag
PostPosted: Mon Apr 04, 2011 10:10 am 
Post subject: Re: Finding previous month's First and last day (Oracle 11)

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.
Back to top
travisk
Senior Member
Senior Member



Joined: 16 Jul 2008

Posts: 89



PostPosted: Mon Apr 04, 2011 10:18 am 
Post subject: Re: Finding previous month's First and last day (Oracle 11)

So the oracle SQL I posted will work within the universe though?
Back to top
jwhite9
Forum Enthusiast
Forum Enthusiast



Joined: 28 Jul 2006

Posts: 1371
Location: Rochester, NY


flag
PostPosted: Mon Apr 04, 2011 10:25 am 
Post subject: Re: Finding previous month's First and last day (Oracle 11)

Your first day of month universe object would have its select property set to
Code:
trunc(trunc(sysdate,'MM')-1,'MM')


You last day of month universe object would have its select property set to
Code:
trunc(sysdate,'MM')-1


When you add the objects to your query filter have a look at the sql, you should see the correct format. You could then paste the sql into Toad/SQL Plus/etc. and run it to be sure it does what you want.
Back to top
travisk
Senior Member
Senior Member



Joined: 16 Jul 2008

Posts: 89



PostPosted: Mon Apr 04, 2011 11:18 am 
Post subject: Re: Finding previous month's First and last day (Oracle 11)

Cool thanks, I'll give it a try.
Back to top
patriot3029
Principal Member
Principal Member



Joined: 08 Dec 2006

Posts: 358
Location: NYC


flag
PostPosted: Mon Apr 04, 2011 4:46 pm 
Post subject: Re: Finding previous month's First and last day (Oracle 11)

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)
Back to top
sayannayas
Forum Member
Forum Member



Joined: 28 Oct 2012

Posts: 1



PostPosted: Sun Oct 28, 2012 8:09 am 
Post subject: Re: Finding previous month's First and last day (Oracle 11)

I have some useful DB scripts in my collection above a look here -- http://fundapass.blogspot.in/2012/09/db-scripts.html
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> WebIntelligence XI  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0475 seconds using 17 queries. (SQL 0.0029 Parse 0.0349 Other 0.0097)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo