consecutive years

[princessinthetower]

Oracle 9, Crystal 9
I need to count the number of consecutive years (years is a text field) for donations per person with 2005 as the anchor date so.

2005 $100
2004 $50
2003 $50
2002 $0
then the count = 3 consecutive years

2005 $100
2004 $50
2003 $0
2002 $50
then the count = 2 consecutive years

2005 $0
2004 $100
2003 $50
2002 $50
then the count = 0 consecutive years

Thanks in advance for any assistance.

 

[synapsevampire]

OK, so if you have 2 years of donations, then no donation, then 2 more years is that 4, or?

Anyway, here's an approach:

Group the report by the person.

Report header formula:
whileprintingrecords;
Numbervar Counter:= 0;
Booleanvar GotZero:=False;
Booleanvar StopCnt:=false

Details formula:
whileprintingrecords;
numbervar CurrCount;
If {table.amt} > 0
and
previous({table.person}) = {table.person} then
and
StopCnt := False
counter:=counter+1;
If Counter > 0
and
{table.amt} = 0 then
STopCnt := True

Group footer formula
whileprintingrecords;
Numbervar Counter

Thre above will count consecutive ones, then drop out after it finds a zero donation, which is more difficult than if you want all years, in that case there's no need for formulas, just use a Eunning Total which resets at the person and do a count, with a formula in the Evaluate->Use a Formula of:

{table.amt} > 0

Hopefuly you'll figure it out from here.

-k