Author Topic: Running searches/reports on contributions (Read 2340 times)

lcdweb · July 22, 2008, 01:16:17 pm

Because there's still some interface disconnects between CiviContribute and CiviMember/CiviEvent, it can be difficult to effectively run reports on contributions.

Case in point from one of my clients -- they run contribution reports weekly on things like membership dues. So they want to run the report and get a breakdown of the payments completed or pending for each dues category. As it stands, that's not possible.

IF they recorded the contribution using the "record membership payment" in the new membership or renew membership forms, then a record in civicrm_membership_payment has been created that links the two records (membership and contribution). You can then custom query to get your contributions with membership_type breakdown, or create a custom search form that adds that capability to the interface.

However... three remaining issues:
1) Needing to create a custom search or query to accomplish this is cumbersome. Seems like that type of functionality should be built-in and accessible to end users.
2) Contribution records created directly in CiviContribute (not through CiviMember) don't have that connection between the member record and contrib record. This is most likely if someone uses the "submit credit card" contrib option through the backend interface, as that is currently not integrated into the CiviMember new/renew form (and thus must be done as a separate action).
3) Imported records don't have that membership+contribution connection.

Item 3 is where this came up for me. This client just moved to CiviCRM over the last three months. They need to run reports on all their members to date for the fiscal year. When I imported the contrib records, I included custom fields to capture the membership type for each contribution. So we had half our records with the membership type in the contrib custom field, and the new records created natively in Civi that did not have that contrib custom value because the contrib was recorded using the membership form.

My fix was to add code to the membership and membershiprenewal scripts that inserted the membership type into the corresponding contribution record custom field. So basically the membership type data is replicated for both the membership record and corresponding contribution record.

I think something along these lines should be built-in.

1) Create new/renew membership + record membership payment > insert membership type into contribution record (new field in that table)

2) When creating/editing contribution record directly, either offline or credit card, if "dues fee" contrib type is selected, dropdown field with the membership type options should appear so that the user can manually select the membership type this contrib is applied to.

3) Replicate the same behavior for events -- event title transferred to contrib table and drop down available on contrib record

Looking for feedback/input before creating a Jira issue.

-Brian

Donald Lobo · July 22, 2008, 11:56:22 pm

I dont agree with the above issues, with regard to the three remaining issues:

1. we've improved custom search accessibility in v2.1. I do think getting the community involved in building and publishing custom searches is a good direction for the platform

2. This is fixed in v2.1

3. We should fix import

with regard to your points about "something along these lines", we've tried to avoid polluting contribution records with other components. We've been using join tables instead. I suspect we'll try to adhere to this principle for some time

lobo

Dave Greenberg · July 23, 2008, 03:44:43 pm

Brian - A few more points with regard to upcoming 2.1 functionality...

1. Export from Find Members and Find Participants now includes all related contribution record data by default.
2. 2.1 supports "submit credit card" mode for event registration and membership create and renewal in the "back office"

There's no improvements for the Import problem - so if that's a recurring issue for your clients, would be good for you to write up a specification for how that should work - e.g. how import should deal w/ membership+contribution or event reg+contribution - and file an issue for 2.2.

Perhaps you can re-evaluate how well things are working with the 2.1 improvements and then post back w/ what might still be needed.

thx!
dave

lcdweb · July 24, 2008, 06:16:16 am

To Lobo's points --

1) In general, I completely appreciate that you prefer/need to defer to custom searches on some functions.

2) I'm *very* glad to see that backend event/membership with credit card has been added. I know you guys were not sure if that was going to be able to be included in 2.1 or bumped to 2.2, so I'm excited to see it made the cut. I think that addresses the biggest hole (though it looks like it's not yet available for the member renewal function-- any chance that can be squeezed in?). But there does still exists a hole -- if someone records a contribution directly for an event fee or member dues (not through the event/member registration, but directly through the contribution tab), there's still is no way to associate that with a membership dues type or event through that interface. The scenarios where that would be likely to happen have been reduced with the additional functionality above, but I could definitely come up with some situations where that ability would be needed.

3) That would be great.

Re: polluting contrib records -- I appreciate that. You do it to some extent by populating the contribution source field with membership or event data, though that's a generic field, not component-specific. But since you have join tables connecting contrib-to-event, and contrib-to-member, you don't *need* to have those component-specific fields in the contrib record -- you just need to create interface access to create that join from the contribution record -- which is basically what I'm looking for.

If I create a contribution record for an event, I want the option of "connecting" it to the specific event -- not just the event fee contrib type generically.

On the search side of the equation, I want to be able to do a search not only for all event fees within a date range, but for contributions received for a *specific* event.

I realize there was a custom search created and offered to the community to provide this functionality. My argument is twofold: without the ability to assign a contribution to an event, there's still a potential data gap (the custom search relies on that join table); and that I think that type of functionality is basic enough to organizational operations, it should be included in the default interface, not be relegated to a custom search.

I literally ran into this situation yesterday with a client. They need to do a report on all payments received to date for ABC Training Workshop. With the current setup, that's very difficult to do.

To Dave's point --

1) Just ran the export on 2.1 sandbox -- the key piece it's not exporting is the event or membership type associated with the contribution record via the join table.

As an aside -- I realize the contribution source field generally has the info on the membership type or event. My concern with the contrib source field is that it's editable, and so it becomes (in my mind) an unreliable mechanism for handling these searches. Basically, I don't trust end users not to touch the field and affect the ability to search reliably on it. The join tables are much more reliable.

Re: importing -- I'll work up some specs on that for 2.2.

I really don't mean to drag this issue out. And as I said, the addition of event/membership via credit card does address the biggest functional hole. I'm coming from the perspective of clients that need to run financial reports and reliably track down the monies received or due for each event or membership category. As it stands, that's not easy to do.

Thanks for listening!
Brian

Dave Greenberg · July 24, 2008, 07:08:41 am

Quote

On the search side of the equation, I want to be able to do a search not only for all event fees within a date range, but for contributions received for a *specific* event.
1) Just ran the export on 2.1 sandbox -- the key piece it's not exporting is the event or membership type associated with the contribution record via the join table.

Brian - The intended functionality is that you go to Find Participants (for the case above) and search by Event Title - then do the export. You will get all participants for the event with all contribution record data including in the rows. The same process for Memberships by membership type, date range, etc. If that's not what you saw on 2.1 sandbox - let me know since it may be a bug. If that doesn't solve this problem - let's discuss further.

I'll look at the renewal piece...

Dave Greenberg · July 25, 2008, 04:38:30 pm

Membership renewal via credit card has been implemented for 2.1 and seems to be working nicely. Thanks for the heads up on this missing piece!

CiviCRM Community Forums (archive)

News:

Author Topic: Running searches/reports on contributions (Read 2340 times)

lcdweb

Running searches/reports on contributions

Donald Lobo

Re: Running searches/reports on contributions

Dave Greenberg

Re: Running searches/reports on contributions

lcdweb

Re: Running searches/reports on contributions

Dave Greenberg

Re: Running searches/reports on contributions

Dave Greenberg

Re: Running searches/reports on contributions