Note: In this example we are looking at the past 25 years, 1988 through 2012.
 

  1. Insert a group on the CnBio_ID field or any other field that contains unique values for each constituent.
  2. Create 25 formulas to determine whether or not the constituent gave a gift in that year.
    Example formula for 2011: if {Gf_Date} in Date(2011, 01, 01) to Date(2011, 12, 31) then 1 else 0
  3. Place these formulas in the Details section
  4. Create 25 MAXIMUM summary fields on these formulas to determine if 1 occurred for the constituents (grouped on Group #1 so that they appear in Group Footer #1).
  5. Create a formula to determine the maximum number of consecutive years where a 1 appeared for the constituent:

    WhilePrintingRecords;
    NumberVar Array MaxYears;
    redim MaxYears[25];
    NumberVar i := 1;
    NumberVar consec := 0;
    NumberVar max := 0;

    MaxYears[1] := Maximum ({@Formula-1988}, {CnBio_ID});
    MaxYears[2] := Maximum ({@Formula-1989}, {CnBio_ID});
    MaxYears[3] := Maximum ({@Formula-1990}, {CnBio_ID});
    MaxYears[4] := Maximum ({@Formula-1991}, {CnBio_ID});
    MaxYears[5] := Maximum ({@Formula-1992}, {CnBio_ID});
    MaxYears[6] := Maximum ({@Formula-1993}, {CnBio_ID});
    MaxYears[7] := Maximum ({@Formula-1994}, {CnBio_ID});
    MaxYears[8] := Maximum ({@Formula-1995}, {CnBio_ID});
    MaxYears[9] := Maximum ({@Formula-1996}, {CnBio_ID});
    MaxYears[10] := Maximum ({@Formula-1997}, {CnBio_ID});
    MaxYears[11] := Maximum ({@Formula-1998}, {CnBio_ID});
    MaxYears[12] := Maximum ({@Formula-1999}, {CnBio_ID});
    MaxYears[13] := Maximum ({@Formula-2000}, {CnBio_ID});
    MaxYears[14] := Maximum ({@Formula-2001}, {CnBio_ID});
    MaxYears[15] := Maximum ({@Formula-2002}, {CnBio_ID});
    MaxYears[16] := Maximum ({@Formula-2003}, {CnBio_ID});
    MaxYears[17] := Maximum ({@Formula-2004}, {CnBio_ID});
    MaxYears[18] := Maximum ({@Formula-2005}, {CnBio_ID});
    MaxYears[19] := Maximum ({@Formula-2006}, {CnBio_ID});
    MaxYears[20] := Maximum ({@Formula-2007}, {CnBio_ID});
    MaxYears[21] := Maximum ({@Formula-2008}, {CnBio_ID});
    MaxYears[22] := Maximum ({@Formula-2009}, {CnBio_ID});
    MaxYears[23] := Maximum ({@Formula-2010}, {CnBio_ID});
    MaxYears[24] := Maximum ({@Formula-2011}, {CnBio_ID});
    MaxYears[25] := Maximum ({@Formula-2012}, {CnBio_ID});

    While i < 25  Do
    (
        While MaxYears[i] = 0 and i < 25 Do
        (i := i + 1);

        While MaxYears[i] = 1 and i < 25 Do
        (consec := consec + 1;
        i := i + 1;
        if consec > max then max := consec);

        consec := 0;
    );

    max;

  6. Place this formula in Group Footer #1 to display the results.