Thursday, March 25, 2010

Problem Description
Have you ever encountered a tabular worksheet that when you tell it to Group Sort an item that it doesn't display as a Group Sort even though in the Sort list Discoverer says it is a Group Sort? If so, I have the reason and a solution.

Background
Let's say you have a worksheet that has the following 3 fields:

Department Name

Supervisor Name

Employee Name

And let's say we have two optional parameters:

Department Name

Supervisor Name

This worksheet simply lists who the employees are within a department along with the associated supervisor. The parameters will let you pick departments or supervisors or both at the same time or none due to the optionality of the parameter.

Now, clone this worksheet to another one to add the projects that an employee is working on. You will now get many instances of the employee. The fields in this worksheet are:

Department Name

Supervisor Name

Employee Name

Project Name

In this worksheet, the parameters are, again optional,:

Department Name

Supervisor Name

Employee Name

Now, if you return to the first worksheet and create a Drill Link on the Department Name to the second worksheet you will be asked to provide a value for the 3 parameters in the second worksheet. So far so good.

Next, try and add a Group Sort to the Department Name in the first worksheet. It will not display as a Group Sort?

Why do you think this is?
The answer lies in the fact that you will be passing values from below the Group Sort to parameters in a linked drill.

If the Department Name was Group Sorted you would not be able to click in any row other than the first instance of the department. Discoverer understands that this cannot be right and so does not display Group Sorted items as a Group Sort when lower level items are needed in a drill.

Solution
The solution is to either remove the Group Sort or place the drill to link on one of the lower non-Group Sorted items.

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