Minimum Year Selection at a Group Level 1

[IbeKaba]

I'm working with Crystal IX using an ODBC connection
Here's the source of my headache for the past couple of days:
I'm pulling records from one table (gifts) where each record could potentially have multiple gifts dates. In trying to determine how many new donors we get each year, I'm referencing the date of donor's first donation.

Example
ID GiftYear
111 1999
111 2000
111 2001
222 1988
222 1999
333 2000
333 2001
333 2002

I have a grouping on GiftYear and a second grouping on ID
I have this formular in my group selection:
Minimum ({@GftYear}, {gifts.patron_id}) = {?parYear}

@GftYear = year({gifts.gift_date})
gifts.patron_id = ID in the above example
?parYear = year(s) the user is asked to enter

As it is when I enter one GiftYear (as in 1988) I get only ID 222. Which is exactly as it should be
However when I enter 1999 and 2000 for GiftYear, it gives me ID 111 under 1999 (as it should be) but it also lists it under year 2000 in addition to ID 333, thereby incorrectly indicating two donors gave for the first time in 2000.

A little Extra Info
Eventually I would need to get total number of donors, amount donated, and average donation for each year. I would also need to show this on a graph.

Any help will be greatly appreciated.
Thanks,
ibe

 

[lbass]

The problem you are running into is that the use of a group on year forces your group selection only to apply to that year, since the client ID group is nested within it. Go to database->database expert->your datasource->add command and enter something like:

Select gifts.`ID`, min(gifts.`giftdate`) as mindate
From `gifts` gifts
Group by gifts.`ID`

Then link the command to the gifts table on ID and on giftdate, and for link options, choose "enforce both". This will cause the main report only to return the first giftdate. You can then use mindate in your year formula.

Ideally you would set up the entire query as a command for speed, in which case you would accomplish the above a little differently, setting up the minimum date in the where clause like this:

where gifts.`giftdate` = (select min(A.`giftdate`) from gifts A where A.`ID` = gifts.`ID`)

-LB

 

[IbeKaba]

LB,
Thanks once again for the help. You are the best!
You were right about the speed problem associated with doing it the other way. It was impossibly slow!. I did it the second way you suggested and it's working beautifully.
Merci,
ibe