Overview

Some grants require that you show what donors have increased their giving from one year to the next.  In Trail Blazer this requires a special query and format.

In the article at this link we discuss the use of the SQL tab for advanced queries.  You will be using the SQL tab for this report.

The Setup

Trail Blazer’s query interface is very powerful when looking for records where the voter/donor contains specific information.  Example, all records with a city = x, gender = y, and they contributed in 2013. 

But the query to determine increased giving means we need to first total the contribution column by year and then compare the years to see which ones have increased their giving.  As this is somewhat more advanced, we need to use the SQL tab to copy and paste the query.

The Query

( --  sum  of  2012  greater  than…

 (( SELECT  SUM ( TB047927.CL010666 ) FROM  TB010611  AS  TB047927  WHERE ((( TB047927.CL010595 = TB008485.CL005500 ) AND ( TB047927.CL010796 <= 1 )) AND (( TB047927.CL010568 >= '20120101' ) AND ( TB047927.CL010568 < '20130101' ))))>

--  sum  of  2011

 ( SELECT  SUM ( TB047927.CL010666 ) FROM  TB010611  AS  TB047927  WHERE ((( TB047927.CL010595 = TB008485.CL005500 ) AND ( TB047927.CL010796 <= 1 )) AND (( TB047927.CL010568 >= '20110101' ) AND ( TB047927.CL010568 < '20120101' )))))

 )

The only important text for you to understand is the dates in red.

Notice each date is surrounded by a single quote (‘).  The date of 20120101 is read as 2012 01 01 or 2012/01/01 or 1/1/2012 or January 1^st, 2012.

So the query says to total all the contributions for the period GREATER THAN OR EQUAL TO 1/1/2012 (20120101) and LESS THAN 1/1/2013 (20130101).  That will cover all of 2012.  After the query there is a GREATER THAN  (>) symbol.  The next part of the query totals all of the 2011 contributions.

If you run the search above AND you have people who increased their donation from 2011 to 2012, you will get some results.

In my example I have 967 people that meet these criteria:

Once you have edited and run the query, be sure to save it.

Format the Report Output

The columns I want to display for this report need to be added.  I am going to use the ContributionTotalUser01 and ContributionTotalUser02 columns.

Click the Filter link on ContributionTotalUser01:

Enter the date range for the 1^st year you are comparing to:

Click OK.

Change the column title:

Click the Filter link on ContributionTotalUser02:

Enter the date range for the 2^nd year you are comparing to.

Set the title for this column:

Optionally, I like to preset the column widths to 75:

Click OK to view your results.

#4 Related Resources

Knowledge Base - Creating Formats

Knowledge Base - Advanced Queries – the SQL tab

YouTube Channel - Filtered Contribution Columns in Format