Filter using sub report in BO SAP Web Intelligence

Question

I need to report on employees who have not been absent during the last year. I can do this easily using the following simplified SQL

    SELECT  EMPLOYEE_NUMBER
    FROM    Employee
    WHERE   Employee.EMPLOYEE_NUMBER NOT IN (
        SELECT  EMPLOYEE_NUMBER
        FROM    EmployeeAbsence
        WHERE   EmployeeAbsence.ABSENCE_END_DATE >= ADD_MONTHS(SYSDATE, -12)
    )

I have been investigating how I could do this using sub queries in BO Web Intelligence, but have had no success.

Are you not getting the results you want? Or are you unsure where to start? — Isaac, Dec 10 '19 at 14:23
I have done this now by creating two queries; one that returns employees who have had an absence in the last year, and another that returns all employees. I join the employee numbers from the two queries in the final report with the All Employees employee number begin the main. Filtering on where the absence employee number is missing returns the results I need. — NiMuSi, Dec 12 '19 at 14:07

score 0 · Accepted Answer · answered Dec 12 '19 at 15:25

You should be able to do this with a subquery within the Query Panel. Here is an example from the eFashion universe to show all stores that do not have the Outerwear Line. This approach only works when the objects you want to return and those involved in your exclusion criteria are in the same universe.

If they are not in the same universe you will need two queries. First create a query of what you want to exclude. For my example that is Stores that have the Outerwear Line.

Then create a query where the Store Name is not in the list of Store Names that do have the Outerwear Line.

If you would like to see the creation of a subquery in action watch this short video.

Filter using sub report in BO SAP Web Intelligence

1 Answers1