Monday, March 2, 2009

OBIEE Between Dates Prompt

One the questions you see regularly on the forums is how to create a 'between dates' dashboard prompt if the date column is not a part of a date dimension. If more important with the use of the calendar control.

Step 1 create a new prompt based on a date dimension:`

image

select 'Calendar' from the control dropdown box, if you do it  later on it won't be available (undocumented feature ;-) ).

Step 2: Alter the column function:

CASE WHEN 1=0 THEN CALENDAR.D_DATE ELSE TIMESTAMP '1900-01-01 00:00:00' END

The CASE WHEN part guarantees validation of the column.

Step 3: Set the presentation variable and label

image

Step 4: repeat step1

Step 5:  Alter the column function:

CASE WHEN 1=0 THEN CALENDAR.D_DATE ELSE TIMESTAMP '2999-12-31 00:00:00' END

This has to be different from the one used in step 2 since OBIEE uses the default outcome of the formula as internal name for the column.

Step 6: Set the presentation variable and label

image

Step 7: reference the variable in your report:

image

Step 8 : Create the dashboard:

image

Till Next Time

13 comments:

Anonymous said...

Hi,

I Tried this but struck at referencing the variable in the report.
Can you give those steps as well.

Regards

Manohar

John Minkjan said...

Have a look here:

http://obiee101.blogspot.com/2009/03/obiee-referencing-presentation.html

regards

John

dimitris said...

hi....tnks...nice work.
but how about create 2 periods of time using calendar dashboard?
i'm really confused...
tnks for your time and keep the good work.

John Minkjan said...

Hi Dimitris,

Have a look here:
http://obiee101.blogspot.com/2009/04/obiee-between-prompt-using-calendar.html

regards

dimitris said...

hi John Minkjan.
tnks for answering.
i hava solve the problem only with drop-downlist and using in my QTY collumn of the fact
--->
FILTER("....QTY" USING (.....Cal_date" BETWEEN date '@{ds1}' AND date '@{de1}'))
<---I do this for the 2 periods.
Unfortuantely,if i use calenar or edit box , the only date which is accepted is in form yyyy-MM-dd.
I want to display it by choosing it from calendar and then the report to understund the presentation variables.
tnks one more time.

imtiaz said...

Hi John,
Can you kindly refer to the thread in the forum. http://forums.oracle.com/forums/thread.jspa?threadID=2143881&tstart=0

Thanks in advance,
Imtiaz.

Anonymous said...

Very useful information.

Tried to implement the same for one of my requirement. Getting an
error

[nQSError: 46046] Datetime value 3/1/2011 does not match the specified format. (HY000)

Will you please help me in this.

Thanks,

Susee

Parag said...

Hi John,

I want a date prompt with between operator and the req is in such a way that when we select the from date the to date will be restricted to next seven days only.

Can you please help .

Thanks in Advance.

Regards,
Parag
paragbhargava29@gmail.com

John Minkjan said...

@imtiaz,

looks like your are missing the time compenent: 00:00:00

regards

John

John Minkjan said...

@parag,

Add the from date to a presentation variable and use it in the SQL for the until date.

regards

John

Parag said...

Hi John,

Thanks for the reply. But Still I am not able to figure out the issue. Can you please elaborate it.

If possible you can mail me on paragbhargava29@gmail.com

thangaamudhuraj.selvarajan said...

Hi John, I am Amudhu.
This is really useful.
But I have a reqmnt like displaying the presentation variable in the chart view's Axis title. For EX: If I select a currency in prompt I want that currency symbol to change dynamically based on the prompt selection in the chart view's axis title and its values. I tried with the syntax u mentioned, its not working for axis title.
Can any1 help me out with this.

Anonymous said...

Hi,

Your blog is very useful, thanks for posting such information.

I have a requirement were we are currently using in between function using calender in prompt for many reports now we want to upgrade the reports where the start date cannot be greater than end date.

How to implement it using calender or by using in between drop-down list based on only one column?

NOTE: we can make changes to existing prompt as there are numerous reports which are already built.

Regards,

Preet