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¶
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.