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:`
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
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
Step 7: reference the variable in your report:
Step 8 : Create the dashboard:
Till Next Time
13 comments:
Hi,
I Tried this but struck at referencing the variable in the report.
Can you give those steps as well.
Regards
Manohar
Have a look here:
http://obiee101.blogspot.com/2009/03/obiee-referencing-presentation.html
regards
John
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.
Hi Dimitris,
Have a look here:
http://obiee101.blogspot.com/2009/04/obiee-between-prompt-using-calendar.html
regards
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.
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.
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
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
@imtiaz,
looks like your are missing the time compenent: 00:00:00
regards
John
@parag,
Add the from date to a presentation variable and use it in the SQL for the until date.
regards
John
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
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.
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
Post a Comment