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.

Screenshot 2019-10-29 16.01.28.png

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.

Screenshot 2019-10-29 15.50.22.png

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.
  • 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['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
                                      , p.FirstName
                                      , p.LastName
                                      , p.Id
                                      , p.Email
                                      , dbo.ufnCrm_GetAddress(p.Id,'Home','FormattedHtmlAddress') as [Address]
                                      , pn.NumberFormatted as MobilePhone
                                      , 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
                          
                                  Left Outer Join PhoneNumber pn
                                      On pn.PersonId = p.Id and NumberTypeValueId = 12
                          
                                  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.AccountId = @account
                                      
                                          and fp.StartDate <= @endDate
                                          and fp.EndDate >= @endDate      
                          
                          Select 
                                  PledgeId
                                  , [Name]
                                  , FirstName
                                  , LastName
                                  , Email
                                  , [Address]
                                  , 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([TotalGiven], 'C') as [TotalGiven]
                                  , FORMAT(PledgeAmount, 'C') as TotalPledge
                                  , Format(TotalGiven - (PledgeAmount), 'C') as DifferenceFromTotalPledge
                                  , Format(PledgeAmount * (PledgeProgress / 100),'C') as PledgeToDate
                                  , Format(TotalGiven - (PledgeAmount * (PledgeProgress / 100)), 'C') as DifferenceToDate
                          
                                  , LastGave
                          From #TempPledgeTable tpt 
                          
                          Drop Table #TempPledgetable
                          {%- 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 %}