Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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
7688438
7688438 Rank 5 - Community Champion
edited August 2024 in Oracle Analytics Cloud and Server

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

Answers

  • Jerry Casey
    Jerry Casey 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

  • Robert Angel
    Robert Angel 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.

  • [Deleted User]
    [Deleted User] Rank 10 - Analytics Guru

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

  • 7688438
    7688438 Rank 5 - Community Champion

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