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

2. Analysis Date(Date Type) for selecting the date you want to run the report against.
3. 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