I have a view that contains the unique identifier of the member and the groups (affiliations) they belong to. If they belong to more than one group they are in the view multiple times. I have a parameter set in my crystal report asking what groups I would like to see. I also have an exclude parameter and was hoping to exclude data where the same record has both the include and exclude groups.

For example, I want to pull everyone that has group A, B & C. But I also want to exclude anyone that also has group D.

My select expert crystal formula looks like this:

{ProfileAffiliation_View.AffPro_AffCode} = {?forAffilations}) and
{ProfileAffiliation_View.AffPro_AffCode} <> {?excludeAffiliations}

However, it doesn't work. Are you able to include and exclude data using the same field in a report?

Pretty sure you will have to do group selection criteria here. Might be very tricky if you have one inclusion param wich allows for multiple selectiuons and one exclsion criteria with multiple seelctions

I'm worried I need to re-write the whole report using a command...where basically the SQL goes as follows:

Select ID from Affiliations where ID in (select Id from affiliations where group=includeparameter) and ID not in (select Id from affiliations where group=excludeparameter)

The only issue here is are you able to include multiple values in that parameter and SQL understand it? I'm new to Crystal 2008 and in Crystal 9 you couldn't have multiple values in a parameter that was used in a SQL command.

Thanks so much for your input. I greatly appreciate it.

the and not will not work because that is evaluating each row not the group.

I have the parameters pulling all possible values dynamically. For now, the number of includes is up to 12 different codes and exclude codes is only 1 at the moment. I did want to allow multiple values on the exclude in case that is an issue moving forward...but if it isn't possible then we'll just have to deal with that.