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

http://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

Custom Fund Sets

--roles=Finance
--class=TotalsByFund

/*
declare @StartDate datetime = convert(datetime, '1/1/2017')
declare @EndDate datetime = getdate()
declare @FundSet varchar(max) = '180'
declare @TaxNonTax varchar(50) = 'Both'
declare @CampusId int = 0
declare @ActiveTagFilter int
*/

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
            ,@CampusId, 0, (case @TaxNonTax when 'TaxDed' then 0 when 'Both' then null else 1 end), 1) c
join dbo.People p on p.PeopleId = c.PeopleId
where (@FundSet is null or exists(select null from dbo.SplitInts(@FundSet) where Value = c.FundId))
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
            ,@CampusId, 0, (case @TaxNonTax when 'TaxDed' then 0 when 'Both' then null else 1 end), 1) c
join dbo.People p on p.PeopleId = c.PeopleId
where (@FundSet is null or exists(select null from dbo.SplitInts(@FundSet) where Value = c.FundId))
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]

© 2019 TouchPoint Software, LLC.  All rights reserved.