OBIEE 12C Prompt year in date Dynamically

7688438

Hi all, I am trying to make below scenario work.I have an year Prompt(Value as 2019,2018,2020)..I would like to provide this year prompt into a date filter condition. Year Prompt-@{sch_enr_yr} for Ex: user selected 2019 ("Student Program Detail"."WITHDRAW_DATE" >= '01-JUL-@{sch_enr_yr-1} AND "Student Program Detail"."WITHDRAW_DATE" <= '30-JUN-@{sch_enr_yr} Lower bound date is 01-Jul-2018 (July 1st is static always) Upper bound date is 30-JUN-2019( June 30 th is static always) Only year is dynamic. Thanks in advance. John

Jerry Casey

This format works for me:

"Student Program Detail"."WITHDRAW_DATE"<=  EVALUATE('TO_DATE(%1,%2)' as date,'06/30/'||cast(@{sch_enr_yr}{2018} as char), 'mm/dd/yyyy')

jerry

Robert Angel

I know that this is not always an option for access reasons, but best is to create these type of variables in the rpd, then they are available in a consistent and simple manner across all of answers for the benefit of everyone.

[Deleted User]

Plus it eliminates the need for endless repetition and possibly many different ways of writing spaghetti code.

7688438

Robert, Thanks for your response.This Prompt variable value has to be dynamic.User wants to select these values dynamically.