General OBIEE

Get Involved. Join the Conversation.

Topic

    Kim Welker
    Return Candidate Name only for Specific CSW Selection...
    Topic posted March 2, 2017 by Kim WelkerBlue Ribbon: 750+ Points, tagged Analyses, Customizing Reports, OBI Answers, OBIEE Answers, SQL 
    206 Views, 11 Comments
    Title:
    Return Candidate Name only for Specific CSW Selection steps/statuses
    Summary:
    How to modify my SQL statement to remove redundancies
    Content:

    Hi!  I've created a report to return all Open Requisitions as well as Filled Requisitions for the past year.  The report filters work well, however I have additionally created a field using the following SQL statement in an attempt to display Candidate Name only when they match specific Steps and Status conditions:

    CASE WHEN "Submission CSW Status - Current"."Current Step Name" IN ('Offer', 'Pre-Hire') AND "Submission CSW Status - Current"."Current Status Name" IN ('Accepted', 'Extended', 'Approval in Progress', 'Approved', 'Draft', 'Offer to be made', 'Candidate Record to be updated', 'Integration To WorkDay') THEN "Candidate Identification"."Name" ELSE ' ' END

    The names are populating as desired, however I am getting an additional row for Requisitions that not only have an Offer/Hired Candidate against them, but also have other Candidate submissions which simply aren't named based off the above SQL statement.  Basically the extra row is representing a placeholder for all the other Candidates that have applied but do not meet the conditions of the SQL statement. 

    Any ideas on how to suppress those extra blank rows?  If a Req does not have anyone in the Offer/Hire process, then I would still want the Req. to be displayed.  In that case the Candidate Name should be blank.

    Below is a sample of what's happening, however the attached doc is a littler easier to read.  Thanks in advance for any advice!

    Requisition Number_Position Number Name
    0044262_P0048293 John Smith
    0044262_P0048293  
    0044360_P-0019999 Jane Doe
    0044360_P-0019999  
    0044365_P0048452 Charlie Johnson
    0044366_P0048453 Sally Thomas
    0044367_P0048454 John Doe
    0044372_P0048457 Jeff Johnson
    0044372_P0048457  

    Comment

     

    • Reena Trangri

      Hi,

        Have you applied filter conditions for the respective step/statuses that is expected? The above custom formula will evaluate for all applications according to the rows fetched based on filter criteria and will display blank if the conditions as per formula is not satisfied. It won't suppress or filter out any rows.. So, just checking if this is the issue and the filter conditions on the required step/statuses are applied i.e.

      "Submission CSW Status - Current"."Current Step Name" IN ('Offer', 'Pre-Hire')

      AND

       "Submission CSW Status - Current"."Current Status Name" IN ('Accepted', 'Extended', 'Approval in Progress', 'Approved', 'Draft', 'Offer to be made', 'Candidate Record to be updated', 'Integration To WorkDay') 

       

      Hope this helps.

      Thanks and Regards,

      Reena Trangri

       

    • Jerome Mariadoss

      Hi Kim,

      The Candidates may have more than one offer that why they show twice, include a filter to fetch by latest offer date.

    • Rahul Thomas

      HI Kim,

      Please trythe below formula, note that I have customized the formula to suit my instance make changes as required.

      CASE WHEN "Submission CSW Status - Current"."Current Step Name" IN ('Offer', 'Pre-Hire Validation') AND "Submission CSW Status - Current"."Current Status Name" IN ('Accepted', 'Extended', 'Approval in Progress', 'Approved', 'Draft', 'Offer to be made') THEN "Candidate Identification"."Name" ELSE 'NA' END

       

      and add a filter on the column(check screenshot below).

      Let me know if this helps.

      Thanks and Regards,

      Rahul Thomas

    • Kim Welker

      Thanks for all the feedback as I try to resolve this issue!

      Reena - I don't want to filter the report for step/status because that will reduce the req population to only those who have Candidates in Offer/Hired.  I want to maintain my list of all Open Reqs and Filled Reqs (over the past year)... and then provide Offer/Hired names when applicable (and blank if no Candidates are in Offer/Hired).

      Jerome - my results show that the secondary "blank" Candidate is representing all the other Candidates that don't match the Offer/Hired condition in my SQL statement.  Those Req's that don't have two rows are from the fact that there is only one Candidate... and that candidate happens to be in Offer/Hired.

      Rahul - I like where you are going with this.  I'm not sure how you filter for column T (...but I'm sure I could figure that out), but the issue with this idea is that there will be some Req's that don't have anyone in Offer/Hired and should have only one row with the NA.  By filtering this way, I believe I'd be eliminating the ability to see this Req. entirely )in this scenario).  

      I've been working on this issue for two days now.  Doesn't seem like it would be all that difficult but everything I've thought of just doesn't work.  Any other ideas?  Please keep them coming! :)

      • Christy Fishel

        Try this (I replaced your criteria with my own but you can just change what's in the quotation marks I think)

        MAX(CASE WHEN "Submission CSW Status - Current"."Current Step Name" IN ('Hire') THEN "Candidate Identification"."Name" END)

    • Christy Fishel

      Kim,

      Thank you, thank you, thank you! I have been trying to solve this problem for a long time but couldn't even figure out step 1. I couldn't believe the first topic in the forum was exactly what I was looking for! I have not used SQL statements in my queries before so I am going to try the suggestions above first, then check back in to see other solutions as they arise. Keep me updated on your progress!

    • Kim Welker

      Christie - thanks for the input.  I used your "Max" suggestion... although I modified to include the additional step/status conditions that are required.  (see below). 

      MAX(CASE WHEN "Submission CSW Status - Current"."Current Step Name" IN ('Offer', 'Pre-Hire') AND "Submission CSW Status - Current"."Current Status Name" IN ('Accepted', 'Extended', 'Approval in Progress', 'Approved', 'Draft', 'Offer to be made', 'Candidate Record to be updated', 'Integration To WorkDay') THEN "Candidate Identification"."Name" END)

      This did not solve for the issue as I am still getting two rows for most of the of the requisition population. :( 

    • Christy Fishel
      Sorry to hear that solution didn't work for you - I got the tip by submitting a service request with Oracle Support so if you have access to that you should give it a try!
      
    • Ramesh Nakka

      Hi Kim,

      > Step and Status are specific/belonging to Application and not Requisition.

      > Once you pull Requisition No along with any Candidate Specific field like Candidate Country/ Candidate Name etc.. all Candidate ID's attached to the particular Requisition No. will display in the report.

      > I believe below assumption is correct.Kindly confirm. I believe you might need to create  Parent/Child query or by using Advanced Sequel.

      Candidate ID Requisition No Step Status Candidate Name Comments
      101 10001 Offer Accepted Ramesh Correct
      201 10001 New Candidate Rejected   Incorrect
      102 10002 Offer Extended Ganesh Correct
      103 10003 Pre - Hire Approved Satish Correct
      104 10004 Pre - Hire Draft Suresh Correct
      105 10005 New Candidate Rejected   Correct
      106 10006 First Assessment Candidate Withdrew  

      Correct

       

       

       

       

      Regards,

      Ramesh Nakka

       

    • Kim Welker

      For those that are looking for the answer to this issue... I opened an SR and Oracle suggested a Union report.  This provided the exact results that I needed!  Here are the instructions:

      Report-1:

      1- Should have the Requisition related field and the other Candidate and Submission related field should be cast as NULL
      2- Other filters should remain same(original report filter)
      3- Do not include any submission step status related filter
      MINUS

      Report-2:

      1- Should have the Requisition related field and the other Candidate and Submission related field should be cast as NULL
      2- Specific step and status should be made as EQUAL TO filter.(conditions you are trying to add with candidate name formula)
      3- Other filters should remain same(original report filter)

      UNION

      Report-3:

      1- Should have the Requisition related field with the other Candidate and Submission related field as required in the report.
      2- Specific step and status should be made as EQUAL TO filter.(conditions you are trying to add with candidate name formula)
      3- Other filters should remain same(original report filter)
      4- Add a filter for Candidate > Candidate Identification > Record Status -- is equal to -- AVAILABLE