Crystal Reports - Multipe Operators

Or you could put a group in for client and then a group for year. Then just use a formula to count the number of years in the year group.

For example

Shared NumberVar YearGroupCounter;

YearGroupCounter := YearGroupCounter + 1


Then another formula to display the counter in the client footer

Shared NumberVar YearGroupCounter;

YearGroupCounter


Then one last formula in the client footer (AFTER you display it -- make another footer)  to zero the variable:

Shared NumberVar YearGroupCounter;

YearGroupCounter : = 1

This has two advantages over your approach:

1. You never have to physically name the years. The group counter will always track the years for you. So you will never have to update this report as years pass.
2. You aren't limited in the number of years you can check. They could have 2,4,5, 15 or eve 20 years. This approach will catch them all.


Another way to do it (if you have db access is to write a view that counts the years.

For example:

Select
<clientname field>,
count(datepart(YYYY, <datefield>) [YearsContributing]
from
<yourtable>
group by
<clientname field>

Then just report on client name field.

Hope that helps,
Dave

Sorry, that last formula should read:

Shared NumberVar YearGroupCounter;

YearGroupCounter : = 0 (not 1)

What data are you working with?

Do you have separate records for each year for a person?

mlmcc

Hi Dave and mlmcc,

It's the same report as I was working on before.  I am now trying to take the data from the details section (date IE: 20080101) and determine how many consecutive years someone has donated since 2008.  

My details sections looks like the following:
GF1 Donor ID
GF2 Donation Type
Details: Donor ID Donation Type Date of Donation
GF1
GF2 Donation Type, Donation Type, Donor ID, and hopefully how many consecutive years they donated.

All fields except for GF2 are suppressed.

-byd2k

GS2 Correction:
Donation Type, Donation Type Sum,Donor ID, etc..

It seems you have a separate record for each year.

Are looking to see how many years in a row the person made the same donation type? or just that he made a donation?

mlmcc

It would be how many years in a row irregardless of donation type.  The criteria would start from 2008 to 198x.

-byd2k

That would be how many years in a row he donated something regardless of donation type. (As long as he donated something.)

-byd2k

The problem I see is getting the data into the correct order.  With the DONATTION TYPE group you will see
Type 1  2008
             2006
             2002

Type 2   2005

Type 3   2007
              2006

Obviously we want to see 4.  It can be done but it will require some code.

I'll see what I can come up with.

mlmcc

I was able to perform this in another method, however it's ugly.  I have two fields, one to determine if there was a donation, and the other used as a formula to state whether or not it was consecutive.

This is an example of the one formula:
GF 1 -Donor ID
GF1 - I have an entry for 2008 Donation, 2007 Donation, 2006 Donation, etc...
Goal is to show how many consecutive years someone donated.

if NthLargest (1, {@2008 Donation}, {Table Record}) = "2008"
and
NthLargest (1, {@2007 Donation}, {Table Record}) = "2007"
and
NthLargest (1, {@2006 Donation}, {Table Record}) = "2006"
then "3 Year" else "No Go"

The problem is, I have to rely on Excel to sort out the dates.  Not what I want, however it works so that I see:

2008, 2007, No Go, No Go, etc...

The other formula:
@2008 Donation ='s
if {donation_date} in 20080101 to 20081231 then "2008" else "0"
@2007...
@2006...



-byd2k

Do you want me to pursue another solution?

mlmcc

I'm fine with my solution, unless you know of a more efficient way.  I feel like I was able to resolve it, however it just didn't seem right. :)

Thanks again for your assistance and thanks to as well Dave.

I'll give it a try

Is your data kind of like

Name   DonationType  Year Amount

mlcc

First of all, when you posted the report layout, you didn't label the sections properly.  You had:

GF1
GF2
Details
GF1
GF2

 That should be:

GH1
GH2
Details
GF2
GF1

 Not trying to be picky.  Having the sections mislabeled can only lead to confusion.

 Now, as for your problem.  One way to do this is using an array.  Each element in the array is a flag for a different year (1 is for 2008, 2 is for 2007, etc.).  If you get a date in that year, you set that element in the array.  At the end, you count how many consecutive flags (elements) are set to see how many consecutive years there were.

  Put the following formula in GH1 to initialize the array with each new donor ID.

// init_years
Shared NumberVar Array years;

// I'm starting the array with 20 elements.  You can change that if you like.
Redim years [20];

// Only output an empty string
""


 The "" at the end is so that the formula doesn't actually produce any output on the report.

 Put the following formula in the details section:

// update_years
Shared NumberVar Array years;
Local NumberVar diff;

//  Put the difference between the years in a variable, just so that we
// don't have to keep doing the calculation over and over.
//  I use the current year as the baseline.  You could replace that with
// something else if you like (2008, the year from a parameter, etc.).

diff := Year (CurrentDate) - Year ({SALES_CREDIT_NOTES.CREDIT_DATE}) + 1;

// If the difference exceeds the size of the array, increase it, up to a
// point.  I put a cap on the size (currently 50).  If the number of years
// gets that high (maybe because the date field is invalid), it's just
// ignored.  You can change or remove that cap if you want, but I
// think some kind of limit is a good idea, just in case.

if diff < 50 and diff > UBound (years) then
(
  Redim Preserve years [ diff ];
// I put an empty string here because without it, CR complains that there
// is no else for the if statement
  ""
);

// If the date field has a year greater than the current year (maybe the
// year was mistyped), the difference would be negative, which would
// give us a subscript error, so ignore those entries.

if diff > 0 and diff < 50 then
  years [ diff ] := 1;

// Only output an empty string
""


 Put the following formula in the GF1 section (donor ID footer):

//output_years
Shared NumberVar Array years;
Local NumberVar i;

i := 1;

while i <= UBound (years) and years [ i ] = 1
do
  i := i + 1;

if i - 1 > 0 then
  CStr (i - 1, "#") + " Year Donor"
else
  "No Donation This Year"


 I've tested those formulas here and they seem to work.

 Oh, and FWIW, the formula in your original message:

 If {donation_date} = 20080101 to 20081231 and 20070101 to 20071231 and
  20060101 to 20061231 then "3 Year Donor"

 could never work, because it's trying to compare a single value ({donation_date}) against three different date ranges.  It could never be in all three at the same time.  Conceptually, you want to know if the dates for a donor, as a group, include dates in all three ranges, but the test would be seeing one value at a time.

 James

Obviously in the second formula you'd replace {SALES_CREDIT_NOTES.CREDIT_DATE} with donation date, or whatever your date field is.  I forgot to mention that.

 James