CiviCRM Community Forums (archive)

In addition to membership, our organization has the concept of sponsors - people who contribute additional money in support of the organization. Since there isn't a way to automate the "please give us more money" notice like there is with membership, I'm looking for ideas to build a report or even a smart group that will help us contact these individuals.

The problem is, I can't come up with a way to build a query that can find these people in a single select statement, and I don't see an obvious way to build a custom report that calls select multiple times. Am I making this harder than I need to?

Basically what I need is a query along the lines of:

• Find the latest contribution and contact record for all contributors if their latest contribution is more than 365 days in the past

The biggest problem I have had so far is figuring out how to get just the most recent contribution from a donor.  Am I just missing something? I can get all contributions but not just the most recent. Then I need a relative date comparison -- something I can do easily in SQL but not so much through the search interface.

Does anyone have any ideas how I can do this without just writing a script to extract the data directly from the database?

Thanks!

Ok, how did I not see that report before?

Thanks for pointing that out! It isn't quite what I need, but it's really close. It should at least be a place for me to start with building a custom report.

I see two problems with it out of the box:

• There is no way to filter by contribution type. I need to be able to filter by sponsor contribution, and don't want to see all of the membership and event registrations.  This should be easy to do in a custom report.
  
• It is based on calendar year and I need one year from the current date. If someone just contributed in Dec I don't want to be nagging them for another contribution until this Dec.

I don't suppose anyone has built a custom report for this they would like to share?

Just out of curiosity, any idea why my SYBUNT and LYBUNT reports are defaulting to 2009 rather than the current year?

Thanks again for the pointer to the LYBUNT report, that put me right on the path I needed!

I was able to customize a SYBUNT report to give me a report of past sponsorship contributions and even added membership status to it. The only drawback is that I had to hard-code the contribution type, I don't know how to get the type_id from within the 'clause' statement for receive_date, and I'm not even sure if the value is available at the time the SQL is executed.

I'm attaching the code to this post, if anyone has any comments or suggestions I would love to hear them!