Omit detail from a group if it also is in another group 2

[glalsop]

Hello all -

Using Crystal 8.5, SQL Server, ODBC.

I am working with a database that tracks employee training. In my event table there is a field (status) that records an employee's current status for a particular course. 'C' (Completed), 'E' (Enrolled), etc.

I have been assigned to report on certain courses according to this status field, i.e. list all employees with a status of 'C', then all employees with a status of 'E', etc, so I am running a report grouped by this field.

The problem is that some employees sign up for the same class more than once. So some employees have a record for course XXX101 with a status of 'E', and a second record for course XXX101 with a status of 'C'.

The specs of the report state that each employee should only show up once, with a status of 'C' having precedence over a status of 'E'. So I need to omit detail records from the 'E' group if there is a cooresponding record in the 'C' group for that employee.

Does Crystal provide direct functionality to do this? Or do I need to load an array on the 'C' group and conditionally suppress records in the 'E' group based on this array. Or perhaps a better idea?

-Gary

 

[synapsevampire]

An array is the long way there...

SQL with a subquery that checks for the status of C seems the most logical to me. But do you have other concerns, such as 2 different C records where one would take precedence over the other?

Anyway, you might use a subreport which returns just the empid, course and status, then use a formula in the sub to apply your business rules, and then return the results to the main report using a shared variable, which simply has the employee information.

-k

 

[glalsop]

Hi k, thank you for your reply. I am afraid I am a bit confused by both of your suggestions and perhaps did not state my problem accurately.

I do not see how I would implement a sub-SELECT (is this what you mean by subquery?) in this situation.

empid course status
1 MGT101 C
1 MGT101 E
2 MGT101 E
3 MGT101 C

Perhaps you could post a sample sub-select which (in this case) would select all records except the second one?

I am not extremely well acquainted with SQL, but is it possible to test for an aggregate function like COUNT(), and if a particular empid has COUNT(status) > 1 for a particular course, then for that empid and course select records where status = 'C'? Do you mean something like this?

Unfortuantley, I left some info out of original post. The status field can contain other values than 'C' and 'E', but these are the only 2 that management is concerned with for this report.

Hope I'm making sense,
-Gary

 

[glalsop]

Forgot to mention -

If an employee has completed the same class twice (has 2 records with status 'C') that is okay. That empid can show up both times in the 'C' group.

-Gary

 

[lbass]

I think you could handle this by first eliminating the group on status and then creating a group on emplID. Next insert a minimum on the status field. Because "C" comes before "E" in the alphabet, each employee who has a "C" record will have a "C" as the minimum, while those without a "C" will have an "E". (This assumes that you are using a record select statement: {table.status} in ["C","E"]. Drag the minimum into the group header next to the employee ID, and then suppress the details.

Next, go to report->topN/group sort expert and choose the minimum of {table.status} as your topN field, and choose descending order. This will give you all employees with "C" status first, followed by all employees with "E" status.

-LB

 

[glalsop]

LB -

Superb, works wonderfully!! Very creative, too. My sincerest thanks.

-Gary

 

[glalsop]

In the interest of completeness (in case someone runs into this problem and this thread in the future), this is how I 'faked' group headers using LB's method.

I created 3 additional Group Header bands above the one containing empID, so I have Group Header #1a - Group Header #1d. In b and c I hard-coded my two 'grouping' options, 'Completed'(GH1b) and 'Enrolled' (GH1c).

In report header I put this formula (suppressed):
@InitiateCounters

whileprintingrecords;

//Counter to store number of employees who's minimum status is 'C'
numbervar c_counter;
//Counter to store number of employees who's minimum status is 'E'
numbervar e_counter;

c_counter := 0;
e_counter := 0;

Then in GH1a I put the following formula (entire band suppressed):
@IncrementCounters

whileprintingrecords;

numbervar c_counter;
numbervar e_counter;

//Increment counters
if Minimum ({event.xstatus}, {@EEFullName}) = "C" then
c_counter := c_counter + 1
else if Minimum ({event.xstatus}, {@EEFullName}) = "E" then
e_counter := e_counter + 1

Finally, I put the following formulas in the suppression formula editors for GH1b and GH1c (the bands containing 'Completed' and 'Enrolled').

GH1b:
whileprintingrecords;

numbervar c_counter;

c_counter <> 1

GH1c:
whileprintingrecords;

numbervar e_counter;

e_counter <> 1

Thanks again LB!!

-Gary

 

[lbass]

Well, I thought your solution for creating the fake group headers was pretty creative, too! (*)

-LB