I need to create a report in CR10, and am having some difficulty with my select.

The table elements in the "Profile" table are linked one or more times to the "SecurityGroup" table - I need to return only the Profile elements that are linked to one and only one SecurityGroup element..

That is..

Profile
ID DocName
1 Test1
2 Test2
3 Test3
4 Test4
5 Test5

SecurityGroup
ProfileLink SecurityGroup
1 0
1 1
2 1
3 0
3 1
4 1
5 0

Where 0,1 are two different security groups - the SecurityGroup table does not have a unique key. As above, I only want to return elements with one specific element (in the example above, I would want to return Profile.ID elements 2 and 4. Can this be done? I have tried using "Select Distinct Records" - didn't work for what I needed, and I have not been able to craft an appropriate select statement.

Any help would be gratefully appreciated.

Cheers!

You can do that in one (or more) of the following methods:

- Use a group for Profile.ID
- Use a running total to sum SecurityGroup.Security Group if criteria to print the record is based on the value of Security Group
- Or use a summary to count the number of records if the criteria to print is based on record count (unsure of which criteria because Profile.ID 2 and 4 is printed but not 5)
- Evaluate the value of the sum of Security Group or record count
- if it is equal to 1 then print else suppress the record