Recipe - Pledge Analysis Page
Skill level: Beginner
Organization: Kevin Rutledge
Requires Rock: 1.8.0
{# strip images & classes from the HTML but otherwise leave structure #}
I have often found it diffucult to return the pledge analysis information needed by my organization using the built in tools. For various reasons we do not follow the best practice of creating a new financial account for each pledge year/period. Instead, we need to rely on the pledges start and end date based on either the current date or a selected date. We also need to be able to figure out the pledge status based on that selected date. If we are 50% through the year, anything at or above 50% of the pledge given would be considered meets or over.
We want two different reports.
First we want a list of people who pledged to a particular account, how much they pledged, how much they gave, how far ahead/behind they are, and when they last gave.
Second, we wanted an easy to read chart that summarizes the information below giving pledge counts based on status and total difference between pledged and given for all statuses(Over, Under, and Meets). Below is a picture of the summary report.
For both reports, we need to be able to select the account and date we want to run the report up to.
You will need to create a page with three blocks.
- BEMA's Page Filter Parameters Two plugin Block with three filters
- Financial Account(Single-Select) with the following sql which will return the accounts with
pledges
Select Distinct
fa.PublicName as Text,
fa.Guid as Value
From FinancialAccount fa
Inner Join FinancialPledge fp
on fp.AccountId = fa.Id
Order By Fa.PublicName
- Analysis Date(Date Type) for selecting the date you want to run the report against.
- Tolerance Percent: At any given time a person may not meet a pledge exactly. This integer value will be converted to a percent and as long as they are within that tolerance, they will be marked as meeting their pledge.
- Dynamic Data Block 1 - The first dynamic data block returns the people list of pledges active at the date
selected or the current date if left blank with the following settings.
- Query:
{%- if PageParameter['ActivePledges'] -%}
{%- assign active = PageParameter['ActivePledges'] -%}
{%- endif -%}
{%- if PageParameter['FinancialAccount'] -%}
{%- assign account = PageParameter['FinancialAccount'] -%}
{%- endif -%}
{%- if PageParameter['AnalysisDate'] -%}
{%- assign analysisdate = PageParameter['AnalysisDate'] -%}
{%- endif -%}
{%- if account -%}
{%- if analysisdate -%}
Declare @endDate as Date = '{{ analysisdate | Date: 'yyyy-MM-dd' }}' -- Date Set
{% else %}
Declare @endDate as Date = getDate() -- get Date
{% endif %}
Declare @account as Int = (Select Id From FinancialAccount Where [Guid] = '{{account}}')
DECLARE @PledgeReport table (PledgeId int, AccountId int, StartDate date, EndDate date, PersonId int, PledgeAmount decimal, TotalGiven decimal,PercentPledgeGiven decimal, PledgeProgress decimal )
INSERT INTO @PledgeReport
Select
fp.Id as PledgeId
, fp.AccountId as AccountId
, fp.StartDate
, fp.EndDate
, p.Id
, fp.TotalAmount as PledgeAmount
, Case When [TotalGiven] is Null Then 0 Else [TotalGiven] End as TotalGiven
, Case When TotalGiven is Null Then 0 Else Floor(Cast(TotalGiven as Decimal) / Cast(fp.TotalAmount as Decimal) * 100) End As PercentPledgeGiven
, Case
When Cast(DATEDIFF(day, fp.StartDate, @endDate) as Decimal) / Cast(DateDiff(day, fp.StartDate, fp.EndDate) as Decimal) > 1 Then 100
When TotalGiven = 0 Then 0
Else Floor(Cast(DATEDIFF(day, fp.StartDate, @endDate) as Decimal) / Cast(DateDiff(day, fp.StartDate, fp.EndDate) as Decimal) * 100)
End as PledgeProgress
From FinancialPledge fp
Join PersonAlias pa
on pa.Id = fp.PersonAliasId
Inner Join Person p on pa.PersonId = p.Id
Left Outer Join PhoneNumber pn
On pn.PersonId = p.Id and NumberTypeValueId = 12
Outer Apply (
Select
Distinct
Sum(aft.Amount) Over (Partition By fp.Id) as TotalGiven
From AnalyticsFactFinancialTransaction aft
Where
aft.TransactionDateTime >= fp.StartDate
and aft.TransactionDateTime <= @endDate
and p.GivingGroupId = aft.GivingGroupId
and fp.AccountId = aft.AccountId
) as [Sum]
Where
fp.AccountId = @account
and fp.StartDate <= @endDate
and fp.EndDate >= @endDate
Select
PledgeId
,p.Id
, fa.[Name]
, p.FirstName + ' ' + p.LastName as FullName
, spouse.FirstName + ' ' + spouse.LastName as Spouse
, p.Email
, dbo.ufnCrm_GetAddress(p.Id,'Home','FormattedHtmlAddress') as [Address]
, pn.NumberFormatted as MobilePhone
, Concat(PledgeProgress,'%') as PledgeProgress
, Concat(PercentPledgeGiven,'%') as '% Pledge Given'
, Case
When tpt.PercentPledgeGiven > tpt.PledgeProgress then 'Over'
When tpt.PercentPledgeGiven = tpt.PledgeProgress then 'Meets'
ELse 'Under'
End as PledgeStatus
, FORMAT(PledgeAmount, 'C') as TotalPledge
, FORMAT([TotalGiven], 'C') as [TotalGiven]
, Format(TotalGiven - (PledgeAmount), 'C') as DifferenceFromTotalPledge
, Format(PledgeAmount * (PledgeProgress / 100),'C') as PledgeToDate
, Format(TotalGiven - (PledgeAmount * (PledgeProgress / 100)), 'C') as DifferenceToDate
, av.ValueAsDateTime as LastGave
From @PledgeReport tpt
Inner Join FinancialAccount fa
on fa.Id = tpt.AccountId
Inner Join Person p
on p.Id = tpt.PersonId
Left Outer Join PhoneNumber pn
On pn.PersonId = p.Id and NumberTypeValueId = 12
Left Outer Join AttributeValue av
on p.Id = av.EntityId and AttributeId = 1788
Left OUter Join Person spouse
on spouse.Id = dbo.ufnCrm_GetSpousePersonIDFromPersonId(p.Id)
{%- endif -%}
- Hide Columns - For us, these columns were wanted on the sql export but not needed on the screen.
- PledgeId, Name, Address, Email, MobilePhone, Id
- Selection Url - /page/159?pledgeId={PledgeId}
- Timeout - 60
- Dynamic Data Block 2 - The second dynamci data block shows the pledge analytics based on the number over, under,
or meeting their pledge, by how much over the total pledge and by how much for the pledge to the selected date.
- Query:
{%- if PageParameter['FinancialAccount'] -%}
{%- assign account = PageParameter['FinancialAccount'] -%}
{%- endif -%}
{%- if PageParameter['AnalysisDate'] -%}
{%- assign analysisdate = PageParameter['AnalysisDate'] -%}
{%- endif -%}
{%- if account -%}
{%- if analysisdate -%}
Declare @endDate as Date = '{{ analysisdate | Date: 'yyyy-MM-dd' }}' -- Date Set
{% else %}
Declare @endDate as Date = getDate() -- get Date
{% endif %}
Declare @account as Int = (Select Id From FinancialAccount Where [Guid] = '{{account}}')
Select
fp.Id as PledgeId
, getDate() as DateRan
, @endDate as SettingEndDate
, fa.Name
, fa.Id as AccountId
, fp.StartDate
, fp.EndDate
, fp.TotalAmount as PledgeAmount
, Case When [TotalGiven] is Null Then 0 Else [TotalGiven] End as TotalGiven
, Case When TotalGiven is Null Then 0 Else Floor(Cast(TotalGiven as Decimal) / Cast(fp.TotalAmount as Decimal) * 100) End As PercentPledgeGiven
, Case
When Cast(DATEDIFF(day, fp.StartDate, @endDate) as Decimal) / Cast(DateDiff(day, fp.StartDate, fp.EndDate) as Decimal) > 1 Then 100
When TotalGiven = 0 Then 0
Else Floor(Cast(DATEDIFF(day, fp.StartDate, @endDate) as Decimal) / Cast(DateDiff(day, fp.StartDate, fp.EndDate) as Decimal) * 100)
End as PledgeProgress
, Case
When Cast(DATEDIFF(day, fp.StartDate, @endDate) as Decimal) / Cast(DateDiff(day, fp.StartDate, fp.EndDate) as NVarChar(4)) > 1 Then '100%'
Else Concat(Cast(Floor(Cast(DATEDIFF(day, fp.StartDate, @endDate) as Decimal) / Cast(DateDiff(day, fp.StartDate, fp.EndDate) as decimal) * 100) as NVarChar(15)),'%')
End as '% of Pledge Term Completed'
, av.ValueAsDateTime as LastGave
into #TempPledgeTable
From FinancialPledge fp
Join PersonAlias pa
on pa.Id = fp.PersonAliasId
Inner Join Person p on pa.PersonId = p.Id
Inner Join FinancialAccount fa
on fa.Id = fp.AccountId
Left Outer Join AttributeValue av
on p.Id = av.EntityId and AttributeId = 1788
Outer Apply (
Select
Distinct
Sum(aft.Amount) Over (Partition By fp.Id) as TotalGiven
From AnalyticsFactFinancialTransaction aft
Where
aft.TransactionDateTime >= fp.StartDate
and aft.TransactionDateTime <= @endDate
and p.GivingGroupId = aft.GivingGroupId
and fp.AccountId = aft.AccountId
) as [Sum]
Where
fp.StartDate <= @endDate
and fp.EndDate >= @endDate
and fp.AccountId = @account
Select
PledgeAmount
,tpt.Name
, [TotalGiven] as AmountGiven
, PledgeAmount * (PledgeProgress / 100) as PledgeToDate
, TotalGiven - (PledgeAmount * (PledgeProgress / 100)) as DifferenceToDate
, TotalGiven - (PledgeAmount) as DifferenceFromTotalPledge
, Case
When tpt.PercentPledgeGiven > tpt.PledgeProgress then 'Over'
When tpt.PercentPledgeGiven = tpt.PledgeProgress then 'Meets'
ELse 'Under'
End as PledgeStatus
From #TempPledgeTable tpt
Drop Table #TempPledgetable
{%- endif -%}
- Timeout - 60
- Formatted Output
{%- if PageParameter['FinancialAccount'] -%}
{%- assign account = PageParameter['FinancialAccount'] -%}
{%- endif -%}
{%- if PageParameter['AnalysisDate'] -%}
{%- assign analysisdate = PageParameter['AnalysisDate'] -%}
{% else %}
{%- assign analysisdate = 'Now' | Date:'MM/dd/yyyy' -%}
{%- endif -%}
{% if account %}
{% assign count = 0 %}
{% assign PledgeOverCount = 0 %}
{% assign PledgeOver = 0 %}
{% assign PledgeOverToDate = 0 %}
{% assign GivenOver = 0 %}
{% assign PledgeMeetsCount = 0 %}
{% assign PledgeMeets = 0 %}
{% assign GivenMeets = 0 %}
{% assign PledgeMeetsToDate = 0 %}
{% assign PledgeUnderCount = 0 %}
{% assign PledgeUnder = 0 %}
{% assign GivenUnder = 0 %}
{% assign PledgeUnderToDate = 0 %}
{% for row in rows %}
{% if row.PledgeStatus == 'Over' %}
{% assign count = count | Plus:1 %}
{% assign PledgeOverCount = PledgeOverCount | Plus: 1 %}
{% assign PledgeOver = PledgeOver | Plus: row.PledgeAmount %}
{% assign PledgeOverToDate = row.PledgeToDate | Plus: PledgeOverToDate %}
{% assign GivenOver = GivenOver | Plus: row.AmountGiven %}
{% elseif row.PledgeStatus == 'Meets' %}
{% assign count = count | Plus:1 %}
{% assign PledgeMeetsCount = PledgeMeetsCount | Plus: 1 %}
{% assign PledgeMeets = PledgeMeets | Plus: row.PledgeAmount %}
{% assign PledgeMeetsToDate = row.PledgeToDate | Plus: PledgeMeetsToDate %}
{% assign GivenMeets = GivenMeets | Plus: row.AmountGiven %}
{% else %}
{% assign count = count | Plus:1 %}
{% assign PledgeUnderCount = PledgeUnderCount | Plus: 1 %}
{% assign PledgeUnder = PledgeUnder | Plus: row.PledgeAmount %}
{% assign PledgeUnderToDate = row.PledgeToDate | Plus: PledgeUnderToDate %}
{% assign GivenUnder = GivenUnder | Plus: row.AmountGiven %}
{% endif %}
{% endfor %}
<div class="panel panel-default">
<div class="panel-heading"> {% capture title %}Pledge Analysis for {{rows[0].Name}} as of {{analysisdate | Date:'MM/dd/yyyy'}}{% endcapture %}{{title | SetPageTitle }}{{ title }}</div>
<div class="panel-body">
<table class="table-striped table table-hover table-responsive table-sm">
<thead>
<tr>
<th> Pledge Status:</th>
<th> Pledge Count:</th>
<th> Total $ Pledged:</th>
<th> Total $ Pledge To Date:</th>
<th> Total $ Given:</th>
<th> Difference To Date:</th>
<th> Pledge Difference:</th>
</tr>
</thead>
<tbody>
<tr>
<td> Over</td>
<td> {{ PledgeOverCount }}</td>
<td> {{ PledgeOver | FormatAsCurrency }}</td>
<td> {{ PledgeOverToDate | FormatAsCurrency}}</td>
<td> {{ GivenOver | FormatAsCurrency}}</td>
<td> {{ GivenOver | Minus:PledgeOverToDate | FormatAsCurrency }}</td>
<td> {{GivenOver | Minus: PledgeOver | FormatAsCurrency}}</td>
</tr>
<tr>
<td> Meets</td>
<td> {{ PledgeMeetsCount }}</td>
<td> {{ PledgeMeets | FormatAsCurrency }}</td>
<td> {{ PledgeMeetsToDate | FormatAsCurrency}}</td>
<td> {{ GivenMeets | FormatAsCurrency}}</td>
<td> {{ GivenMeets | Minus:PledgeMeetsToDate | FormatAsCurrency }}</td>
<td> {{GivenMeets | Minus: PledgeMeets | FormatAsCurrency}}</td>
</tr>
<tr>
<td> Under</td>
<td> {{ PledgeUnderCount }}</td>
<td> {{ PledgeUnder | FormatAsCurrency }}</td>
<td> {{ PledgeUnderToDate | FormatAsCurrency}}</td>
<td> {{ GivenUnder | FormatAsCurrency}}</td>
<td> {{ GivenUnder | Minus:PledgeUnderToDate | FormatAsCurrency }}</td>
<td> {{GivenUnder | Minus: PledgeUnder | FormatAsCurrency}}</td>
</tr>
</tbody>
<tfoot>
{% assign totalPledged = PledgeUnder | Plus: PledgeOver | Plus: PledgeMeets %}
{% assign totalPledgedToDate = PledgeUnderToDate | Plus: PledgeOverToDate | Plus: PledgeMeetsToDate %}
{% assign totalGiven = GivenUnder | Plus: GivenOver | Plus: GivenMeets %}
<tr style="border-top:1px solid black;" >
<th> Total</td>
<th> {{ PledgeUnderCount | Plus: PledgeOverCount | Plus: PledgeMeetsCount }}</th>
<th> {{ totalPledged | FormatAsCurrency }}</th>
<th> {{ totalPledgedToDate | FormatAsCurrency}}</th>
<th> {{ totalGiven | FormatAsCurrency}}</th>
<th> {{ totalGiven | Minus:totalPledgedToDate | FormatAsCurrency }}</th>
<th> {{totalGiven | Minus: totalPledged | FormatAsCurrency}}</th>
</tr>
</tfoot>
</table>
</div>
</div>
{% else %}
<div class="alert alert-info">Please select a Financial Account and Click Filter</div>
{% endif %}
Screenshots
- /GetImage.ashx?guid=3098d4f2-efaf-4519-baa4-d7c4638770ea
- /GetImage.ashx?guid=c3393a13-2eca-4169-a2fe-f326098d90d1