Monthly Giving Analysis

This report runs from the Totals by Fund page. The report will present a column for each month 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/./2018-04-30_12-08-12.png

Create the Monthly Giving Analysis Report

Step 1
Go to Administration > Setup > 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 MonthlyGivingAnalysis.
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 Monthly Giving Analysis

Step 1
Go to Administration > Contributions > Totals by Fund.
Step 2
Select the Campus (or leave unspecified), Date Range, Tax Status, 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 Monthly Giving Analysis.

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

Note

The monthly 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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
--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,
    convert(varchar, year(c.Date)) + '-' + format(month(c.Date), '0#') as ColName,
    sum(c.Amount) as AmtSum
    into #t
from dbo.Contributions2(@StartDate, @EndDate, 0, 0, NULL, 1, NULL) c
join dbo.People p on p.PeopleId = c.PeopleId
left join lookup.Campus cc on cc.Id = @CampusId
left join lookup.Campus pc on pc.Id = p.CampusId
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 (@CampusId = 0 or coalesce(c.Campus, pc.Description) = cc.Description)
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), month(c.Date)

select
    c.FamilyId,
    'Total' as ColName,
    sum(c.Amount) as AmtSum
    into #tt
from dbo.Contributions2(@StartDate, @EndDate, 0, 0, NULL, 1, NULL) c
join dbo.People p on p.PeopleId = c.PeopleId
left join lookup.Campus cc on cc.Id = @CampusId
left join lookup.Campus pc on pc.Id = p.CampusId
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 (@CampusId = 0 or coalesce(c.Campus, pc.Description) = cc.Description)
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],
    null [Campus],
    null [MemberStatus],
    null [AssimCode],
    null [Flock],
    '' [Address],
    '' [City],
    '' [State],
    '' [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,
    campus.Description [Campus],
    mbr.Description [MemberStatus],
    assim.StrValue [AssimCode],
    stuff((select '; ' + sfl.Name from dbo.TagPerson tp
                                    join dbo.Tag t on t.Id = tp.Id
                                    join dbo.StatusFlagList sfl on sfl.Flag = t.StatusFlagId
                                  where tp.PeopleId = h.PeopleId
                                    and t.StatusFlagId is not NULL 
                                    and sfl.Name like '% Flock' for xml path('')), 1, 2, '') [Flock],
    f.AddressLineOne + case when f.AddressLineTwo is not null then ', ' + f.AddressLineTwo else '' end [Address],
    f.CityName City,
    f.StateCode [State],
    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
join lookup.Campus campus on campus.Id = h.CampusId
join lookup.MemberStatus mbr on mbr.Id = h.MemberStatusId
left join dbo.PeopleExtra assim on assim.PeopleId = h.PeopleId and assim.Field = 'AssimCode'
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 10/19/2021

Modify script to account for empty (not NULL) @fundset.