General OBIEE

Get Involved. Join the Conversation.

Topic

    Nina Manns
    Using an SQL Date Formula in OBIEE Report
    Topic posted September 20, 2018 by Nina MannsRed Ribbon: 250+ Points, tagged Reports, SQL 
    916 Views, 8 Comments
    Title:
    Using an SQL Date Formula in OBIEE Report
    Summary:
    How do I display previous 30 days of data
    Content:

    Could someone please show me how to tweak the below formula.  I’d like to pull data from the previous 30 days; however, it seems to be pulling for the future 30 days instead. 

                   timestampadd(sql_tsi_day, 30, current_date)

    When I change the formula to the below example it gives me an error and doesn't return any data:

                    timestampadd(sql_tsi_day, -(30), current_date)

    Thank you.

    Version:
    OBIEE 11.1.1.9.0

    Comment

     

    • Christian Berg

      timestampadd(sql_tsi_day, -30, current_date)

      No brackets

      • Nina Manns

        Thank you Christian for your help.  I was trying to use the formula in a Filter and had already written it the way you'd recommend before I asked for help but was still unable to get it to work.  However, I located an old report I used once before with a similar formula and it worked in a Filter.  Thanks again for your support.

        "Submission Dates"."Hire Start Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) AND TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

    • Richard Chan

      or if you want the previous month timestampadd(sql_tsi_month,-1,current_date) if you want to take into account the variable number of days in a month

      • Nina Manns

        Thank you Richard for your help.  I was trying to use the formula in a Filter and had even written it the way you'd recommend before I asked for help but was still unable to get it to work.  I located an old report I used once before with a similar formula and got it to work in a Filter.  Thanks again for your support.

        "Submission Dates"."Hire Start Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) AND TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

    • Saresh Namboorissery

      Hi,

      Does this link helps:

      https://obiee4conceptby.wordpress.com/2012/05/24/dynamic-filter-by-date/

      Thanks,

      Sareesh

      • Nina Manns

        Saresh,

        Thank you for this information.  I actually had tried the formula timestampadd(sql_tsi_month,-30,current_date) in the filter portion of my analysis but could not get it to work so I used the below formula instead and got it to work.  Thanks again for the site information, I'll keep it handy for future use.

        "Submission Dates"."Hire Start Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) AND TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

        • Wendy Ware

          Sounds like you have a working solution but, for future reference, be careful because timestampadd(sql_tsi_month,-30,current_date) will subtract 30 months (not 30 days or 1 month as you are intending.)

          • Nina Manns

            Thank you Wendy for catching that.  What I meant to say was that I'd tried "timestampadd(sql_tsi_month,-1,current_date)" in the filter portion of my analysis previously but could not get it to work, so I had to use the below formula instead which is working.

            "Submission Dates"."Hire Start Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) AND TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))