Yearly Giving Analysis

This report runs from the Totals by Fund page. The report will present a column for each year in the date range selected, but will report only Primary Adults and will combine their contributions. The first columns present information about the donor(s), including the Last Gift Date and the Last Attended Date within the date range selected.

Sample Report

https://i.tpsdb.com/./2025-02-26_10-57-27.png

Create the Yearly Giving Analysis Report

Step 1

Go to Admin > Advanced > Special Content and select the Sql Scripts tab.

Step 2

Click the green +New Sql Script File button and enter the name of the script as YearlyGivingAnalysis.

Step 3

Copy all of the code below and paste it into the file, then click Save Sql Script at the bottom of the page.

Do not run the script from this file. It will automatically be added to the Totals by Fund Report under Other Reports.

Run the Yearly Giving Analysis

Step 1

Go to Finance > Search > Totals by Fund.

Step 2

Select the Campus (or leave unspecified), Date Range, and Source. If you have Custom Fund Sets, select the set you want included in the report. You can also run this report for the Active Tag.

Step 3

Click Run. Then, under Other Reports, select Yearly Giving Analysis.

You can download to Excel. That button is at the bottom of the report on the left side.

Note

The yearly totals will combine all giving for the donor(s) for all funds. If you want the report for a single fund or to include just a couple of funds, create a Custom Fund Set file and set up sets for a single fund or for multiple funds to report on together.

See also

../../TextScripts/CustomFundSets

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
--roles=Finance
--class=TotalsByFund

drop table if exists #t
drop table if exists #tt
drop table if exists [##957C899C-59FA-47DA-A946-89680B02BB16]
drop table if exists [##957C899C-59FA-47DA-A946-89680B02BB16sum]

select
    c.FamilyId,
    --'FY' + case when month(c.Date) < 7 then convert(varchar, year(c.Date)-1) else convert(varchar, year(c.Date)) end as ColName,
    convert(varchar, year(c.Date)) as ColName,
    sum(c.Amount) as AmtSum
    into #t
from dbo.Contributions2(@StartDate, @EndDate, @CampusId, 0, NULL, 1, NULL) c
join dbo.People p on p.PeopleId = c.PeopleId
where (isnull(@FundSet, '') = '' or c.FundId in (select Value from dbo.SplitInts(@FundSet)) )
and (isnull(@TaxNonTax, '') = '' or c.ContributionTypeId in (select Value from dbo.SplitInts(@TaxNonTax)) )
and p.PositionInFamilyId = 10
and (@ActiveTagFilter is null or exists(select null from dbo.TagPerson tp where tp.Id = @ActiveTagFilter and tp.PeopleId = c.PeopleId))
group by c.FamilyId, year(c.Date)

select
    c.FamilyId,
    'Total' as ColName,
    sum(c.Amount) as AmtSum
    into #tt
from dbo.Contributions2(@StartDate, @EndDate, @CampusId, 0, NULL, 1, NULL) c
join dbo.People p on p.PeopleId = c.PeopleId
where (isnull(@FundSet, '') = '' or c.FundId in (select Value from dbo.SplitInts(@FundSet)) )
and (isnull(@TaxNonTax, '') = '' or c.ContributionTypeId in (select Value from dbo.SplitInts(@TaxNonTax)) )
and p.PositionInFamilyId = 10
and (@ActiveTagFilter is null or exists(select null from dbo.TagPerson tp where tp.Id = @ActiveTagFilter and tp.PeopleId = c.PeopleId))
group by c.FamilyId

declare @columns nvarchar(max), @sql nvarchar(max);
set @columns = '';
select @columns+=', '+quotename(x.ColName)
from (
    select ColName from #t
    group by ColName
) as x
order by ColName

declare @sumcolumns nvarchar(max), @sumsql nvarchar(max);
set @sumcolumns = '';
select @sumcolumns+=', sum('+quotename(x.ColName) + ') as ' + quotename(x.ColName)
from (
    select ColName from #t
    group by ColName
) as x
order by ColName


set @sql = '
select [FamilyId],
    '+stuff(@columns, 1, 2, '')+' into [##957C899C-59FA-47DA-A946-89680B02BB16]
from ( select
        [FamilyId],
        [AmtSum],
        [ColName]
    from #t
) AS j pivot (sum(AmtSum) for [ColName] in
       ('+stuff(replace(@columns, ', piv.[', ',['), 1, 1, '')+')) AS piv;';
exec (@sql)


set @sumsql = 'select ' + stuff(@sumcolumns, 1, 2, '') + ' into [##957C899C-59FA-47DA-A946-89680B02BB16sum] from [##957C899C-59FA-47DA-A946-89680B02BB16] '
exec (@sumsql)

select
    null PeopleId,
    'TOTALS' [First],
    'LINE' [Last],
    '' [Spouse],
    null [Age],
    '' [City],
    '' [Zip],
    null LastGiftDate,
    null LastAttended,
    null FamilyId,
    tot.*,
    (select sum(AmtSum) from #tt) Total
from [##957C899C-59FA-47DA-A946-89680B02BB16sum] as tot

union all

select
    h.PeopleId,
    h.PreferredName [First],
    h.LastName [Last],
    s.PreferredName [Spouse],
    h.Age,
    f.CityName City,
    substring(f.ZipCode, 1, 5) Zip,
    (select max(convert(date, contributionDate)) from dbo.Contribution c where c.PeopleId in (h.PeopleId, s.PeopleId)) LastGiftDate,
    (select max(convert(date, a.MeetingDate)) from dbo.Attend a where a.PeopleId in (h.PeopleId, s.PeopleId) and a.AttendanceFlag = 1) LastAttended,
    t.*,
    #tt.AmtSum Total
from [##957C899C-59FA-47DA-A946-89680B02BB16] as t
join #tt on #tt.FamilyId = t.FamilyId
join dbo.Families f on f.FamilyId = t.FamilyId
join dbo.People h on h.PeopleId = f.HeadOfHouseholdId
left join dbo.People s on s.PeopleId = f.HeadOfHouseholdSpouseId

drop table if exists #t
drop table if exists #tt
drop table if exists [##957C899C-59FA-47DA-A946-89680B02BB16]
drop table if exists [##957C899C-59FA-47DA-A946-89680B02BB16sum]


Latest Update

02/26/2025

Added article