Summary for Fund in a query - Pledge Total ADDING Write-Offs?!?

Options
Tom Klimchak
Tom Klimchak ✭
Tenth Anniversary Facilitator 2 Name Dropper Photogenic
in Blackbaud Raiser's Edge NXT®: General Discussion
Hi... We created a fund query which shows all our funds with gifts in them since 2009.  We listed out FundID, Fund Description and a few other fields.  Then we decided to use a "Summary For Fund" field to list the "Total Given" amount...



So far so good, except when we tried to look at "committed" amount by creating a total given field which ads up Pledges, Write-Offs and Gifts.  Raiser's Edge is actually ADDING the write-off amount to the total, not subtracting it.



Example:



One fund has a $25,000 pledge in it, but that pledge has a $5,000 write off.  The total comes out as "$30,000" because it's adding the $25,000 pledge with the $5,000 write-off.  That's crazy.



Is there any way to show the TRUE value of committed amount to that fund?  The true value is $20,000 because we KNOW we'll never get the other $5,000.



You can see the same dynamic taking pledge when you open the Fund record and choose View--> Gifts.  The Fund record ADDS the write-off to the total pledge amount.



Any help would be greatly appreciated... Thank you!

 
Tagged:

Comments

  • JoAnn Strommen
    JoAnn Strommen ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic
    Will be curious to see if a user has a way to work around this.  Over the years I've spent time on several cases/chat regarding excluding pledges that have been written off from gift totals, fund totals, giving history etc.  Support didn't have any way to do it other than manually. For most recent I couldn't go with just cash/pay cash.  Need amount pledged.  To me, something that definitely needs a fix. 



    Only thought for your case is don't include write-offs in that query.  Query on just the write offs and subtract that amount.  Pain, huh?
  • Tom Klimchak
    Tom Klimchak ✭
    Tenth Anniversary Facilitator 2 Name Dropper Photogenic
    I would love to be able to create a totals field that shows the written off amount... but... that apparently can't be done, either.  I have not been able to create a query or export which will automatically figure out the total written off for any particular fund. 



    We have a lot of people here that like to "slice and dice" their data in Excel (including our VP)... It's going to be a difficult conversation when I explain to him that there's simply no way to export the true amount of a pledge that's been written off.
  • JoAnn Strommen
    JoAnn Strommen ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic

    Thomas Klimchak:

    I would love to be able to create a totals field that shows the written off amount... but... that apparently can't be done, either.  I have not been able to create a query or export which will automatically figure out the total written off for any particular fund. 



    We have a lot of people here that like to "slice and dice" their data in Excel (including our VP)... It's going to be a difficult conversation when I explain to him that there's simply no way to export the true amount of a pledge that's been written off.

    The easiest/cleanest way I've found to get a query or report of write offs is to go to Reports > Pledge and Recurring Gifts Report > Written-Off Report.  Can check to get an output query or just run the report.  Maybe that will help.  



    Not being able to exclude write-offs often leads to inflated reports for us.
  • JP Provencal
    JP Provencal ✭
    Ancient Membership Facilitator 1 Name Dropper Photogenic
    Hi Thomas,



    Wouldn't it make sense to dump the Fund Summary field in your query outputs three times?

    First sumary is filtered on Cash & Cash Equivalents you want includes (ie. Cash, Stock, GIK).

    Second summary is filtered on Pledge.

    Third summary is filtered on Write-Off.



    Yes you will have to do some work in Excel if you export the data - but you'll end up with the ablity to subrtract column 3 from column 2 to get your 'real' pledged total.
  • Tom Klimchak
    Tom Klimchak ✭
    Tenth Anniversary Facilitator 2 Name Dropper Photogenic
    Yes, I would really, really like to have three columns, but I have yet to figure out how to do that because Raiser's Edge won't allow me to list out JUST the write-off amount in a column.  I created a fund query with an output field that totals only gifts of type "Write Off" and "MG Write Off" and... it doesn't work.  Raiser's Edge just gives me $0.00 for every fund...



    It appears that Write Offs are not "true" gift transactions.  There's a lot you can't do with them. 



    So, yep, I would love to have a query or export on funds that has three columns:



    1. Gifts/Cash

    2. Pledge/MG Pledge

    3. Write Off/MG Write Off



    The problem is that Raiser's Edge won't list a value for that Column 3...  I am going to go poke around in the canned reports and then we may have to manually change some numbers in Excel.



    If that doesn't work we may try hooking a 3rd party reporting tool into RE's SQL database and go poking around to see if there's any way we can build some calculated fields on our own.  I don't think a normal "Custom" report will work because we'd be limited by the fields that we could export.



     
  • JP Provencal
    JP Provencal ✭
    Ancient Membership Facilitator 1 Name Dropper Photogenic
    Well isin't that a fun fact.



    I've got the same issues on my side.  Sorry that I assumed there was a way to do this.



    Let me know if you do figure out a good way to get this done.



     

Categories