Need gift amounts and dates

Graham Getty:

Dawn,



I've built comparison reports like this before. It's a bit tricky given all the moving parts. For what it's worth, I'd recommend the following:



1. Build a single GIFT EXPORT to export all the gifts you want to compare in one Blackbaud Report Writer database export (.MDB file). The gift export should definitely include the Gift Amount under the related FUNDS section in order to account for any split gift amounts in Raiser's Edge (otherwise you risk inflated totals). Here's a screenshot of a sample gift export that I'd recommend:







2. In Crystal build a formula for each fiscal year you want to compare. Here's an example of one I built last year where "Current Fiscal Year" refers to gifts with dates from July 1, 2014 to May 31, 2015. It uses FASB standards to exclude payments and give full amount of pledges:



// Current Fiscal Year Total Amount

// FASB Standards - no pledge payments, full amount of pledge, hard credit, no matching gifts



// CRITERIA:



// Group by Fund Category (Unrestricted, Temp Restricted, Permanently Restricted) - handled

// by separate grouping formula in report.



// Only gifts included in current fiscal year (handled by formula in report).



// Hard-credit gifts only (handled by formula in report).



// No pledge payments (handled by formula in report).



// Full amount of pledges (handled by gift export from RE).



// No Gifts-in-Kind (handled by gift export from RE).



// No gift types of Other (handled by gift export from RE).



// NOTES:



// 1. {GfFnds_1.GfFnds_1_Amount} calculated instead of {Gf.Gf_Amount} to account

// for Raiser's Edge split gift functionality used when processing gifts.



// 2. Fiscal year date range will need to be adjusted annually or

// replaced by a parameter.



// BEGIN FORMULA



// remove all payments



if inStr({Gf.Gf_Type},"Pay")<> 0 then

    0

 else



 // remove matching gifts constituent recipients



if inStr({Gf.Gf_SCMGFlag}, "Constituent") <> 0 then

    0

else



// remove soft-credit recipients



if inStr({Gf.Gf_SCMGFlag}, "Soft") <> 0 then

    0

else





// remove gifts not in Current Fiscal Year



if not ( {Gf.Gf_Date} in DateTime (2014, 07, 01, 00, 00, 00) to DateTime (2015, 05, 31, 23, 59, 59) ) then

    0



else



// use {GfFnds_1.GfFnds_1_Amount} for accurate totals due to split gift entry in RE



{GfFnds_1.GfFnds_1_Amount}





// END FORMULA



3. Build as many other formulas for as many other date ranges you need. Insert these formulas into the details section of your report.



4. Insert groups as needed (Fund Category, Fund Description, Constituent SortKey).



5. Insert Summary for all groups for formulas you created for discrete fiscal years.



6. Suppress details section.



7. Voila



If you run into trouble, the Crystal Support Team at Blackbaud is awesome. They won't build anything for you, but if you put together a report, they will definitely help you through the trickier parts.



Ron Carmical (Blackbaud employee on Crystal Support Team) is amazing. I think he's forgotten more about Crystal than I'll ever know about anything.



Good luck with your reports!



Graham,

This helped a lot-----now I'd love to find a way to get monthly comparisons of gifts side by side, with the years in adjacent columns, but I either get them to not line up Jan - Jan - Jan, (they drop a level for each year), or if I set parameters trying to compare just say 6 months, once I do the second year, it overwrites the first year's parameters.  I did go a different route and do this in a BB Pivot Report, but then I can't get the comparisons of dollar changes and % changes my boss is looking for.  She wants something set that she can run herself at will-----thanks for letting me vent!