Reporting on gifts split between multiple funds and appeals

Helen Wieger:

My organization has a rather complex budgeting system with appeals that feed multiple funds and funds that are fed by multiple appeals (never-mind the packages!)

I have been attempting to design a report that would contain the following information:

All gifts given to certain appeals or certain funds (eg. given at any 2014 Event (appeals) or to any of the budget lines typically "fed" by the events (funds))

Grouped by fund, then appeal

Subtotal for appeal within the fund

Totals for each fund.

Goal for each fund.

Running total for each appeal.

Details listing name of donor, date of donation, gift type, (maybe packages) etc.

The report would be relatively simple, were it not for split gifts. Has anyone found a way to effectively export gifts that are split between multiple funds & multiple appeals, and then report the amount with correct fund & appeal (& possibly package). If I export Fund ID and Fund Amount, and Appeal ID and Appeal Amount, I inevitably get duplicates.

Eg. Enter a gift as

FUND A, APPEAL 1, $100

FUND B, APPEAL 2, $200

If I group by fund then appeal and use the appeal amount I get APPEAL 1, $100 and APPEAL 2, $200 in both FUND A group and FUND B group - thus inflating my numbers. If I use the Fund Amount instead, then I get APPEAL 1, $100 and APPEAL 2, $100 under FUND A, and both appeals at $200 under FUND B - again inflating my numbers. If I suppress duplicates I don't know a way of keeping FUND A with APPEAL 1. The report randomly pulls some A's with 1's and some A's with 2's, etc.

Is there a way around this? (other than not entering split gifts)

Thanks!

So the problem is, when RE exports gift information, it doesn't export each of these splits - it aggregates them by fund, by campaign, and by appeal -- as, I guess, that KB solution indicates.

I still wonder if you couldn't write some complicated formulas in crystal to get around this -- if not, it seems like reporting off the back end might be your only solution...

Helen Wieger:
Thanks for the reply. I had figured it had to do with a lack of information in the export, since reports run in RE do handle split gifts accurately. Unfortunately my org doesn't have Crystal Reports support, so I can't access the KB solution you referenced. So, the question is, do I want to bother with reporting off the back end . . .

I don't know how experienced you are with reporting off the back end - I have done a minor amount of reporting from it, and in my experience if you learn a couple of the functions and views, and stay away from membership, it's not TOO too hard, especially if you're just doing straight up numbers and not stressing about soft credits and addresses and membership transactions and stuff.

Josh Bekerman:

Another work-around would be to use Import functionality of "Create an Import File" to export the Split Gift ID field that you need to make the report work the way you want.  You'll have to create multiple import files and link them in Crystal Reports.

 I've done this a couple times to get certain fields for proposal records where the fields I needed were not available through Export but were available through Import/Create Import File. 

Of course doing it this way you won't be able to run the report through Custom Reports in RE, but it might be a bit easier than going through the back-end.

 

Helen Wieger:

Thank you for that work-around! It's amazing!!! Now if Blackbaud would just make that accessible in the exports too, I would be beyond happy!

For future reference of others who read this, how to create an import file is described in BB17606. For split gifts there's a specific Split Gift category under Gifts.

We've used that utility extensively for updating batches of proposals. Until Blackbaud makes Proposal a top-level record (like Action or Gift), it's really the only way to handle it.

Michaela Humpal:
This is very similar to the problem I was running into while trying to create a report. I'm new to Crystal Reports but I figure our how to get the split gift ID out of RE through the Administration Import Export File. Would someone be able to walk me through linking multiple files in Crystal Reports?