Fund reporting troubles due to split gifts

Antanella Tirone 2

Hello everyone!

I was recently tasked with preparing a number of reports for a board member, and I've found myself struggling with a fund breakdown. He wanted a monthly breakdown of how much we received in our Annual Fund for the last 5 fiscal years. It seemed easy enough, but I've run into a number of problems that have just completely stumped me, so I thought I'd post here to see if anyone has any insights or suggestions for me.

My first thought was to use a Fund Comparison Summary report and filter it down to the specific fund + applicable gift types and then (since it only shows 8 reporting periods), pull the numbers into a speadsheet. However, when I add the monthly amounts together, it gives me a completely different amount than what Raiser's Edge has. (ie, my spreadsheet says we received a total of $75,000 for the fiscal year after adding each month together, but the report says it should be $100,000).

When using the Fund Performance Analysis report with the exact same filters, it brings me the same total amount as the Fund Comparison Summary report for that fiscal year. Next I tried instead using a fund query, however those numbers are largely inaccurate due to various gifts being split between multiple funds. I know that there is a field for ‘fund split amount’ in the query, but from what I understand, technically all gifts are considered ‘split’, just that most of them are only split one way. When I tried applying that specific field into the output, per a Knowledgeable article I found, it pulled 17,000+ results which was not particularly helpful.

So I guess my question is, what would the best option be to try and pull this monthly breakdown? Is there a way to build in a query criteria to only pull the amounts designated for the specific fund, or is that something that only be achieved in the reports? And if it is only possible via reports, what can I do to make sure I'm getting accurate amounts beyond the filters I'm currently using?

Thank you!

Edit June 18: Thank you so much to everyone who commented on this with advice / insight! The particular monthly breakdown report I was initially looking for ended up needing to be done manually using NXT Gift lists. I set the list parameters to the same filters I needed, exported it into a .csv file, and then scrolled through to manually update the gift amounts for those with multiple funds. Then I did an auto-sum formula for each month and took the data that way.

After I had all the information I needed, I do go through and check to see how these numbers matched up to the numbers in the reports / queries, and I was very surprised to see that the fund query I built actually had the right numbers! I must have misunderstood the Knowledgebase article stating that queries would not accurately split the gift accordingly. Definitely good to know for future reports.

Dariel Dixon 2

@Antanella Tirone I think the issue is even though your query is looking at the gift splits, the report will not. I think you would be better served in this case to just manually export the data yourself and manipulate it in a spreadsheet and create a table.

It's going to be tough, because split gifts can cause a lot of issues like this. But there's benefits to them as well. If you need to be super specific, I think you're going to want to export the data yourself and consider creating your own report. I would imagine you could filter out all of the non-annual fund gifts out and run the totals from there if you export the fund split fields.

Alex Wong

@Antanella Tirone
I do not use the canned report on RE database view, so can't speak to it. It may also depends on your soft credit option selection.

I normally would just create a gift query and export from there (not using Export module, but export directly from gift query).

If you do not need to know who gave what, then in a gift query:

• Filter
  • Fund ID (or description) is ONE OF all the fund you want to report on for the last X # of years)
  • gift type of your selection
• Output
  • Gift ID
  • Gift Date
  • Gift Type
  • Fund ID
  • Fund Description
  • Fund Split Amount
  • Campaign ID
  • Appeal ID
  • Package ID
• Gift Processing Option
  • Gift to Direct Credit only (first option)

After export directly from the query, in excel, use the remove duplicate (this is just in case you selected any other output that may be a ONE-to-MANY relationship to a gift record) and check off the Gift ID, Campaign ID, Fund ID, Appeal ID, and Package ID. If you ONLY have the above output, then you should not get any message from Excel saying duplicates removed.

Then you can do a pivit table on Fund ID (or description) and use the giftdate's year as column and sum of Fund Split Amount as value.

This pivit table will allow you to see if any “anonmly” in the fund usage over years (are there any gift that has the 2022 annual fund, but with gift date in 2023 or 2021, etc).

I know a lot of org does it (in campaign or fund), but I don't do fund per year nor campaign per year as annual campaign/fund. annual or not, the gift date determines that already.

Antanella Tirone 2

@Dariel Dixon

Hi Dariel! That is exactly what I was afraid it would come down to haha. I spoke with my supervisor about it, and he said that in the past, that was exactly how he had done it, so I'm guessing that's going to be my best option. Thank for you the reply!

Antanella Tirone 2

@Alex Wong

Thank you for the thoughtful response! I had not considered using a gift query and then exporting it, as I was trying to avoid relying on Excel, but it seems that this might be my best bet. I sat down with my supervisor and he showed me how he pulled it for earlier board reports via an NXT gift list, and his process was almost identical to this. He didn't use a pivot table, and instead just manually changed any gift amount that had two or more funds applied to it, but I'll give this a try and see if it's any easier.

Joe Moretti 3

@Antanella Tirone IF you are looking for totals use any of the canned reports in database view, they all will report on split gifts and give correct totals. So if you have a split gift of $100, $75 goimh to Fund A and $25 going to Fund B, it will produce the correct information for you. If you are looking for various years, go with The Demographic and Statistical Reports/Comparisons and Summaries, where you can choose up to five time periods. Doing a gift query is too time consuming and then you have to make sure in RE EXPORT where you attach the query to make sure all the splits are in the output - fund, appeal, campaign - depending on what you are reporting on. Make life easy and go with the canned reports, I use them all the time for perfect totals.

Antanella Tirone 2

@Joe Moretti

Hi Joe! Thank you so much for the suggestion. I think, for reports looking at 12 months, this would not be particularly helpful due to the limitation on reporting periods, but it's proving to be very helpful with some other reports I still needed to work on! Will definitely be saving this for future use.