General OBIEE

Get Involved. Join the Conversation.

Topic

    Brenda Jacks
    How to do MAX Date Logic less than Prompted AsOfDate in...
    Topic posted June 14, 2017 by Brenda JacksBronze Medal: 1,250+ Points, tagged Analyses, Compensation, Core HR, Customizing Reports, Data Models, Fusion, OBI Answers, OBIEE Answers, OTBI, SQL 
    694 Views, 1 Comment
    Title:
    How to do MAX Date Logic less than Prompted AsOfDate in Analysis
    Summary:
    Trying to get the maximum Salary Change Date in OTBI Analysis
    Content:

    I am trying to convert a filter to SQL to get the maximum Salary Change Date that is less than or equal to a prompted AsOfDate in an OTBI Analysis.  Here is the filter I am using:

     

    "Compensation - Salary History Details Real Time"."Salary Details"."Salary change date" =
    SELECT MAX("Compensation - Salary History Details Real Time"."Salary Details"."Salary change date")
     FROM "Compensation - Salary History Details Real Time"
    WHERE "Compensation - Salary History Details Real Time"."Salary Details"."Salary change date" <= '@{AsOfDate}{@{system.currentTime}[YYYY-MM-DD]}'

     

    I'm getting error getting drill information and wondering how I can fix this?  I have attached the catalog.

    Thank you!

    Brenda

    Comment

     

    • rashid ramzan

      First: What is the error that you are getting?

      Second: By setting VARIABLE PARAM_EFFECTIVE_DATE  to AsOfDate it will return the latest salary change record on or before that date so I don't think you need any Max

      If your error is related to Date Format then try MM/DD/YYYY in your prompt. Thanks

      Regards,

      Rashid