Overview

This article will teach you how to create a year-end contribution report using Trail Blazer and Microsoft Excel.  Trail Blazer is unable to print multiple lines of contributions within its word processor.   Exporting the contribution detail to Excel will give you the option of generating separate pages of contribution detail for each donor.  This article will first teach you how to query for all donations in a calendar year, second it will teach you to export the list, open in Excel and finish generating the report using subtotals.

NOTE:  MS Excel limits the amount of page breaks you can insert in a spreadsheet to 1,026.

TIP: 

See this article for advanced queries.  If you would like to limit the output results to just those with MULTIPLE contributions for the year (month, qtr, etc), use this query and change the date ranges:

((((TB010611.CL010568>=

-- this is the starting date (do not delete the apostrophe's):

'01/01/2012'

)) AND ((TB010611.CL010568<

-- this is one day AFTER the ending date (do not delete the apostrophe's):

'01/01/2013'

))) AND (( SELECT  COUNT ( * ) FROM  TB010611  AS  TB047927  WHERE  TB047927.CL010595 = TB008485.CL005500  AND ((( TB047927.CL010568 >=

-- the SAME starting date is repeated here (do not delete the apostrophe's):

'01/01/2012'

 ) AND ( TB047927.CL010568 <

-- the same ending date is repeated here (do not delete the apostrophe's):

'01/01/2013' ))))> 1 ))

Steps to Query for All Contributions in a Calendar Year

Under the Application Menu follow Voter/Donor > Contributions/Pledges > Contributions – this will open the contribution search window.

Enter the calendar date that you want to query by and click the [Search] button.  In this example I selected the date range 1/1/2010-12/31/2010 and had a contribution record count of 2282.

Click the [Export] button from the List tool strip.

Select the location where you would like to save the file, give the file a name and save it as a (*csv.) file type, which is what Trail Blazer selects by default.

Click the [Save] button to finish the export, Trail Blazer will prompt you with a notification on how many rows were exported; click [OK] to finish.  This is displayed in two images below.

Steps to Create Contribution Year-end Report in MS Excel

Navigate to the location where you saved the file and double click to open it in MS Excel.  In this example I saved the file to my C:\Temp Folder and called it “TBZ Contribution Year-End Report.csv”.

Once the file is open in excel you will need to widen the columns and begin removing columns you don’t want included in the report.  Below is a list of hot keys you can use to perform these steps:

Ctrl + A = Highlight the entire spreadsheet.

Alt + O, C, A = Auto-widen columns (if you first highlight the spreadsheet it will widen all columns).

Ctrl + Space = Highlight entire single column.

Alt + E, D [C] = Remove entire column.

Ctrl + P = Print.

Ctrl + Shift + 1 = Sets the highlighted cells to 2 decimal places.

For this report I kept the Amount, First Name, Last Name, Date and the TBZ ID column.  I removed every other column from the spreadsheet (i.e. street address, batch code etc.)  I renamed Donor ID to “TBZ ID”, Date to “Contribution Date”, and Amount to “Single Contribution Amount” – I also made the column headers bold to make the spreadsheet easier to read.

NOTE:  The column head that was changed to “TBZ” is the unique ID associated with each record in your Trail Blazer database.

Next you will sort the list on the fields that you want to subtotal.  Navigate to the Data tab and click the [Sort] button.

From the sort window click the [Add Level] button and choose to sort by Last Name and First Name leave the [Sort On] as its default which is Values, and the [Order] as it’s default which is A to Z.

Click the [OK] button to execute the sort on your spreadsheet.

Now that the list is sorted by Last Name and First Name you will navigate to the Data tab and click the [Subtotal] button.

For the drop down “At each change in:” select TBZ ID.  For the drop down “Use function:” select Sum.  Check Single Contribution Amount box for “Add subtotal to:”.  Check all three boxes on the bottom to Replace current subtotals, Page break between groups, and Summary below data.  These instructions are displayed in an image below.

Click [OK] to finish generating your report.  The results of the report are displayed in an image below.  Be patient, MS Excel may take some time to compute the final report (if your list of contributions has to many page breaks excel may not allow you to execute this process due to lack of memory).

Below is an image this displays how the spreadsheet will look like after you run the group process.

Steps to Print the Report

How the final report will look is ultimately up to you and how aesthetically pleasing you want it to be, for this example I performed some minor formatting and select some (optional) print options.

First navigate to the Page Layout tab and click the [Print Titles] button.

From the Sheet tab select the Rows to repeat at top and then click the top row of your spreadsheet so that the column headers will appear on each page of the report.

Click [OK].

You can put the columns in any order you see fit, in this example I did TBZ ID, Single Contribution Amount, Contribution Date, First Name, I also changed the single contribution amount format into a number format.  This is displayed in an image below.

Use the hot key Ctrl + P to print or select File > Print.

Click the Page Setup link.

Choose the (Landscape) radio button, you may need to make some scaling adjustments.  In this example %95 was used to make all of the columns fit appropriately.

Click [OK].

 If you need to you can also make some adjustments to the margins as displayed in the image below.

Make any further adjustments to your printer and click [Print] to finish the process.

Double check your report before printing.

TIP:  Save a digital copy for your records as well as the physical copy, change the file type from a (*csv.) to an (.xlsx) in order to keep the same format throughout.

Resources

Dummies.com – “Subtotals”

YouTube Channel – Trail Blazer Software – “Contribution Year-End Report” (Not yet created.)