nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Pledge Projections

Christie Cotcamp

Hello! I would like to be able to report on future projections of pledges from one fund. The end result should include the donor's pledge balance, last installment, total attributed to that particular pledge, future installments for 2020, 2021, etc. I've spent a lot of time in Mail and using the Reminder option because I liked how the fields I want are an option for an export, but the trouble is that it will only pull donors with the reminders box checked and not all of our donors wish to have reminders. I've also spend a lot of time in the Cash Flow Report, which was helpful for projections but again I would like more information---total attributed to the pledge, etc.

I've been going back and forth with customer support and they say that this project cannot be done with just a query or export but I have to compare the Cash Flow Report to my query, which is very time consuming. I'm at a roadblock. Anyone have any great ideas? Thank you!!

open-pledge-report.pdf

Find more posts tagged with

Query Import Export

Comments

Patrick Clancy

Hi Christie

I've been trying to replicate an existing Pledge Projection report with a query with very little success.

Like you, I'd like to be able to export the results of a query that shows all Pledge (and Recurring Gift) payments within a timeframe (Eg, next 60 months).

All I can seem to do is either export everything where the next payment is due in the projected reporting period, or export the detail report which is a horrifically formatted mess.

It seems that I need to figure out which field shows "Installment Payment Date" or suchlike, and then in report criteria set something along the lines of "Installment Date Between 01/08/2020 and 01/08/2025", but if this was possible I'm sure that it would have come up in your discussions with customer support?

EDIT:

Further tinkering this afternoon, and I've created a Gift Query with the following criteria:

fbf5f7e5866491fb24478ec2298c25ef-huge-an

Which I would imagine is going to show me all Pledge and RG payments with a gift date of this month and installments due between now and 2025 (or blank), but I don't think this actually fits the needs for what you or I are trying to do and needs refining.

Christie Cotcamp

Yes, similar issue. I've played around with Installment Date Due (output) and tried to come up with something that I could separate with a time frame but also pretty time consuming. Talking with customer support, they pretty much explained that I can't have a combination of the Cash Flow Report and an export of information. They made it clear that the criteria that I'm looking for or combination does not exist. Thanks for your examples!

Christine Cooke

write a query that is Pledge Balance is equal to or greater than $.01

Go to Export and build the export with all the of the pledge fields you want to see info on and pull that query through the Export.

the Pledge Status Report and the Cash Flow report have most if not all of what you want, though it sounds like you want all of their remaining installments listed. That will make for a very big report so be prepared that you will have a lot of info to cull through.

BTW Query is a grouping tool not a reporting tool 99% of the time.

Patrick Clancy

That's excellent, thank you Christine.

I'm not going to be battling through the report, I'm going to let PowerBI do that ?

Christie Cotcamp

Already came to this conclusion and was looking for a different solution, thanks anyway.

Dan Snyder

Christie Cotcamp‍ and Patrick Clancy‍,

Attached is an open pledge report I created in Power BI. While I did use the export from the Mail Reminder report, I am sure you could easily follow Christine Cooke's advice and create this from an export.

The last page of this report is just a list formatted for sharing with our gift officers so they can say yay or nay to sending the pledge reminder as they may have other plans for reminding their prospects.

open-pledge-report.pdf

Elizabeth Rich

I have a query and export combination where I can pull installments of pledges with balance based on fund type. For example, I pull all unrestricted pledges for the fiscal year with all of their installments. My query is a gift query with just Fund Category equals Unrestricted And Gift Pledge Balance greater than $0.00. My gift export in excel has all of the relevant fields I need for gift amount, type, subtype, campaign, fund, pledge balance, Constituent name, Funds, and installments. You can only pull 99 installments, so if you have more, you will need to do a little manual work on your report. If this sounds helpful, I'd gladly assist further.