Wednesday, April 1, 2009

OBIEE Between prompt using the calendar control

Sometimes you need a between prompt on a date but the fields but you can't use the normal is between because of the complex filter. Here is a possible workaround. In the repository create a dummy table called Dates with two columns Date1 and Date2 based on the current date.

image

Next create a prompt and put the result in two presentation variables:image

Next create the complex filter:

image

Till Next Time

10 comments:

dimitris said...

hi....
how about choose two periods of time for a specific measure value using calendar???

Goran Ocko said...
This comment has been removed by the author.
Goran Ocko said...

Hi John.

The first part of the filter condition (before OR, between part) can be also fixed with two dummy date dashboard prompts:

1. cast('01.01.2009' as date)
and
2. cast('01.02.2009' as date)

Both are calendar and they are filling presentation variables so u need to use convert this filter to SQL for Calendar Date column to write manually between condition.

For example:

Caledar Date between '@{p_date_from}{some default value}' and '@{p_date_to}{some default value}'

Regards,
Goran

http://108obiee.blogspot.com/

John Minkjan said...

Hi Goran,

Your right, I like doing it in the repository to avoid problems with casting conventions on the server. Both work.

regards

Sam said...

Do you know of a way to default the Start Date Calendar prompt to be say, 3 months before the current date? And default the End Date Calendar prompt to the current date?

Thank you very much

John Minkjan said...

@Sam,

Set the default to SQL:

SELECT CASE WHEN 1=0 then "D0 Time"."T00 Calendar Date" else TIMESTAMPADD(SQL_TSI_MONTH, -3,CURRENT_DATE) end FROM "Sample Sales"

Anonymous said...

Hi, good post.
If I want the First Day of the Current Month and the Last Day of the Current Month?
For example: I'm on 03/11/2010, so I want it shows 01/11/2010 and 30/11/2010. Thank you very much

Anonymous said...

I got it and I want to share it with you:
First Day:
SELECT CASE WHEN 1=0 then Time."Start date" else TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE)* -(1) + 1, CURRENT_DATE) end FROM WEB_REPOSITORY

Last Day:
SELECT CASE WHEN 1=0 then Time."End Date" else TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) end FROM WEB_REPOSITORY

John Minkjan said...

Thanks for sharing

regards

John

Chinmay said...

Hi Gurus,

I have created one logical column and casted as an integer

CAST ( EXTRACT( HOUR FROM DW.TABLE2.COL2) AS INTEGER )

and attached along with date in page prompt level with greter than condition
when i selected 10 then it is showing data related to 10 AM only and not showing time after 10 AM and PM as well
http://forums.oracle.com/forums/thread.jspa?threadID=2127783
it is really really urgent

thanks