Edit this page :: Discussion

Searching and Reporting

Databases are useful to store data, but the real power of any database, especially CiviCRM, is getting data out! This section will review how to build contribution reports. You should already be familiar with CiviCRM's contact search features such as Find Contacts, Advanced Search, and Search Builder, as well as CiviCRM profiles. 

CiviContribute Summary

The CiviContribute main page (displayed below) gives a summary of contributions including contributions received in the current month to date, year to date, and cumulatively since inception.  Listed below are the 10 most recent contributions:

summary

You can also view bar or pie charts to compare contribution totals across months of a given year and/or across years.

chart

Contact contributions summary

Every contact in CiviCRM has a contributions tab which shows contributions they have made to your organisation.  Use this tab to view, edit, or delete contributions.

cont_tab

Contribution reports using Find Contributions

Find contributions is CiviCRM's interface for reporting on contributions made to your organisation.

resized_500x253_findcon_1

You can search based on a number of criteria, specifying date range, amount, contribution status etc.

Contributions matching all specified criteria are returned, so the more criteria you enter, the narrower the search will be.  For example, searching for the contribution type 'donation' and the date range 'January 1st to May 1st' will return contributions that meet both criteria.

The results screen from a search displays the the total amount for that search criteria, the number of contributions, and the average amount per contribution :

contribution_totals

You also have options under the "more actions" menu once you select all or a subset of records :

resized_400x300_more_action_con_1

The "more actions" menu allows you to:

  • Export the list of contributions. Note : The find contributions search is contribution-centric.  If one contact has multiple contributions that fit the search criteria, that contact will appear as two rows when you export your spreadsheet. If you want to do searches that return one result per contact, use the contact advanced search.
  • Delete contributions.  Rather than deleting contributions, consider editing them and updating their status to cancelled, which gives you a better 'audit trail'.
  • Print receipts creates a PDF file of all the receipts in the search which you can print and mail to your donors.
  • Batch update the list of contributions via a profile.  This is useful if you want to update a large number of contributions' thank-you date at once, for example. Note You need to create the profile you want to use before you perform the search and batch update.

Advanced Search

There are many scenarios where searching solely by contribution criteria is insufficient. You may, for example, want to search all of your donors that live in a certain state and have a certain tag assigned to their record.  This type of search is accomplished within CiviCRM's advanced search.

The advanced contribution search engine offers essentially the same criteria as Find Contributions. The key differences between Advanced Search and Find Contributions are:

  • You have many more options under "more actions" dropdown, allowing you to perform advanced functions on your search results.
  • It is contact-centric, which means each record in the result represents one contact instead of one contribution.

The disadvantages of using an advanced search include:

  • Since advanced search is contact-centric, it will not provide you with a total of the contribution amounts.
  • Advanced search won't export contribution amounts, for the same reason.

Search Builder

For organizations needing complex donor reports and queries, Find Contributions and Advanced Search may not be enough. Luckily, there are still more powerful options. Search Builder is a tool that builds a report from a variety of criteria (pictured below). The search can be built so that records must meet all the criteria you enter or any criteria you enter.

searchbuilder

Advantages of Search Builder include:

  • Searches can include records that do not match a certain criterion as well as those that do. In other words, if you create a custom field for contributions called "Contribution Tactic," you can search all contributions that don't match a particular tactic.
  • You can search across data types (contribution records, membership records, contact records, etc.).
  • The search results columns display the criteria you enter for a useful visual summary.
  • You still have all of the useful "more actions" options on your search results screen.

Disadvantages include:

  • Some of the search operators ( <, >, =, NULL, NOT NULL, etc.) and the general interface are intimidating to some non-technically minded people, which presents a moderate learning curve.
  • There are some fields in the database that can not be searched.

Custom Searches

If Find Contributions, Advanced Search, and Search Builder do not provide you with the report you need, the final reporting option is to build a custom search. This is only for those with advanced technical skills, including strong knowledge of MySQL and PHP as well as knowledge of CiviCRM's internal database structure. If you have that knowledge you can build a query on any criteria and control how it is displayed in the interface to allow other users of the database the ability to periodically run the report.

With custom searches you control:

  • Criteria that is always a part of the query
  • Criteria you can input from the interface before you run the query
  • The columns displayed on the search results screen
  • The fields that get exported to a spreadsheet

An example where you may need to create a custom search is to aggregate the total amount of contributions coming from a household record and all individuals that are related to it. 

CiviCRM includes some pre-built custom searches specifically related to contributions, including:

  • Contributions made in Year X and not Year Y: This report is commonly called SYBUNTY (Some Year But Unfortunately Not This Year) or LYBUNTY (Last Year But Unfortunately Not This Year). 
  • Contribution Aggregate: As mentioned earlier, Find Contributions is contribution-centric while advanced search is contact-centric. This custom search bridges the gap and provides the best of both worlds. It finds all donors and aggregates all of their contributions for each record.
  • Event Aggregate: Allows you to find the total number of contributions raised through events. You can search by event name, event type, or date range.

To read documentation on how to build a custom search, visit: http://wiki.civicrm.org/confluence/display/CRMDOC/Custom+Search+Components.