General OBIEE

Get Involved. Join the Conversation.

Topic

    Tim Welsh
    Setting relative dates in a filter for an OTBI AnalysisAnswered
    Topic posted April 28, 2017 by Tim WelshBlue Ribbon: 750+ Points, tagged Analyses, OBIEE Answers, OTBI, Reports, SQL 
    2157 Views, 17 Comments
    Title:
    Setting relative dates in a filter for an OTBI Analysis
    Summary:
    In an OTBI Analysis is there a way to add into a relative date range into a filter?
    Content:

    I need an Analysis with a filter to pick up dates within the last week.  I don't see a way to do this in the normal filter properties and I'm getting errors when i add the below code into "Convert this filter to SQL"  Is there a way to do this either with the regular filter or with the Advanced SQL filter?  

     BETWEEN (SYSDATE - 7) AND SYSDATE

    Best Comment

    Manoj Cheruvathoor

    hi Tim,

    I think this should work for you

    "date variable" > TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE) 

    date variable is the date field that you are filtering for. just put this in the sql filter. Hope this helps

    Thanks
    Manoj

    Comment

     

    • Manoj Cheruvathoor

      Hi Tim,

       

      you can look at the TIMESTAMPADD function. use a negative number (I think it should be -7 in your case) and the sysdate. use this in the in the filter.

      Thanks
      Manoj

    • Manoj Cheruvathoor

      hi Tim,

      I think this should work for you

      "date variable" > TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE) 

      date variable is the date field that you are filtering for. just put this in the sql filter. Hope this helps

      Thanks
      Manoj

      • Tim Welsh

        Thanks Manoj, using a filter on date with the SQL expression of TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)  nailed it.  

      • Tim Welsh

        Manoj,

        Do you know how I can capture this date (range) for display in the report output header?  

        Thanks,

        Tim

      • Murali Gangidi

        Hi Manoj,

        Do you know how can this be used in Prompts. I have a Date Prompt and need to default the date as (CURRENT_DATE-1) because the report will be scheduled and can run adhoc when needed to input the dates by the user.

        I tried this with SQL Expression in Prompts but didn't work.

         

        SELECT
        CASE WHEN 1=0 THEN
        "Cash Management - Bank Statements Real Time"."Time"."Report Date" ELSE timestampadd
        (SQL_TSI_DAY,-1,CURRENT_DATE) END
        FROM "Cash Management - Bank Statements Real Time"

         

        When i tried this with Variable Expression and it's working correctly defaulting to CURRENT_DATE, but need (CURRENT_DATE)-1

        Attached screenshot

         

        • Kamran Butt

          Hi Murali,

          I have a similar requirement in which I need the date prompt to show last year's date. Did you have any luck with this?

          Thanks!

          -Kamran

    • Richard Chan

      SYSDATE is CURRENT_DATE in OBIEE for your future reference

    • Manoj Cheruvathoor

      Hi Tim,

      glad to hear, it worked for you. instead of the day you can use these variables

      SQL_TSI_SECOND,SQL_TSI_MINUTE,SQL_TSI_HOUR,SQL_TSI_DAY,SQL_TSI_WEEK,SQL_TSI_MONTH,SQL_TSI_QUARTER,SQL_TSI_YEAR  for different time periods. You can see worked examples in the OBIEE help https://reporting-bichf06.taleo.net/analytics/olh/l_en/calendar.htm#CHDJDGBE . Hope this helps.

      thanks
      Manoj

    • Nicole Hicks

      FILTER("Requisition Status - Historical"."Req. Historical Status Start Date" USING (("Requisition Status - Historical"."Req. Historical Status Start Date" IN (TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)))))

      I am getting an error on the formula above. Not sure what I am doing wrong.  Can you provide instructions on where and how to enter the formula and direct me to the training on formulas/filters?

       

      "date variable" > TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE) 

    • Murali Gangidi

      Do you know how can this be used in Prompts. I have a Date Prompt and need to default the date as (CURRENT_DATE-1) because the report will be scheduled and can run adhoc when needed to input the dates by the user.

      I tried this with SQL Expression in Prompts but didn't work.

       

      SELECT
      CASE WHEN 1=0 THEN
      "Cash Management - Bank Statements Real Time"."Time"."Report Date" ELSE timestampadd
      (SQL_TSI_DAY,-1,CURRENT_DATE) END
      FROM "Cash Management - Bank Statements Real Time"

       

      When i tried this with Variable Expression and it's working correctly defaulting to CURRENT_DATE, but need (CURRENT_DATE)-1

      Attached screenshot

    • Farooq Haider Syed, PMP

      thank for sharing