This recipe was created by modifying the SQL: Givers by Amount and Monthly Consistency recipe by Josh Crews, with Simple Donation.

There is an attached .sql file that contains:

  • The code and documentation for a stored procedure you can add to your Rock SQL database.
  • Sample execution calls so you can see the various ways the data can be pulled.
  • The descriptions of what blocks and settings need to be on your page in Rock (also detailed out below).

If you need any assistance in creating the stored procedure on your server, let me know. Happy to help!

One thing I have not added yet is the formatting for the output. So, the table that comes out is pretty basic. (Screenshot included)

I am also already working on V2 of this solution that will combine Person and Business giving where a "soft credit" needs to exist. Happy to share that update if anyone thinks they would find it useful, when I am finished with it.

On your Rock page, you will need to add the following:

  • A Filter By Page Parameters Block with 4 fields, Min and Max Amount, Campus (if you are multi campus), and As Of Date. (Screenshot included)
  • The Key for each will need to be exactly what I list here, or you will need to change the code for the dynamic data block to reflect your keys. (MinAmount, MaxAmount, Campus, AsOfDate)
  • A dynamic data block, with the following text in the Query box
Declare @minamt money = null
Declare @maxamt money = null
Declare @campusguid nvarchar(36) = null
{% assign minamt = 'Global' | PageParameter:'MinAmount' %}
{% assign maxamt = 'Global' | PageParameter:'MaxAmount' %}
{% assign campusguid = 'Global' | PageParameter:'Campus' %}
DECLARE @asofdate date = '{{'Global' | PageParameter:'AsOfDate'}}'
{% if minamt > 0 %}
Set @minamt = {{ minamt }}
{% endif %}
{% if maxamt > 0 %}
set @maxamt = {{ maxamt }}
{% endif %}
{% if campusguid > '' %}
set @campusguid = '{{ campusguid }}'
{% endif %}
DECLARE @Today DATETIME
Declare @MonthsBack Datetime
if @asofdate = '1/1/1900'
begin
SET @Today = dbo.GetDate()
end
else
begin
SET @Today = @asofdate
end
if OBJECT_ID('tempdb..#tempSummary') is not null
drop table #tempSummary
set @MonthsBack = dateadd(month, -1, @Today)
Declare @Month1 nvarchar(20) = '[' + (select left(convert(nvarchar, @MonthsBack, 107), 3) + ' ' + right(convert(nvarchar, @MonthsBack, 107), 4)) + ']'
set @MonthsBack = dateadd(month, -2, @Today)
Declare @Month2 nvarchar(20) = '[' + (select left(convert(nvarchar, @MonthsBack, 107), 3) + ' ' + right(convert(nvarchar, @MonthsBack, 107), 4)) + ']'
set @MonthsBack = dateadd(month, -3, @Today)
Declare @Month3 nvarchar(20) = '[' + (select left(convert(nvarchar, @MonthsBack, 107), 3) + ' ' + right(convert(nvarchar, @MonthsBack, 107), 4)) + ']'
set @MonthsBack = dateadd(month, -4, @Today)
Declare @Month4 nvarchar(20) = '[' + (select left(convert(nvarchar, @MonthsBack, 107), 3) + ' ' + right(convert(nvarchar, @MonthsBack, 107), 4)) + ']'
set @MonthsBack = dateadd(month, -5, @Today)
Declare @Month5 nvarchar(20) = '[' + (select left(convert(nvarchar, @MonthsBack, 107), 3) + ' ' + right(convert(nvarchar, @MonthsBack, 107), 4)) + ']'
set @MonthsBack = dateadd(month, -6, @Today)
Declare @Month6 nvarchar(20) = '[' + (select left(convert(nvarchar, @MonthsBack, 107), 3) + ' ' + right(convert(nvarchar, @MonthsBack, 107), 4)) + ']'
set @MonthsBack = dateadd(month, -7, @Today)
Declare @Month7 nvarchar(20) = '[' + (select left(convert(nvarchar, @MonthsBack, 107), 3) + ' ' + right(convert(nvarchar, @MonthsBack, 107), 4)) + ']'
set @MonthsBack = dateadd(month, -8, @Today)
Declare @Month8 nvarchar(20) = '[' + (select left(convert(nvarchar, @MonthsBack, 107), 3) + ' ' + right(convert(nvarchar, @MonthsBack, 107), 4)) + ']'
set @MonthsBack = dateadd(month, -9, @Today)
Declare @Month9 nvarchar(20) = '[' + (select left(convert(nvarchar, @MonthsBack, 107), 3) + ' ' + right(convert(nvarchar, @MonthsBack, 107), 4)) + ']'
set @MonthsBack = dateadd(month, -10, @Today)
Declare @Month10 nvarchar(20) = '[' + (select left(convert(nvarchar, @MonthsBack, 107), 3) + ' ' + right(convert(nvarchar, @MonthsBack, 107), 4)) + ']'
set @MonthsBack = dateadd(month, -11, @Today)
Declare @Month11 nvarchar(20) = '[' + (select left(convert(nvarchar, @MonthsBack, 107), 3) + ' ' + right(convert(nvarchar, @MonthsBack, 107), 4)) + ']'
set @MonthsBack = dateadd(month, -12, @Today)
Declare @Month12 nvarchar(20) = '[' + (select left(convert(nvarchar, @MonthsBack, 107), 3) + ' ' + right(convert(nvarchar, @MonthsBack, 107), 4)) + ']'
declare @sqlcmd nvarchar(4000)
Set @sqlcmd = N'CREATE TABLE #tempSummary
(PersonId int, Campus nvarchar(50), [Person/Business] nvarchar(max), AmountPrev money, Amount money, MonthsGiven int, Month1 money, Month2 money, Month3 money, Month4 money, Month5 money, Month6 money, Month7 money, Month8 money, Month9 money, Month10 money, Month11 money, Month12 money)'
+ ' declare @StartDate DateTime = ''' + convert(nvarchar, @Today, 101) + ''' '
+ ' declare @minamt money = ' + case when @minamt is null then 'null' else cast(@minamt as nvarchar) end
+ ' declare @maxamt money = ' + case when @maxamt is null then 'null' else cast(@maxamt as nvarchar) end
+ ' declare @campusguid nvarchar(36) = ' + case when @campusguid is null then 'null' else '''' + @campusguid + '''' end
+ ' INSERT INTO #tempSummary exec sps_Grace_12MonthsGivingSummary @StartDate, @minamt, @maxamt, @campusguid'
+ ' select PersonId, [Person/Business], Campus, '
+ ' Format(AmountPrev, ''C'', ''EN-US'') as Previous12MonthsTotal, '
+ ' Format(Amount, ''C'', ''EN-US'') as Last12MonthsTotal, '
+ ' MonthsGiven, '
+ ' Format(Month1, ''C'', ''EN-US'') as ' + @Month1 + ', '
+ ' Format(Month2, ''C'', ''EN-US'') as ' + @Month2 + ', '
+ ' Format(Month3, ''C'', ''EN-US'') as ' + @Month3 + ', '
+ ' Format(Month4, ''C'', ''EN-US'') as ' + @Month4 + ', '
+ ' Format(Month5, ''C'', ''EN-US'') as ' + @Month5 + ', '
+ ' Format(Month6, ''C'', ''EN-US'') as ' + @Month6 + ', '
+ ' Format(Month7, ''C'', ''EN-US'') as ' + @Month7 + ', '
+ ' Format(Month8, ''C'', ''EN-US'') as ' + @Month8 + ', '
+ ' Format(Month9, ''C'', ''EN-US'') as ' + @Month9 + ', '
+ ' Format(Month10, ''C'', ''EN-US'') as ' + @Month10 + ', '
+ ' Format(Month11, ''C'', ''EN-US'') as ' + @Month11 + ', '
+ ' Format(Month12, ''C'', ''EN-US'') as ' + @Month12
+ ' from #tempSummary '+ ' order by Amount desc'
exec sp_sqlexec @sqlcmd

The grid will display the following columns:

  • Person/Business - The name of the person or business who made the contribution. We are already working on a solution to combine rows for businesses and the person who should receive the "soft credit" for the contribution. 
  • Campus
  • Previous 12 Months Total - This total is for the 12 months prior to the 12 months being displayed on the report. This is so you can do a year over year comparison of the totals for the exact same time frame.
  • Last 12 Months Total - This is the total for the 12 months of detail being displayed on the report.
  • Months Given - How many months in the last 12 months the person/business gave. This is not how many gifts they gave. If they gave 3 times in one month, that would count as one time in this report. The lowest possible value in this column is 1 and the highest is 12.
  • 12 months of totals per month. The column headers will be dynamically generated in the code above so that they show the particular month that the total is for.

Keep in mind that the report will never show transactions that happen during the month of the "As Of Date". So, if you choose 8/1/2020 as your "As Of Date", the report will run for the 12 months from Aug 2019 through July 2020, so that we are never looking at a partial month of giving and
comparing the year over year values with full months.

There are no required filters/values on this page. If none are filled in, the stored procedure will use today's date as the "As Of Date" and will apply no other filters.