So I was recently tasked with creating a report of our donors. The parameters were: They must have given at least one gift this past fiscal year. And they must have given a minimum of $500 a year on five different fiscal years.
They don't have to be consecutive.
So I created a report that first groups based on Donor ID. Then groups on Gift Date (gifts are not guaranteed to have been entered into database in chronological order). Finally I group on Gift ID because they do gift splits which causes gifts to repeat.
I came up with the following formula. I should note our fiscal year starts July 1st. So I adjust for that.
whileprintingrecords;
global numbervar lastYear;
global numbervar giftCummulative;
global numbervar validYears;
global booleanvar GaveThisYear;
numbervar m = Month({AD_V_GIFT_HIST.GIFT_DATE});
numbervar y = Year({AD_V_GIFT_HIST.GIFT_DATE});
datevar d;
if m < 7 then
y:= y-1;
d = date(y,7,1);
if d in {?CurrentFiscalYearRange} then
GaveThisYear := true;
if y <> lastYear then(
lastYear:= y;
if {@GiftCumMatches} then
validYears:= validYears + 1;
giftCummulative:= {AD_V_GIFT_HIST.GIFT_AMT} ;
)
else
giftCummulative:=giftCummulative + {AD_V_GIFT_HIST.GIFT_AMT};
This formula runs in the group header of the GiftID group. So it only runs once per gift.
I have a reset formula that sets everything to zero or false and that runs every time the Donor ID changes.
So here's my problem.
At precisely record 107706 it errors out saying I need to define a year between 1 and 9999. On the line d=date(y-1,7,1).
When the formula editor opens up and shows the values of the different variables it shows a date of "2010-10-20 00:00:00" for Gift_Date. But Y and M both return zero.
I have no idea what is wrong with the code. Or why Year() would suddenly stop returning a value even though there clearly is one available.
Any suggestions?
Edited by levifiction - 05 Jul 2015 at 5:59am