General OBIEE

Get Involved. Join the Conversation.


    Nicole Hicks
    Formula to capture last 7 days of data
    Topic posted May 4, 2017 by Nicole HicksRed Ribbon: 250+ Points, tagged Customizing Reports, Discussion Forums, Reports, Scheduling or Agents 
    910 Views, 7 Comments
    Formula to capture last 7 days of data
    Formula to capture last 7 days of data



    We are looking to capture requisition in specific statuses (Approved, To Be Approved, Draft, Sourcing and Filled) for the last seven calendar days.  Look for assistance in creating a formula to capture that timeframe so I can set up a weekly agent to run the report.



    • Arun Raj

      You may use the below variable: 

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

      Thanks to Manoj who had posted the above formula in another post. Here is the link to the original post





      • Joy Hanneman


        Would you by chance know how to structure this formula to bring back candidates who have been rejected in the last seven days?




      • Rhea Evans

        I am looking at incomplete submissions and desire to see those that are in the last seven days.

        I am using the field Submission Created Date in the Criteria as a filter.  I can enter a value of 3/8/2019 and all works fine.  Using your notes above, I'm using 'Add More Options" > SQL Expressions and entered:   "Submission Dates"."Submission Created Date" >= TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)

        After running the report, an error is presented:

        Error getting drill information: SELECT "Submission Dates"."Submission Created Date" saw_0, "Submission General Info"."Submission Is Completed" saw_1, "Submission General Info"."Career Site Last Page Reached Name (BL)" saw_2, "Submission General Info"."Career Site Last Page Reached Sequence" saw_3, "Candidate Identification"."Name" saw_4, "Candidate Identification"."Email" saw_5, "Candidate Identification"."Candidate Identifier" saw_6, "Requisition Identification"."Req. Identifier" saw_7, "Submission Source"."Submission Source (BL)" saw_8, "Requisition Identification"."Title (BL)" saw_9 FROM "Recruiting" WHERE ("Submission General Info"."Submission Originator" = 'Candidate') AND ("Submission General Info"."Career Site Name (BL)" IN ('Corporate', 'Corporate_mobile')) AND (("Submission Dates"."Submission Created Date" IN ("Submission Dates"."Submission Created Date" >= TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)))) AND ("Requisition Organization"."Organization Level1 Name" = 'Aerospace') AND ("Requisition Primary Location"."Location Level1 Name" = 'United States') AND ("Submission General Info"."Submission Is Completed" = 'No')


        Odbc driver returned an error (SQLExecDirectW).
        State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)
        State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)
        State: HY000. Code: 27002. [nQSError: 27002] Near <>=>: Syntax error (HY000)
        State: HY000. Code: 26012. [nQSError: 26012] . (HY000)


        Any help you could provide?  Thanks, Rhea

        • Arun Raj

          Hi Rhea,

          You have to select "Is Greater Than" condition and then add SQL Expression as TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)

          See the attached image.

          Hope this fixes the issue.


    • Ramesh Nakka

      Hi Joy,

      You can use the below for formula for  Rejected Candidates for the last 7 days.


      "Application Current CSW"."Application Current CSW Status" IS IN Candidate Reject - Not Applicant;Candidate Reject — No Business Need;Candidate Rejected;Candidate Withdrew
      "Application Current CSW Start Date"."Application Current CSW Start Date"  is Greater Than TIMESTAMPADD(SQL_TSI_DAY, -6,"Special Objects"."Current Date")