OTBI - Not exists / Outer Join ?

Scott Hillier-132647

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 ??

 

slgott

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

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.

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-132647

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.

Scott Hillier-132647

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.

Scott Hillier-132647

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.

Wade Wilson

Thanks! It's been mine since birth

That's correct. We do the same thing for goals.

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...

MaartenDIF

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.