Going Beyond Native Roll-Up Summary Fields

Are you running into limitations with the native Roll Up Summary fields that Salesforce provides? Do you want to include dynamic criteria in a Roll Up Summary field’s filter? Have you been trying to create a report like ‘Accounts with more than 5 open Cases per month‘ with no native workaround whatsoever? Have you voted on the 8 year old idea to no avail? If yes, then I have the perfect solution for you – The Declarative Roll Up Summary Tool developed by Andrew Fawcett (a Salesforce MVP) which lets you use dynamic filter criteria like relative dates (TODAY, THIS_MONTH etc.), create Roll Up Summary Fields for objects connected via a lookup relationship, perform scheduled calculations, use Developer API to call out the Rollup Engine and embed calculations in existing Apex Triggers, perform operations like Count Distinct, Concatenate, Concatenate Distinct, First and Last and provides support for the Process Builder! Isn’t that too much awesomeness in one single tool?

I have used the Declarative Roll Up Summary Tool (DLRS) a ton of times to accomplish requirements that couldn’t be met using the native Roll Up Summary (RUS) fields in Salesforce and as you must have guessed by now – without writing a single line of code (unless you want to refer to a partial SOQL query as code). Being active on the success community makes me aware of scenarios that are most common among multiple other use cases that cannot be met using the native RUS fields. I see at least 1-2 questions everyday concerning business use cases which calls for a versatile and extremely useful tool like DLRS. Based on my research about what people most frequently ask for, I am laying out the five business requirements that DLRS can prove to be useful for:

Roll up Activities to Account

Use Case : Find the number of Open tasks at the Account level set to ‘High’ Priority
Success community question : How can I count activities for a lead that meet certain criteria?
Lookup Rollup Summaries record setup :

High priority Open tasks
Roll up Notes to Lead

Use Case : Find the number of notes at the Lead level that contain the word ‘Critical’ in their title
Success community question : How to count the number of notes and attachments with a record?
Lookup Rollup Summaries record setup :

Notes marked Critical

Note: For counting Notes and Attachments both, you can create another Roll Up field with Attachment as the Child object and then add up the Notes and Attachments rollup fields, if needed. Unfortunately, Salesforce doesn’t let you create triggers on the NoteAndAttachment object so you can’t create a consolidated rollup field to include both.
Roll up Cases to Account

Use Case : Create a report that shows only those Accounts that have more than 5 open cases in the current month
Success community question : Filtering a Report
Lookup Rollup Summaries record setup :

Open Cases this month

Note: Once you have done the above, you can then create a report to show Accounts that have more than a certain number of Open Cases per month. You will need to set the Calculation Mode to ‘Scheduled‘ in order to re-calculate bulk records in a batch.
Roll up Contacts to Account

Use Case : Allow a user to edit a custom field on an Account only if no Contacts exist for the Account OR all Contacts for this Account are inactive
Success community question : Validation rule using child records
Lookup Rollup Summaries record setup :

Number of Inactive Contacts

Total Contacts

Note: This is what the validation rule will look like:
```
AND(
   ISCHANGED(Primary_Domain__c),
   OR( 
      Total_Contact_Count__c= 0, 
      Inactive_Contact_Count__c = Total_Contact_Count__c
   ) 
)
```
Roll up Opportunities to Account

Use Case : Find the number of Opportunities at the Account level that were set to Closed Won today
Success Community Question : I couldn’t find the question that I recently replied to but trust me, there was one!
Lookup Rollup Summaries record setup :

Today’s Closed Won Opportunities

Note: Yet again, the use of a ‘Scheduled‘ Calculation Mode is called for to run a batch update job every night.

So install this incredibly useful package in your Salesforce instance and start reaping the benefits of instant rollup analytics that are an integral part of almost every organization’s data! If you have used this tool for scenarios other than the ones I listed above, please feel free to share them below with a comment.

This Post Has 10 Comments

Balaji Srinivasan January 25, 2016 Reply

Hi Mayank, in the validation rule, why do you have this condition? ISCHANGED(Primary_Domain__c) — Wasn’t able to relate to the original qstn.
1. Mayank January 25, 2016 Reply
  
  Balaji, the question (click on the success community link) starts with the following statement:
  “We have a custom field called Primary Domain on the Account object. My boss wants me to write a VR that would allow users to edit this field ONLY if….”
  That is why it is a part of the validation rule.
  1. Balaji Srinivasan January 25, 2016 Reply
    
    Gotcha !!! Thank you.
Kelly Armenta February 12, 2016 Reply

Hello Mayank,

If I want to use this with relative date fields, (LAST QUARTER) should I set the Calculation Mode to ‘Scheduled’? One issue I had with this ‘LAST QUARTER’ scenario was that the roll-up worked fine all quarter, but when the quarter changed, the data in the roll-up was not updated–it kept referring to what would then be two quarters ago. If I recalculated the roll-up, it would update to last quarter.

Thanks,

Kelly
1. Mayank February 15, 2016 Reply
  
  Hey Kelly, this is where the ‘Schedule Calculate’ button comes to the rescue. Use it to recalculate records every night. You can find more info here: http://andyinthecloud.com/
  1. Kelly Armenta February 15, 2016 Reply
    
    This is great! Thank you for getting back to me.
Passage Technology March 4, 2016 Reply

Thanks for putting this together Mayank! Great use cases! Don’t forget about another great product, Rollup Helper. Rollup Helper allows you to do all the rollups described in this great blog post + more. The product is also supported. Rollup Helper is also available on the AppExchange here: https://appexchange.salesforce.com/listingDetail?listingId=a0N30000009i3UpEAI There is a free version and paid version depending on your needs.
Ron September 25, 2016 Reply

Hi Mayank
I’m trying to do exactly what you wrote on the first point (Roll up Activities to Account), but instead of Account–> Lead:Roll up Activities to Lead. But the Declarative didn’t recognize “Leadid” as a relationship field. Can you help me with that?

Thanks!!
1. Mayank October 29, 2016 Reply
  
  For Lead, you would have to use WhoId. Contacts and Leads are referred to as Who ID’s for Activities.
John Kline November 8, 2017 Reply

thanks for this great info. How do I install this thing? I’m not very techie, are some instructions somewhere?