Thursday, March 25, 2010

Group Sort will not sort

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.

No comments:

Followers