Before you begin, keep in mind we do have an Executive Report called Giving Dynamics that can also help you compare giving between two periods of time.  The benefit of building a custom query/report is that you have the option to focus on more specific donations.  For example, if you wanted to see how well a certain Approach performed in 2014 vs 2015, you wouldn't be able to do that with the Giving Dynamics Report.  The Giving Dynamics Report is used for more of a an overall summary of the two periods of time.  It can't be used to look at donations to a specific Fund, Campaign, Approach, User Defined Field, etc.  You can do this with a custom query and report, but you do lose some of the features the Giving Dynamics Report provides such as quickly identifying new donors, recaptured donors, lapsed donors, etc.  This information can still be seen in a custom report, you just have to manually review each donor being listed.

A custom report can allow you to see total amounts per donor for Year To Date and up to five previous years.  You will build a report that has between two and six Received columns.  You will then be able to set an Aggregate on each Received column to make it show totals for a specific year.

First, create a query that pulls donations to a specific Fund (or campaign, approach, etc) for all the years you want to compare (don't go past five years).  In this example we wanted to compare 2013, 2014, and 2015 for our General Fund:
  1. Click the Queries button on the menu bar
  2. Select a category that you want to store this query in (Ex: Base)
  3. Click New Query under the Tasks menu
  4. Name the query All Donations From 1/1/2013 – 12/31/2015 To The General Fund
  5. Set the Starting Query to Base: All Constituents - A
  6. Set the Data Return Type to Journal Entries
  7. Select Match Each Criteria under the Criteria Matching field
  8. Select Commonly Used Fields from the Browse Fields drop down menu
  9. Select Journal Entry Date from the list of links
  10. Type 1/1/2013 into the Start Date and 12/31/2015 into the End date
  11. Select Individual Transaction Received from the list of links
  12. Choose Greater Than Or Equal To .01
  13. Select Funds from the list of links
  14. Check General Fund
  15. Click Save And - Preview
Second, create the report that will show you totals per donor for each year.  Because our query only contains donations to the General Fund, I know that my three Received columns are just totals per year to that exact Fund:
  1. Click the Reports button on the menu bar
  2. Select a category where you'd like to store the report (Ex: System)
  3. Click New Report under the Task Menu
  4. Name the report Total Amounts For Year To Date, One Year Ago, and Two Year Ago
  5. Set Group By to Account under the Group Results section
  6. Set the second drop down, below Group By, to Collapse Groups
  7. Choose Commonly Used Fields from the Browse Fields drop down menu
  8. Choose Name, Received, Received, Received from the list of columns under the Browse Fields drop down
    • Yes, you do want to click Received three times so you'll have three separate Received columns
  9. Click the three dot icon to the left of the first Received column to expand it
  10. Set the Aggregate to Two Year Ago
  11. Highlight the column name “Received” for the first Received column
  12. Type 2013 Total to change the name of the column
  13. Click the three dot icon to the left of the second Received column to expand it
  14. Set the Aggregate to One Year Ago
  15. Highlight the column name “Received” for the second Received column
  16. Type 2014 Total to change the name of the column
  17. Click the three dot icon to the left of the third Received column to expand it
  18. Set the Aggregate to Year To Date
  19. Highlight the column name “Received” for the third Received column
  20. Type 2015 Total to change the name of the column
  21. Arrange the columns into order by clicking on the middle of the field column and dragging up or down
  22. Click Save and Run under the Task Menu
  23. Set the Category drop down menu to the one you built the query in and set the Query drop down menu to All Donations From 1/1/2013 – 12/31/2015 - JE
  24. Set the Report Format at the bottom of the page
  25. Click the Submit button
Note: This article was written in the year 2015.  So the eTapestry report automatically knows that the Year To Date aggregate = 2015, One Year Ago = 2014, and Two Year Ago = 2013.  If I was to run this exact query and report in the year 2016, it wouldn't work correctly.  My query isn't pulling 2016 donations, so if I run this report without making any changes at all, my 2015 column is actually going to display $0.00 because Year To Date would = 2016.  I'd have to adjust the Journal Entry Date in my query if I wanted to run this report again in the year 2016.