Opportunity Roll-Up Summary Recommendations
Salesforce released Opportunity Roll-Up Summary fields on the Account object in the Spring 08 release. This is a pretty useful feature and below are some recommendations to help you get started using them.
One of the major benefits of this feature is that you now get quick answers on the Accounts you are selling to without having to run an Opportunity report and get multiple records for the same Account. If you sell to a customer multiple times throughout the year, that customer will be represented once in an Account report, but will be represented multiple times (once for each sale) in an Opportunity report.
Create the Fields
To get started, create a new Account field and choose the Roll-Up Summary option and click Next.
Next, give it a name. I’d like to recommend a naming convention because you will probably create multiple of these. My personal naming convention is used in the example fields below. For the API name, you should also use a naming convention. This will help keep these fields together when viewed in Eclipse or Explorer.
After clicking Next, your options are to COUNT, SUM, MIN, MAX information from the Opportunity. Below is a table with some ideas for fields you that might be useful:
| Field Name | Rollup Type | Field to Aggregate | Filter Criteria |
| $ Opportunities (Open) | SUM | Amount | Closed = False |
| $ Opportunities (Open – This Year) | SUM | Amount | 1) Closed = False 2) Close Date = THIS YEAR 1 |
| $ Opportunities (Won) | SUM | Amount | Won = True |
| $ Opportunities (Lost) | SUM | Amount | 1) Won = False 2) Closed = True |
| # Opportunities (Open) | COUNT | N/A | Closed = False |
| # Opportunities (Open – This Year) | COUNT | N/A | 1) Closed = False 2) Close Date = THIS YEAR |
| # Opportunities (Won) | COUNT | N/A | Won = True |
| # Opportunities (Lost) | COUNT | N/A | 1) Won = False 2) Closed=True |
Using Formulas on the Rollups
One really nice thing about these Rollup fields is that you can use their results in your regular formula fields. Using the rollup fields I mentioned above, we can use formula fields on the account to create the fields such as:
- Win Rate (%) = # Opportunities (Won) / (# Opportunities (Won) + # Opportunities (Lost))
- Avg Win Amount = $ Opportunities (Won) / # Opportunities (Won)
Using the Roll-Ups
Now those rollup fields can come in handy in reporting to do things like:
- Filter for all sold-to Accounts: $ Opportunities (Won) > 0
- Filter for all Accounts with Open Opportunities: # Opportunities (Open – This Year) > 0
Another possiblity is in, say, an application that allows you to map your Salesforce.com data. Adding the $ Opportunities (Won) field onto the query page makes generating a map of all sold-to accounts a breeze.
What about you? How are you using Roll-Up Summary fields?
1 [back] – Other date filters include THIS QUARTER, THIS MONTH, LAST MONTH, NEXT YEAR, NEXT QUARTER, etc. Refer to the Salesforce help for more. These date filters are particularly useful for rollup summary fields.
Joe Said,
March 11, 2008 @ 8:30 am
This guide is great. However, the date filters like THIS YEAR, THIS MONTH etc do not work in Account – Opportunity roll up summary fields. The error message Error: Invalid date (Valid date format 11/03/2008) is received in our PE instance.
Thanks
Joe
Scott Hemmeter Said,
March 11, 2008 @ 11:07 am
@Joe:
D’oh! I added those date recommendations without testing them. I took for granted that they’d work. As it turns out, Salesforce doesn’t re-use the filtering functionality everywhere, so there is different functionality in different places.
Sorry about that.
The workaround to this would be to create some formula fields that result in TRUE or FALSE to calculate whether the Close Date is in the current year, current quarter, etc. Then use those formula fields in the Roll-Up Summary fields.
Hey Salesforce, how about making those date filters available in Roll-Up summary fields? I’ll post an idea.
Stefan Said,
March 17, 2008 @ 8:12 am
Unfortunately the workaround does not seem to work either
Scott Hemmeter Said,
March 17, 2008 @ 8:15 am
@Stefan:
Ugh. That stinks.
I guess my lesson learned here is to not assume something will work just because it works in other places of Salesforce.
CRMZepher Said,
July 4, 2008 @ 11:53 am
Hello,
Great advice and tutorial . . just one question . . is there a difference between PE and EE with this feature? I can´t get the AMOUNT field to appear in the Field to Aggregate column. I have the PE edition
Scott Hemmeter Said,
July 5, 2008 @ 8:13 am
I tried it in PE and I can see the Amount field available. Not sure why you can’t. Make sure you are selecting Opportunities as the object to roll up and then the SUM/MAX/MIN option.
Georgie Rebane Said,
November 10, 2008 @ 11:44 am
I also use PE and cannot see the Amount field or any of my custom fields that are currency fields.
Scott Hemmeter Said,
November 10, 2008 @ 7:06 pm
@George, I wonder if there is an issue if multi-currency is enabled. Do you use multiple currencies?
Tom C Said,
November 12, 2008 @ 11:10 am
We use PE, and had a “sum of all opps” working splendidly in our accounts. Then one day, the little sand timer icon showed up and never went away, and now when I try to recreate the same roll up summary I have the same issue — in Field to Aggregate, Amount is not a choice. We do use multiple currencies here. My only guess is that when Winter ’09 was released it broke our implementation.
Tom C Said,
November 12, 2008 @ 11:38 am
I think I figured out the problem. Our organization has Advanced Currency Management enabled, and buried in salesforce’s help pages, I found that opportunity amount roll up sums will not work when this is enabled. So it’s not Winter ’09’s fault, it’s that we enabled Advanced Currency Management at some point in the past month or so, and it broke our roll up summary field.
pankaj Said,
June 10, 2010 @ 11:48 pm
The Roll up summary is disabled in both developer and sales force edition…
How to enable it…any ideas??