General OBIEE

Get Involved. Join the Conversation.

Topic

    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
    Title:
    Formula to capture last 7 days of data
    Summary:
    Formula to capture last 7 days of data
    Content:

    Hello,

     

    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.

    Comment

     

    • 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

       

      Thanks,

      Arun

       

      • Joy Hanneman

        Hello!

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

         

        Thanks!

        Joy

      • 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')

         

        Error Codes: YQCO4T56:OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P
        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.

          Regards,
          Arun

    • 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
      AND
      "Application Current CSW Start Date"."Application Current CSW Start Date"  is Greater Than TIMESTAMPADD(SQL_TSI_DAY, -6,"Special Objects"."Current Date")

       

      Regards,

      Ramesh