BusinessObjects Board

Need Last Month Data in Range Prompt

Hi,

I have a prompt like this -

DEAL.DATE >= @Prompt('DATE_FROM','D',,mono,free) AND DEAL.DATE <= @Prompt('DATE_TO','D',,mono,free) 

Now here I need to replace this prompt as a filter and whenever user fetches this filter, he/she will get last month data. For an e.g.;

If today I am running a report that is today’s date is 9 Aug 2011 and If I fetch this filter it should give me the data from 1 July 2011 to 31 July 2011.

Could someone help me …!!


aniketp :uk: (BOB member since 2007-10-05)

Hi,
Please correct me if my understanding is wrong. You need to replace the prompt with a filter condition, right?
If your Universe is based on Oracle, then you could define the filter object as:

DEAL.DATE >= (last_day(add_months(sysdate, -2))+1) AND DEAL.DATE <= last_day(add_months(sysdate, -1))

mehak83 (BOB member since 2011-06-28)

Hey Mehak, thanks a lot for your response. Will this work for all months, like Feb also ?

One more thing, Right now I am not able to test as I don’t have acces to universe, once I get it , I will test and update you soon.


aniketp :uk: (BOB member since 2007-10-05)

Also you can use below condition object.

DEAL.DATE >= trunc(add_months(sysdate,-1),‘mm’) AND DEAL.DATE <= trunc(sysdate,‘mm’)-1


Rakesh_K :india: (BOB member since 2007-12-11)

Hey Rakesh, thanks for your response. Mehak’s solution is working fine, I will try yours too and update you soon.

Once again thank you for your input.


aniketp :uk: (BOB member since 2007-10-05)

I always creat a suite of date constants in a Universe like:

Today
Yesterday
First of Last Week
Last of Last Week
Firts of Current Month
First of Last Month
Last of Last Month

…and so on for as many date intervals as you might need.

Then you can use those objects to create filters (and other calculations) like:

[Data Date] between [First of Last Month] and [Last of Last Month]

Those filters included in the report query will give you dynamic data based on the current date.

Works like a charm.


datagal :us: (BOB member since 2005-12-21)

Hi datagal,

I have to implement your filters logic with a prompt. How do you implement it using prompt syntax at the universe level?

Prompt : date between first day of quarter and last day of quarter

-Thanks


objbusiness (BOB member since 2011-08-04)

There is a topic with quite a few suggestions for date objects as a “sticky” in this forum. Try having a look there first.

If that topic doesn’t help, you need to post which database you’re connecting to as the functions are not the same for every vendor.


Dave Rathbun :us: (BOB member since 2002-06-06)

objbusiness:

Can you give me a little more detail?

Do you want the user to select a date and them return the entire quarter around that date? Why not just promt the user for Year and Quarter?


datagal :us: (BOB member since 2005-12-21)