Monday, May 19, 2008

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

Wednesday, May 07, 2008

August 2008 Important Update: Oracle released CP1 for Discoverer 10.1.2.3 on August 7th (link to patches)

In a recent posting of mine (link) I advised you not to upgrade to Discoverer 10.1.2.3 just yet.

I have heard that Oracle is working on a critical patch update for 10.1.2.3 that will incorporate all of the 10.1.2.2 critical patches (CP4, CP5 and CP6) plus fix any specific 10.1.2.3 issues that are unique to that release. The latest information I have is that you should expect to see the 10.1.2.3 patch before the end of the summer. I don't have any better timeline than this I'm afraid. As soon as I hear something more definite I promise I will let you know.

Therefore, for the time being I still recommend not upgrading to 10.1.2.3. You should, however, upgrade to 10.1.2.2 and apply the most recent critical patch (link).

Michael Armstrong-Smith

Michael is co-owner, CIO and principal consultant of Armstrong-Smith Consulting. He is the author of the Oracle Discoverer 10g Handbook. Armstrong-Smith specialize in everything Business Intelligence, including data warehouse and curriculum design. Michael is also the founder and owner of this blog and has over 25 years experience in applications development. He has presented at many conferences and is one of the founder members of Oracle's advisory board for business intelliegence. Appointed to be an Oracle ACE in 2006, Michael has been working in the Business Intelligence arena for over 12 years. Please contact me if you would like help with your BI installtion or are planning or implementing any BI initiative.

Join my LinedIn Group

Rod West

Rod has been using Oracle databases since 1985 and is principal consultant at Cabot Consulting in the United Kingdom. He specializes in Oracle Applications 11i / 12i as well as Oracle Discoverer