Suppress record only in one group (crystal XI)

Question

My report is a list of names grouped into constituencies. There can be one name to many constituencies. Each name should show up in ALL groups where it has that constituency. (In the example below Mary has both A and B so she should be in both of those groups). The last group should be on all the remainder people who don't fall into any of the other groups. (Jack has constituency C that I am not interested in grouping on.)

Example (as I want it to be):

Constituency A

• John J Smith

• Mary Elmore

Constituency B

• Brad Jones
• Mary Elmore

None of the above Constituency

• Jack T Ripley

I have a left join on constituency to name and have grouped on the constituency field. In the group options I have it set to "put all others together, with name: None of the above Constituency"

It is showing the names in each group as I want. The problem is in the last, catch-all group. It's shows all the people who have a blank (not relevant constituency) or null constituency. I want it to only show those that are not showing up in any of the other groups. How do I suppress those only in the last group?

Example (as it is currently): Constituency A

• John J Smith
• Mary Elmore

Constituency B

• Brad Jones
• Mary Elmore

None of the above Constituency

• Jack T Ripley
• Brad Jones

(Brad has constituency B and C but I don't want him to show in the last group because he already shows up in B.)

I am exporting out of a Raiser's Edge database and do not have a lot of flexibility with my queries.

Thank you for any help or advice you can provide.

Answer 1

First idea off the top of my head: Make a custom SQL statement within Crystal that returns a new field, which is just the combined collection of unique Constituencies each person belongs to. You can link this statement to the rest of your tables and treat it like just another table.

So for Mary the field would be something like Constituency A, Constituency B, John's field would be Constituency A, and Jack's field would be... empty.

Then rather than display that final group, make a subreport to display every user where the new field is blank. (You can make the subreport look exactly like it's just another group, for bonus points.