How do I query and report on all active recurring gift schedules (auto and manual)?

If you have recurring donors, at times you may need to query and report on anyone with an active recurring gift schedule.  This will include both Auto-Process and Manual schedules.
First, create a query to find all recurring gift schedules:

1. Click Queries
2. Click the Base category
3. Click New Query under the tasks menu
4. Name the query All Recurring Gift Schedules
5. For Starting Query plug in Category: Base and Query: All Constituents
6. Set the Data Return Type to Journal Entries
7. Under Criteria Matching select Match Each Criteria
8. Under Criteria select Journal Entry Type
9. Choose Recurring Gift Schedule
10. Click Save and View Queries

Next, create a query to find all recurring gift schedules with a stop date:

1. Click Queries
2. Click the Base category
3. Click New Query under the tasks menu
4. Name the query All Stopped Recurring Gift Schedules
5. For Starting Query plug in Category: Base and Query: All Constituents
6. Set the Data Return Type to Journal Entries
7. Under Criteria Matching select Match Each Criteria
8. Under Criteria select Dates from the drop down
9. Click Recurring Gift Schedule Stop Date
10. Enter something like 1/1/1990-5/18/2016
11. Click Save and View Queries

Then, create a compound query to subtract out the stopped schedules. This leaves you with all of your active recurring gift schedules:

1. Click New Compound query
2. Name the query Active Recurring Gift Schedules
3. Set data return type to journal entries
4. Choose subtract query 2 from query 1
5. For Query 1 select Category: Base and Query: All Recurring Gift Schedules
6. For Query 2 select Category: Base and Query: All Stopped Recurring Gift Schedules
7. Click Save and Run Report

Lastly, create a report to include the data you want from each active recurring gift schedule:

1. Select a Category to store this report in (Ex: System)
2. Click New Report under Tasks
3. Name your report Active Recurring Gift Schedule Information
4. Under Report Columns - Commonly Used Fields - Click Account Name and any contact info you would like to include
5. Under Report Columns - Journal fields - Click Installment Amount, Frequency, and any other information you would like to include
6. Click Save and Run
7. Select Category: Base and Query: Active Recurring Gift Schedules
8. Scroll down, select your Report Format and click Submit

Environment

 eTapestry

Was this article helpful?