Using CASE to solve Outer Join issues

As many of you know, one of the services that my company offers is Discoverer workshops. What this means is that we send one of our experienced trainers on site and work, sometimes one on one, with the end users on their real system to help them generate the reports that they need. Sometimes I get to go and while I would like to do this more often I just don't have enough time so when the chance does come around I jump at it.

Anyway, one of the most frequent problems that I come across with end user reports is how to effectively create and work with reports when two or more of the folders have outer joins. Such a situation happened in one of the recent workshops that I conducted.

Picture if you will an EUL with 2 folders, folder A and folder B, where folder A is the master being outer joined to B. Thus, if you include something from both folders you will get one row for every item in folder A even if there is nothing in folder B. This is the benefit of the outer join.

Let me get more specific. Let's say that we are working in Human Resources where folder A is Employee Master and folder B is for Employee Leave. Because not all employees have taken leave there is an outer join between the two. Now let's say that in the Leave folder there is an item called Leave Desc that contains descriptions to indicate what kind of leave the employee had taken. With me so far? Here's a screenshot:

Here's some example output:


Ok, so now we want to build a report that allows the end user to exclude one or more Leave Desciptions from the report. To be specific, We want to see all Employees who either have not been sick or have not taken any leave yet. Looking at the report we should be including Carol, Michael and Susie, but not George.

Simply creating the following condition will not work:

Leave Desc <> 'Sick'

Here is that output:

As you can see, George is still included but now appears to have not taken any leave, which isn't true. How about if we also say that Leave Desc IS NOT NULL?

This doesn't give us the right answer either because now both George and Carol have been omitted.

Let's create this calculation which we will call Exclusion:

CASE WHEN "Leave Desc" = 'Sick' THEN 1 ELSE 0 END

We now get this answer, which as you can see has annotated the rows we do not want with 1.

You might think that adding this condition: Exclusion <> 1 would work but it will not. Try it for yourself and you will see what I mean.

You actually need to do add a Boolean OR condition, like this:

Exclusion <> 1
OR
Exclusion <> 1

Problem solved - isn't it? Try removing the Exclusion from the report and see what happens. In Plus you will get the right answer. However in Desktop you will find that your sorting goes wrong. The solution in Desktop is to add a hidden group sort on the Exclusion!

The problem is solved because of the way the CASE statement works which dictates that the function will end as soon as a true condition is encountered. So even there are NULL records these will all get Exclusion of 0 which is the default.