General OBIEE

Get Involved. Join the Conversation.

Topic

    Scott Hillier
    OTBI - Not exists / Outer Join ?
    Topic posted September 19, 2019 by Scott HillierRed Ribbon: 250+ Points, tagged OTBI 
    108 Views, 9 Comments
    Title:
    OTBI - Not exists / Outer Join ?
    Summary:
    Can we write a report where something DOESNT exist?
    Content:

    I want to create a report where we list where a user HASNT entered their timecard records.

    So effectively I'm looking for either an outer join, or to say "give me all the people records where NOT EXISTS Select timecards from last week etc"

    In SQL I could easily use NOT IN, or an outer join etc - is this doable in OTBI ??

     

    Comment

     

    • Stephanie Gott

      Hello,

      I had to do something similar with activities on opportunities. One of the ways I tried to solve was this: 

      Build an analysis that shows all of the people that DO have time cards for last week. Then, build another analysis and use a filter on it that says "ID DOES NOT equal" one that is on the first analysis you built. So, basically, you're creating a filter based off the results of another analysis. 

      Hope this helps. 

      Thanks,

      Stephanie

      • Scott Hillier

        wow, thanks Stephanie, that's a great tip.  I had no idea you could say "does not equal" to a whole other analysis.  I'll give that a go.

        • Wade Wilson

          Adding onto Stephanie's comment. I use this method when I need to troubleshoot something fast and it works well too. You will find that you can filter on any attribute of the analysis, but make sure to use ID (not number) where possible. Otherwise report performance can take a hit - it's a database thing...

    • Wade Wilson

      Hey Scott - one can also use the set operators, specifically difference (you take the full population minus those who exist with something). Here's an Oracle tutorial of union: https://www.youtube.com/watch?v=li_W-jBTSpo&index=22&list=PLKCk3OyNwIzsdkAOBq_dRtUQW_X8k8l53. Difference is very similar.

      • Scott Hillier

        Thanks Wade (cool name if that's really your name btw)

        So I'd take a full list of current people, minus those who HAVE submitted a timecard - cool, I'll look into it, ta.

    • Shakher Sharma

      You can also give a try to LOGICAL SQL if your requirement is complex. It supports NOT EXISTS clause. 

      Refer to blog: https://oraclebizint.wordpress.com/2009/05/07/oracle-bi-ee-101341-understanding-logical-sql-part-1/

      Here is an old reference:

      https://docs.oracle.com/cd/E14571_01/bi.1111/e10544/appasql.htm#BIEUG426

      • Scott Hillier

        Thanks, but I was trying to avoid just writing SQL to do it if I can.  But yeh if I cant do the above I'll give that a try.

    • Maarten van der Burg

      Also having the same issue. In my case I want a report outputting new joiners to our workforce, which is data we need to set up the users in other systems.

      Data is required from various subject areas, and a few are giving me a headache. Example: If HR has not yet received and/or entered the Bank Account from the new joiner, joining the subject areas ​​​Workforce Management - Person Real Time with ​Payroll - Personal Payment Details Real Time will result in the person not showing up in the report at all.

      Your workaround works, but it's time consuming, especially if you have more than 1 join. Would be nice if we could tell OTBI to outer join somehow.