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!

James Andrews

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!

There is actually a KB article on this very topic: look up #BB428895.

Sadly according to that solution, the only way to make it work is by entering your split gifts as separate gifts.

I'm not entirely sure I believe that; I would think there's some way to aggregate your funds and appeals in a formula, and group on that... but I don't have any gifts that complicated to test it on right now.

James Andrews

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!

I should clarify that this is a result of the export function in RE, not the setup of the database itself. We do have a few split gifts in RE, and I just queried for them on the back end of the DB. What came out was indeed a list of the individual splits, each with its own combination of campaign, fund, and appeal.

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

James Andrews:
I should clarify that this is a result of the export function in RE, not the setup of the database itself. We do have a few split gifts in RE, and I just queried for them on the back end of the DB. What came out was indeed a list of the individual splits, each with its own combination of campaign, fund, and appeal.

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...

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 . . .

James Andrews

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 . . .

Ah, I didn't realize that was a Crystal-only KB solution. Well, if it makes you feel any better, it wasn't really a solution so much as a "it doesn't work that way" answer.

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.

Joshua Bekerman bCLO bCRE

James Andrews:
Ah, I didn't realize that was a Crystal-only KB solution. Well, if it makes you feel any better, it wasn't really a solution so much as a "it doesn't work that way" answer.

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.

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.

 

James Andrews

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.

 

Oh that's pretty cool, I didn't really even realize that functionality existed in import. You learn something new every day!

Helen Wieger

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.

 

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.

James Andrews

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.

To clarify, I did know about the 'create an import file' function of Import. I just didn't realize there was one specifically for split gifts. That's a really great workaround, even though it means you can't use the custom reports function in RE.

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

James Andrews:
To clarify, I did know about the 'create an import file' function of Import. I just didn't realize there was one specifically for split gifts. That's a really great workaround, even though it means you can't use the custom reports function in RE.

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.

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?

James Andrews

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?

Sure - Database menu, Database Expert, and just add a new connection. You can have multiple connections in there, and you pick and choose tables from whichever data source you need to.

Helen Wieger

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?

In Crystal Reports, go to Database-->Database Expert on the menu. On the data tab, using "Create New Connection" you can add tables from multiple databases. Then go over to the links tab and set the links. You will probably want to link the two databases through Gift ID or Gift Import ID since those are unique identifiers for gifts. Set the link type to Left Outer Join, since not every gift will have split gift information. Click OK, and you should be good to go.