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:
Useful tip. Thank you.
ReplyDeleteWhat 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?