Extra Pages

▼

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

at

13 comments:

  1. AnonymousMarch 13, 2009 at 2:45 PM

    Hi,

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

    Regards

    Manohar

    ReplyDelete
  2. John MinkjanMarch 17, 2009 at 1:23 PM

    Have a look here:

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

    regards

    John

    ReplyDelete
  3. eejimkosMarch 30, 2009 at 4:31 PM

    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.

    ReplyDelete
  4. John MinkjanApril 1, 2009 at 7:36 PM

    Hi Dimitris,

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

    regards

    ReplyDelete
  5. eejimkosApril 2, 2009 at 2:26 AM

    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.

    ReplyDelete
  6. imtiazDecember 13, 2010 at 1:34 PM

    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.

    ReplyDelete
  7. AnonymousMarch 14, 2011 at 3:58 PM

    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

    ReplyDelete
  8. ParagMarch 16, 2011 at 8:07 AM

    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

    ReplyDelete
  9. John MinkjanMarch 16, 2011 at 8:18 AM

    @imtiaz,

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

    regards

    John

    ReplyDelete
  10. John MinkjanMarch 16, 2011 at 8:23 AM

    @parag,

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

    regards

    John

    ReplyDelete
  11. ParagApril 6, 2011 at 11:26 AM

    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

    ReplyDelete
  12. thangaamudhuraj.selvarajanMay 30, 2011 at 12:41 PM

    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.

    ReplyDelete
  13. AnonymousJuly 20, 2011 at 3:51 PM

    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

    ReplyDelete

Note: Only a member of this blog may post a comment.