SIA - Soft Credits and Gift Splits

I know it is late on a Friday and most people may lose this in their weekend emails, but I have to put this down before I forget.

I am working on end of year reporting for FY23. After trying multiple report types, queries, exports, etc., I have landed on the Gift Detail and Summary Report. I used the criteria below removing the redundant sections we don't use:

Records to include: All

Include records with these Dates: Last fiscal yr (7/1/2022 to 6/30/2023)

Date to use: GL post date

Soft credit option: Recipients, Use distribution on gift

Include these constituents: Inactive = Yes; Deceased = Yes; Constituents with no valid addresses = Yes

Gift types to include for column Donations: Cash, Recurring Gift Pay-Cash, Pledge, MG Pledge, Stock/Property, Stock/Property (Sold), Gift-in-Kind, Other

Campaigns to include: All

Funds to include: All

Appeals to include: All

Constituent Codes to include: All

Include Balance brought forward: No

Once I pulled the detail report, I exported it in CSV and formated it, yada, yada. When checking it against some gifts (Trust and Verify), I noticed something strange. A little background: being a Feeding America (FA) food bank, we get disbursals from national campaigns as part of cause marketing conducted across the country. This comes via an ACH from FA in one lump sum. This means we create gift splits to match the funds to specific CFAP(Campaign, Fund, Appeal, Package) and then add in Soft Credits with the total disbursement from each entity. For some disbursements, this can be 15+ soft credits! On that file, the report takes the percentage of each split and applies it to multiple entry rows for each constituent. It's a mess! For one gift, I have 38 rows... ?The total for each constituent still equals their total on the record, thankfully, as does the appeal and campaign totals.

To find total number of gifts, I can use a Data Model from a Pivot to find Distinct Counts of the Gift IDs. For total giving for CFA, I can be confident in that those match up. For total giving by constituent, I can be confident on that as well.

To get to the main point of this post, this really just makes me realize that for total constituent giving by CFAP, this might not ever be possible. Since soft credits and gift splits don't talk, I won't be able to say ABC, Co has given $XXX to Campaign A. It makes me dread the rise of Benevity, Fidelity Charitable, BB Giving Fund and their like. We can have multiple corporations contained on a single disbursement giving to different campaigns and it will be a mess trying to get the totals off of that. Am I going crazy or is it just the end of the week working on an audit for three days straight? ?