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
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
Posted by John Minkjan at 3/02/2009 11:11:00 AM
Labels: ALL POSTS, DATE/TIME, OBIEE, PROMPT