Wednesday, 25 January 2012

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

1 comment:

  1. Useful tip. Thank you.
    What if I want to create presentation variables for 'between dates' using date dimension.
    Eg: Date1 should contain min(d_date) and Date2 should hold max(d_date).
    Will this work?

    ReplyDelete