How to Display only certain groups of records

[SuperTime]

I have a main report that has been grouped on Department and then further on there is another group based on the master table and that group has a subreport employees.
The employee subreport is simple in the details sections has all the employees that belong to same department.
Back on the main report below this group there is one more group projects. And that group displays the different projects going on under each department and the employees are thus grouped based on the projects they are working on.
Now an employee could be working on 2 different projects in the same department. Hence I have data that looks as follows.


DeptABC
Tim
Harry
ProjectA Description: blah

Smith
Harry
ProjectB Description: blah

David
Robert
ProjectC Description: blah

DeptXYZ
Dothy
Nancy
ProjectD Description: blah

Laura
Dothy
ProjectE Description: blah

Kim
Jeniffer
ProjectF Description: blah


Now I want to filter the data furhter to display records in each department with the same people working on it.

So I want the following results

DeptABC
Tim
Harry
ProjectA Description: blah

Smith
Harry
ProjectB Description: blah

DeptXYZ
Dothy
Nancy
ProjectD Description: blah

Laura
Dothy
ProjectE Description: blah


Beacuse in deptabc harry is working on 2 projects
and in deptxyz dothy is working on 2 projects.

I am using crystal reports 8.5 pro.
and oracle backend


please advice.

I appreciate any help.
Thanks.

 

[Madawc]

Group your data by employee, with project linked. Suppress all employees who have less than two projects - you can do this by a summary distinct-count within the group, and Group Selection to suppress them.

Madawc Williams (East Anglia, UK)

 

[SuperTime]

I am sorry I think I didn't frame my query clearly.

There focus is not on the number or projects, but on the name of the people working on it.

I want to select records that have the same people working on it.

So on the data give above . I want to select the records becuase in deptabc there is Harry who is a common person working on different projects.



DeptABC
Tim
Harry
ProjectA Description: blah

Smith
Harry
ProjectB Description: blah

David
Robert
ProjectC Description: blah

David
Tom
ProjectC Description: blah

Brian
Jimmy
ProjectC Description: blah

So if I have the above mentioned senario then the records that need to be selected are:

DeptABC
Tim
Harry
ProjectA Description: blah

Smith
Harry
ProjectB Description: blah

David
Robert
ProjectC Description: blah

David
Tom
ProjectC Description: blah



Becaues out of the 4 records Harry is common in first 2 and David is common also but in the next two records.


Please adive

Thanks.

 

[Madawc]

Try my suggestion, unless someone offers a better one. I don't think you'll get Crystal to produce the exact output you showed, because that would require a single detail line in two different groups.

Madawc Williams (East Anglia, UK)

 

[lbass]

One way you might do this is by creating a SQL expression
{%countbyname}:

(select count(AKA.`Name`) from Table AKA where
AKA.`Dept` = Table.`Dept`)

This assumes that the Name and Department field are from the same table. If not, you should supply the table and field names. Replace "Name" and "Dept" with your actual field names, and replace "Table" with your table name. Leave "AKA" as is, since it is an alias table name.

Place {%countbyname} in the details section. Then go to the section expert->details AND GF (Project)->suppress->x+2 and enter:

sum({%countbyname},{table.project}) =
count({table.name},{table.project})

This should suppress all groups where none of the people have participated in other projects within the department.

-LB