Pledge Payment Dates in Analytics

Best I've managed to get is the schedule dates,  amount paid,  balance amount and total amount

Contribution date does the schedule for me but I've not been able to get the schedule amount

Best I've managed to get is the schedule dates,  amount paid,  balance amount and total amount

Contribution date does the schedule for me but I've not been able to get the schedule amount

Thanks for your input Heath!

I was able to find the full schedule of Pledge payments is in T_SCHEDULE. In Analytics, you can pull the Constituent ID, Contribution Number, and Contribution Date (filtered by Pledge status). The Contribution Number in Analytics is the ref_no in T_CONTRIBUTION and T_SCHEDULE.

In SSMS, I did the following:

SELECT C.customer_no, CUST.sort_name,C.ref_no, S.due_dt as [Due Date], S.amt_due
FROM T_CONTRIBUTION AS C
JOIN T_SCHEDULE AS S ON C.ref_no = S.ref_no
JOIN T_CUSTOMER AS CUST ON CUST.customer_no = C.customer_no
WHERE status = 'S'
/*S = Scheduled*/
AND
C.customer_no IN (
--insert your comma delimited list here
)
GROUP BY C.customer_no, CUST.sort_name, C.ref_no, S.due_dt, S.amt_due
;

Sort_name is not the best way to display the name. I only used it as a proof of concept.

As it turns out, this was not highly needed; so I'll file this knowledge in a folder and move on to the next thing. Oh well, hopefully, this information will help someone else.

That's wonderful Neil. I should ve also said i have a SSRS report that i created that pulls out the schedules, balance, unpaid etc for a campaign.  I wrote it for capital campaigns a while back and can send it if you need

Looks a little something like this.  Add a campaign variable and it becomes a fun little report

SELECT
sal.esal1_desc,
a.ref_no,
a.customer_no,
CONVERT(DATE,a.cont_dt) as pledge_date,
b.amt_due as pledge_payment_amount_due, b.amt_recd as pledge_payment_amount_received,
CONVERT(DATE,b.due_dt) as pledge_payment_due_date,
--CONVERT(DATE,t.trn_dt) as pledge_payment_transaction_date,
d.description as pledge_status,
CAST(a.recd_amt as money) as total_amount_received,
CAST(a.cont_amt as money) as original_pledge_amount,
a.cont_amt - a.recd_amt as pledge_balance,
g.description as campaign_description,
h.description as appeal_description,
i.description as fund_description,
e.street1 + ' ' + ISNULL(e.street2, '') + ' ' + e.city + ' ' + e.state + ' ' + e.postal_code as address,
f.address as email

FROM T_CONTRIBUTION a
JOIN T_SCHEDULE b on a.ref_no = b.ref_no
JOIN T_CUSTOMER c on a.customer_no = c.customer_no
LEFT JOIN TR_PLEDGE_STATUS d on a.pledge_status = d.id
JOIN T_ADDRESS e on c.customer_no = e.customer_no and primary_ind = 'Y'
LEFT JOIN T_EADDRESS f on c.customer_no = f.customer_no and f.primary_ind = 'Y'
JOIN T_CAMPAIGN g on a.campaign_no = g.campaign_no
JOIN T_APPEAL h on a.appeal_no = h.appeal_no
JOIN T_FUND i on a.fund_no = i.fund_no
Left Join TX_CUST_SAL sal on c.customer_no = sal.customer_no
WHERE a.cont_type = 'P'
and a.recd_amt <> a.cont_amt
and a.campaign_no = --insert your campaign parameter here

Stumbling upon this as I'm working to create a dashboard where we can potentially monitor pledge payments. I'm interested to understand how you were able to get the contribution date to filter and become the payment dates.

Transaction date is in the Finance Folder.  This probably has more info than you are after but have a go at this Pledges.dash

It pays to search around!  I have been looking for the Pledge Payment Date for a bit and found this email string from a couple of years ago.  Your dashboard pointed me in the right direction and I now have visibility to this elusive date.  Thanks, Heath!

I have a pledge payment schedule SSRS report floating around here somewhere as well if that's of use :)

Thank you for highlighting that SQL script in the conversation above. That is really useful!

Full SSRS report is here https://www.tessituranetwork.com/en/Files/Shared-Reports/Pledge-Schedule-Balance-Report

Thank you so much for sharing this report! It's exactly what we need and will help us tremendously. I passed the files on to a colleague to get the report set up, and he wanted me to ask if you have an XML file to accompany the others that you provided.

Give this a crack.

or pop me an email and I'll bundle up the 3 (SQL, RDL, XML)

  SDC_Pledge_Schedule_Balance.XML