Hi Experts,
We have a requirement to display the total donation amount on the top-level parent account, which includes both the parent's own donations and the donations from all child accounts
under it. This should work even if the account hierarchy has multiple levels.
- We're currently using the out-of-the-box NPSP rollup field 'Total Gifts', but it seems to only consider direct relationships, and we're unsure if it supports deep (multi-level) rollups across the entire account hierarchy.
Example Scenario:
- Parent Account 1 – Donation: 10
- Child 1 (child of Parent 1) – Donation: 20
- Child 2 (child of Child 1) – Donation: 30
- Child 3 (child of Child 2) – Donation: 40
Expected Outcome:
- Parent 1 should display a total of 100 (10 + 20 + 30 + 40)
- Child 1 should display 90 (20 + 30 + 40)
- Child 2 should display 70 (30 + 40)
- Child 3 should display 40
Can this be handled using Salesforce Reports?
Please guide me to find the best solution to implement this use case.
@Nonprofit Hub @Nonprofit Cloud @Nonprofit Release Readiness @Fundraising @Salesforce.org System Administrators @Salesforce Administrators & Developers @Salesforce.org System Administrators
The NPSP Queen has spoken
Yes, indeed 😊
I have successfully built a solution using DLRS.
The challenge is that DLRS will sum the child's values, but not the child + parent values. At each level in the hierarchy, you need to add together the children plus the parent, giving you something similar to Campaign's Value Opportunities in Hierarchy. The next parent up (when the hierarchy is three or more levels deep) needs to count this total, otherwise it will just get the totals from its own children and not its grandchildren.
To make it work with DLRS, you need two DLRS rollup definitions: one for the child values and one for the child + parent values.
Here's how I did it (I used Total Gifts, which is the dollar amount of donations attributed directly to an account):
- Create two currency fields on Account for DLRS to populate:
- Total Gifts Rollup (sum of Total Gifts of each child account)
- Total Gifts in Hierarchy Rollup (sum of formula field below)
- Create two formula fields on Account:
- Total Gifts + Children (Total Gifts + Total Gifts Rollup)
- Grand Total Gifts in Hierarchy (Total Gifts + Total Gifts in Hierarchy Rollup)
- Create two DLRS rollup definitions:
- Account: Total Gifts Rollup
- Parent Object: Account
- Child Object: Account
- Relationship Field: Parent Account
- Field to Aggregate: Total Gifts
- Aggregate Operation: Sum
- Aggregate Result Field: Total Gifts Rollup
- Account: Total Gifts in Hierarchy Rollup
- Parent Object: Account
- Child Object: Account
- Relationship Field: Parent Account
- Field to Aggregate: Total Gifts + Children
- Aggregate Operation: Sum
- Aggregate Result Field: Total Gifts in Hierarchy Rollup
- Account: Total Gifts Rollup
- Save, deploy and active the DLRS rollups then run a full calculate for each. Also schedule a full rollup daily.
You can probably come up with better field names, but the basic mechanism is there. You'll have to do all the above steps for EACH rollup field you want to calculate for the full hierarchy. Also note that "Hierarchy" in this context means "this Account and its child Accounts", not the full hierarchy going all the way from top to bottom.
I'm sure you could also do this with a record-triggered flow, triggering whenever Total Gifts changes, however, you would probably also want to create a one-off scheduled flow to retrofit all existing records.
- Create two currency fields on Account for DLRS to populate: