Salesforce report formulas can be used in reports for grouping records, calculations, or to compare record data.
In the Salesforce Report Builder, you’ll find two different formula types: Summary Formulas and Row-Level Formulas. While they sound similar, there are distinct differences between them!
Summary Formulas go across multiple records, while Row-Level Formulas go across a single record. You can think of Summary Formulas as the column-based formula (where the formula result displays at the bottom of a column), whereas the Row-Level Formula result displays on the row itself.
| Summary Formulas | Go across multiple records. | Find it at the bottom of the column. | Example: Average amount of all renewal opportunities in a report. |
|---|---|---|---|
| Row-Level Formulas | Applies to a single record. | Find it on the row itself. | Example: Check if two fields on the same record have the same value. |
Let’s take a look at Row-Level and Summary Formula examples so you can determine which formula type you would use in your Salesforce reports.
Note: When writing this article, I assumed that you are familiar with the Lightning Report Builder – this Salesforce Report tutorial will get you up to speed.
Example 1: Total Opportunity Amount
Your boss asked if you can create a report that shows Opportunity Amount total broken down by Country.
To achieve this you actually wouldn’t need a Summary Formula field (although if that was your guess you were on the right track!).
- All you need is to click on the field and then click the SUM checkbox.
- If you include a grouping based on Country, this will yield in a summary line in the report:
Example 2: Average Monthly Case Volume for a Specific Product
You have been asked by the support team to generate a report that shows the monthly average of Cases for a specific Product in the last year.
In this case, you would need to use a Summary Formula.
- Before you can make the Summary Formula, make sure that you have the Product field as a ‘Group By Row’ field.
- Click on the drop-down next to Columns and select Summary Formula.
- To create the formula, we are going to take the Record Count (the total records for the whole year), divided by 12 (the number of months in a year):
- Don’t forget to give the column a name. In this scenario, the column name is “Monthly Avg Cases”. With that, you are all set to run the report!
Example 3: Checking Field Value Matches
You have been tasked with verifying that at the Case level the Billing State and the Shipping State of the Account for the Case matches.
With Row-Level formulas, you can now solve this instantly without creating a new field on the Account.
- Create your Case report and then select “Add Row-Level Formula” from the Columns drop-down:
- Once in the editor, we will create an IF statement that compares the Billing State to the Shipping State, resulting in either a 0 (for not matching) or a 1 (for matching):
Tip: Use the Fields and Functions menu on the left-hand side to ensure that you are pulling in the correct formatting.
- Once you are completely satisfied with your formula you can give the Column a name and hit Apply.
- In the report, you will be able to easily identify the misaligned States as well as the filter on the field. There you have it – a simple Row-Level formula!
Summary
There are some considerations to keep in mind when it comes to Row-Level formulas. For example, you can only have one Row-Level formula per report, and it can on reference a maximum of five fields. Also, you can’t use Row-Level formulas for cross-filters or buckets. To learn more about the restrictions check out the Salesforce documentation.
If you would like to learn more about Summary and Row-Level formulas make sure you check out the Trailhead modules on Row-Level and Summary Level formulas. Happy learning!
Comments: