May 30, 2023

This is the time of year when we have to send out “tax letters” providing our members with an official record of their donations for the previous year.

ChurchInfo provides a somewhat customizable report for this purpose.  The report is accessed from Data/Reports->Reports Menu, and the link “Financial Reports”.  The report type is “Giving Report (Tax Statements)”.  The next page allows some customization.  For tax statements, set the start and end dates and tell it to use the Payment Date rather than the Deposit data.  With a little luck it will provide a PDF file where each page is a letter that can be folded, stuffed in a window envelope and mailed.  The text phrases used in the report may be set in Admin->Edit Report Settings for additional customization.

If this feature doesn’t work for your situation things become somewhat more complicated.  Here is a custom query that should be helpful for anyone who has to do some sort of special mail merge.

SELECT b.per_firstname as Adult1First, b.per_lastname as Adult1Last, c.per_firstname as Adult2First, c.per_lastname as Adult2Last, fam_Name as FamilyName, fam_address1, fam_address2, fam_city, fam_state, fam_zip, fam_envelope, sum(a.plg_amount) as TotalPayments from family_fam left join pledge_plg a on a.plg_famID = fam_ID and a.plg_Date>=’2010-01-01′ and a.plg_Date<=’2010-12-31′ and a.plg_PledgeOrPayment=’Payment’ left outer join person_per b on b.per_fam_ID=fam_id and b.per_fmr_id=1 left outer join person_per c on c.per_fam_ID=fam_id and c.per_fmr_id=2
where a.plg_amount>0 and not isnull(a.plg_amount) group by fam_id

To run this query, select Data/Reports->Query Menu and click the link “Run a Free-Text Query”.  Paste the whole query string inside and press “Execute SQL”.  This link is only available to administrators.

Note that this query hard-codes the start and end dates and the two adult family roles.  Running this query on a large database can take a long time!  For my church it takes more than five minutes.  You may have to adjust the settings on your browser to permit such a long page loading time. 

Leave a Reply

Your email address will not be published. Required fields are marked *