Hi all,
Currently, I can use Output Set to return "Total Giving" and "Total Soft Credit Fiscal Year". What I would like to be able to do is return "Total Giving Soft Credit" (not by FY, but in total) and "Total Giving Including Soft Credit" (Total Giving + Total Giving Soft Credit). This is for a Batch Input file for Wealth Engine, but it would also be really helpful in general to be able to return someone's "true" Total Giving.
Has anyone come up with a solution for this already?
Best,
Daniel Baker
Hello again!
I have a SQL view for this too. :) In my files there's a zip you can download that has script to create a view called lvs_credit_cont, as well as a spreadsheet with tabs containing the system table values we used to create new list criteria and output set elements based on this view. Think of it as total giving including soft credits. I don't have "total soft credit" in there but you could pull the total including soft credits alongside the out of the box total, and then the difference between the two should be your soft credit total.
The way it's set up, if the John Doe Family Foundation gives $10,000 and it gets soft credited to John & Jane Doe, you'll get back $10,000 in giving from John & Jane Doe (plus any direct giving they've made,) and $0 from the foundation.
As you may know, it's possible to enter multiple soft credits for the same gift. If you soft credit that $10,000 to four different constituent accounts, from this view it will look like each of them gave $10,000. That may or may not be ok in your world--we never do multiple credits for the same gift so it's not an issue here.
Finally, when a gift gets adjusted or zeroed out, Tessitura doesn't automatically adjust the credit as well. It's a very easy thing a gift entry person to overlook, and we recently ended up manually adjusting several hundred mismatched soft credits that had piled up over the years. This view checks to make sure that the original contribution hasn't been zeroed out, but if the numbers mismatch otherwise you can get some weird results. Which is all to say, this has served our needs very well but I wouldn't use these numbers for any critical reporting or give them to our auditors, say.
Hopefully that's more helpful than confusing!
Just wanted to say, we've been playing around with this SQL view of yours, and we're very happy with the results so far! We've found a couple bugs which I'm currently working to delineate and work through, but the biggest/most obvious one that I'd love your opinion on is this:
Tessitura creates a Soft Credit for every Gift Membership. So with the way this view is set up, it assigns the revenue from all gift memberships to the gift recipient, not the gift giver, which is counter intuitive to say the least (our board members often buy gift memberships for their networks to go towards their give/get goals). In a perfect world, Tessitura would NOT assign a soft credit for gift memberships (or at least make this feature optional). But seeing as how that's not the case, do you have any ideas as to how to work around that using SQL to enhance this code? Maybe restrict it by Creditee Type somehow?