A Big Question

[matthoward]

Well I have a huge problem and no matter how I try to resolve it, it just wont solve itself.
Outline of the problem
How the report looks
Account Number
123456
Name etc etc etc etc Loaded Payment Method
Payment type 1 3time
Payment type 2 2time
Payment type 4 1time

This report is grouped by
Group 1 Account Number
Group 2 Transaction Type

I have a count on transaction type.
What I am trying to achieve is to eliminate all people who have used payment type of 1 more than once. This means I would not get any details what so ever for that person and it would display the next persons details. I hope this makes sence,

Cheers Matt

 

[chelseatech]

Create a formula {@TypeOnes} ...
If {table.paymentType}=1 then 1 else 0

Do a sum of the formula for each Account/Person

Do a GROUP SELECTION FORMULA of
Sum({@TypeOnes},{table.account}) <=1

This will exclude the entire group where more than one Type=1 transaction.

To get the correct totals in the final report, you will need to use either Running Total Fields or Formulas with variables, but it should work well.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[matthoward]

Thank you, It half worked. I am using crystal reports 6. My problems seems to be after putting in the formula for grouping it just does not want to group that way. Am I missing a step because it just wont let me add a group by formula.

Help As Always is Appreciated,

Thanks Matt

 

[synapsevampire]

I think that you misunderstood Chelsea's suggestion:

Create a formula for the details section containing:

If {table.paymentType}=1 then 1 else 0

Create a group select criteria:

Report->Edit Selection Formula->Group

Sum({@TypeOnes},{table.account}) <=1

(CR 6 may not support this because it's very old, upgrade if you can, you're about 5 significant releases behind.)

I would suggest that you do this on the database side in a View or SP.

-k