Dynamic Date Ranges Using SQL Expressions In Insight (OBIEE)

Version 5

    OBIEE doesn't appear to have any dynamic date ranges as native functionality. You typically have to prompt date or add static values to limit date ranges. I've found a way to do this so thought I would share if others are experiencing the same issue.

     

    If you want a report to default to the last n months or the last n days you will need to apply a filter and use the "SQL Expression". In the example below I want to show all page views that have happened in the last 30 days. I have added the activity field "Page View Date" to the filter and now I want to edit this.

     

    Firstly you need to choose "SQL Expression"...

     

     

     

    You can keep the "value" field as blank. Then you need to add a SQL expression...

     

     

    Here is a list of common SQL expressions for this function:

     

    TIMESTAMPADD(interval, integer_exp, time_exp)

     

     

    SummarySQL Expression (to be added)
    Last 12 hoursTIMESTAMPADD(SQL_TSI_HOUR, -12, CURRENT_DATE)
    Last 30 daysTIMESTAMPADD(SQL_TSI_DAY, -30, CURRENT_DATE)
    Last 4 weeksTIMESTAMPADD(SQL_TSI_WEEK, -4, CURRENT_DATE)
    Last 1 yearsTIMESTAMPADD(SQL_TSI_YEAR, -1, CURRENT_DATE)

     

    Provided by this site: Date and Time Functions