Reports in Salesforce are easy to build, easier to filter, and really annoying to update if you want to create arbitrary groupings of accounts or contacts. One trick that helps improve almost any Salesforce report is the row-level formula, a special kind of Salesforce formula field that uses other existing fields in the report to create its logic.
Salesforce describes it as a calculation that takes place on every row of the report. Because it’s a temporary calculation that happens outside of the data model, row-level formulas are pretty flexible and don’t require changes to the underlying objects.
There’s one important thing to remember. Reports may contain only a single row level formula in each report, so make it count.
Why use row-level formulas?
Row-level formulas let you display a value that’s not in your data model for data analysis. This makes it possible for you to iterate quickly instead of creating custom fields that are essentially one-time use fields.
With Row-level formulas, you create logic that makes it easier for you to build semi-custom reports without building a custom report type.
What can you do with a row-level formula?
Here are a few ideas for you to try in your Salesforce reports. They are grouped into three types of formulas:
-
Ratio calculations – these divide one field by another or create complicated averaging or max field values that are not limited to a single field
-
Date difference – compare two date or date time fields and perform a date calculation
-
Multiple field comparison – use a combination of tests against multiple fields to create a temporary segment
Each of these strategies lets you solve a different problem, though you can combine them to create more complex solutions.
Don’t forget – you only get one row-level formula per report, so be mindful of when you want to use it directly and when you want to use it to drive the logic of other existing fields.
Examples of field level formulas as ratios
Salesforce makes it easy to get an SUM, AVERAGE, MEDIAN, MIN, and MAX field for a number value. But what if you wanted to get a ratio based on another field, such as “MRR/User”? If you have a mrr_c field and num_users__c field on your account, it’s as simple as using the calculation mrr_c / num_users__c to get the value.
Perhaps you want to extend on this idea to segment your accounts based on this ratio. You could use the formula to bucket these based on the ratio into a value that would sort nicely:
SWITCH(
TRUE,
MRR__c / num_users__c > 500, "1 - Best",
MRR__c / num_users__c > 250, "2 - Good",
MRR__c / num_users__c > 100, "3 - Ok",
"4 - Low Value" )
It’s true you could use a similar method for a bucket field to sort these accounts, but you’d need an extra formula field to calculate the ratio before grouping the accounts, making row-level formula fields an excellent first step to determining the shape of data in Salesforce reports.
Using a date difference to drive urgency
Another typical way to use row-level formulas in Salesforce reports is to call attention to accounts that need attention.
For example, you might want to determine the number of days since the last updated value, like the number of days since an account was qualified:
TODAY() - Account.MQL_Date__c
Or you want to know if an account has had any updates in the last 30 days:
IF(LAST_N_DAYS:30 > MAX(ActivityDate__c, NULL), "No Recent Activity", "Active")
Once you have this value, you can also use filters in the report to show records that need attention, e.g. when using the formula above, accounts with “no recent activity”.
Another common use for this date difference is to calculate mid sales cycle differences when you date stamp your accounts or opportunities, e.g. days from stage 1 to stage 2.
Multiple Field Comparison in Row-Level Formulas
Finally, a technique that gives you the utmost flexibility is to use a row-level formula in Salesforce for text manipulation. Use this tactic to find the directory in a first visit attribution URL; to extract an ID from a URL, or to perform a kind of fuzzy match on a string.
This is a complex comparison that goes beyond a bucket-field grouping and lets you run custom rules based on the value of several different fields. You can use this to drive a simple account score that aggregates information, or simply use it to create a true/false switch in your report for other grouping.
Another technique that works in many BI tools, but not in Salesforce
Here’s a bonus technique that would be great if it worked better in Salesforce. You can combine text fields to create a dynamic url to cut and paste into a browser, but HYPERLINK(“link”, “link name”)fails to work to create a clickable URL from a report, even though Salesforce will recognize it as valid syntax. (I guess you’ll have to use a URL formula field on the regular object layout for that one.)
What’s the takeaway? Row-level formulas are a useful tool in the Salesforce reporting toolkit. They make it possible to calculate ratios and scores or to bucket records based on multiple field comparisons, making them more versatile than standard SFDC reporting.
