Problem
For strategic reporting on a given Account Manager’s progress in building relationships in their Accounts, I need to be able to group on the number of contacts each account manager has established on their owned accounts. For example, how many Accounts does a User have with either ‘Only 1 contact’, ‘Between 2 and 4 contacts’, or ‘4 or more’ contacts.
Solution
In Salesforce, you can use bucketing in reports to categorize your data, however, there are limitations with bucketing that you need to be aware of. One key limitation is that you can’t bucket values that are displayed as record counts in a report. Fortunately, you can create a couple of rollups to accomplish this type of aggregate reporting with Rollup Helper:
To bucket the number of contacts on accounts:
- First create a rollup that rolls up the number of contacts onto an account:
- Target object is Account.
- Choose a number field to hold your results (needs to be of numeric field type because it will be used for the filtering of 1, 2-4, or 4+ contacts in other rollups)
- The child object is Contact, relationship is AccountId, and the source field is ContactId or IsDeleted. The rollup function to use is Count. No filter is needed.
- Now create a rollup for the User:
- Target object is User
- Select a field on User to store the results.
- Then select Account as the source Object and Account.OwnerId as the relationship. Select the field that was the target field in the previous roll up target field (step 1) as the source field. We will need to create this rollup multiple times (one for each bucket) with a different filter on each. So in this case we have 3 buckets, we will need 3 rollups. One filter criteria is targetFieldFromFirstRollup = 1. The other is targetFieldFromFirstRollup > 1 AND targetFieldFromFirstRollup < 5. The last is targetFieldFromFirstRollup >= 4.
- Now, you can view these fields on the advanced detail user page layout or within a user-based report and compare your account managers.
