Print Page | Close Window

including/excluding parameter values

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13045
Printed Date: 18 May 2024 at 5:25pm


Topic: including/excluding parameter values
Posted By: ediecrystaluser
Subject: including/excluding parameter values
Date Posted: 27 Apr 2011 at 3:23pm
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?


Replies:
Posted By: FrnhtGLI
Date Posted: 28 Apr 2011 at 3:03am
I'm thinking it might not work because technically, if {ProfileAffiliation_View.AffPro_AffCode} equals 'A B C D' then it does not equal {?excludeAffiliations} if {?excludeAffiliations} equals 'D' ('A B C D' <>'D')

What about keeping the first part and having something like:

{ProfileAffiliation_View.AffPro_AffCode} = {?forAffilations})
and
not({?excludeAffiliations} in {ProfileAffiliation_View.AffPro_AffCode})



-------------
|< /\ '][' ( )

Posted By: DBlank
Date Posted: 28 Apr 2011 at 4:05am
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

Posted By: ediecrystaluser
Date Posted: 28 Apr 2011 at 4:11am
Yes, both parameters (inclusion & exclusion) both allow multiple values. I've never done a group selection criteria before. Can you help?

Posted By: ediecrystaluser
Date Posted: 28 Apr 2011 at 4:18am
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.

Posted By: ediecrystaluser
Date Posted: 28 Apr 2011 at 4:19am
Hmmm, I'll have to try the and not... I don't think I tried that combination. Thank you!

Posted By: DBlank
Date Posted: 28 Apr 2011 at 4:20am
the and not will not work because that is evaluating each row not the group.
 

the idea is that once you groupon a field, in your case you would use member ID #, you can select groups of these based on anything criteria that can be determined via an insert summary function re: that group. In the select expert there is a toggle option to switch between group selection or individual selection. The group selection is also boolean like

Minimum({table.date},{table.customerid}) < {?begindate}

Posted By: DBlank
Date Posted: 28 Apr 2011 at 4:26am

how many "codes" do you have and are they static?

Posted By: ediecrystaluser
Date Posted: 28 Apr 2011 at 4:29am
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.

Posted By: DBlank
Date Posted: 28 Apr 2011 at 4:33am
maybe some one else sees a solution allowing for multiple selection in a single param but I think it would require a loop which cannot be used for group selection.
If you had individual params per code with selection options of 'include/ignore' and 'exclude/ignore' you can write formulas to handle it.

Posted By: ediecrystaluser
Date Posted: 28 Apr 2011 at 4:40am
So are you saying if I have only 1 exclude parameter option I can do it with the group selection criteria? Can I still have multiple values on the include parameter?

Posted By: DBlank
Date Posted: 28 Apr 2011 at 4:42am
nope, my idea is one param per value


Print Page | Close Window