Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OBIEE 12C Prompt year in date Dynamically

Received Response
31
Views
4
Comments
Rank 5 - Community Champion

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Answers

  • Rank 6 - Analytics Lead

    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

  • Rank 8 - Analytics Strategist

    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.

  • Rank 10 - Analytics Guru

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

  • Rank 5 - Community Champion

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

Welcome!

It looks like you're new here. Sign in or register to get started.