Salesforce Reports provide two types of formulas: row-level and summary-level. In this explanation, we will first cover row-level formulas and their uses, before moving on to summary-level formulas.
Row-Level Formula: -
Including a row-level formula in your report creates a new column that performs calculations for individual row. You can enter row-level formulas directly within the Lightning report builder.
✅ Basic Info About Row-Level Formulas
|
Feature |
Details |
|
Return Types Supported |
Number, Date, Date/Time, Text |
|
Return Type Not Supported |
Time (use Text format as a workaround) |
|
How Many Per Report |
Only 1 row-level formula allowed per report |
|
Field Reference Limit |
Max 5 unique fields (repeating the same field doesn’t count extra) |
❌ Limitations – Where You Can’t Use Row-Level Formulas
|
Limitation Area |
Not Supported |
|
Filters |
Cross filters |
|
Fields |
Buckets, Summary formulas, Other row-level formulas |
|
Field Types Not Allowed |
Boolean, Timeonly, Email, Phone, Multiselect Picklist |
|
Some Date Fields |
Due Date, Birthdate |
|
Some Text Fields |
Billing Address |
|
Currency |
Uses org default currency only; ignores report-level multi-currency |
|
Report Types |
"With or Without" types (e.g., Accounts with or without Contacts) |
|
Historical Reports |
Can create, but can’t reference historical fields |
|
Reporting Snapshots |
Not supported |
|
Joined Reports |
Not supported (remove formulas before converting to joined report) |
|
Salesforce Classic |
Not supported – can’t view, run, or edit in Classic |
|
Edit Option Location |
Must use OUTLINE panel (not Fields panel) for create/edit/delete |
|
Apex API |
Not available |
✅ Row-level formula function with descriptions
|
Function Category |
Function |
Description |
|
Date & Time |
ADDMONTHS |
Returns the date that is the indicated number of months before or after a specified date. If the specified date is the last day of the month, the resulting date is the last day of the resulting month. Otherwise, the result has the same date component as the specified date. |
|
|
DATE |
Returns a date value from the year, month, and day values that you enter. |
|
|
DATEVALUE |
Returns a date value for a date/time or text expression. |
|
|
DATETIMEVALUE |
Returns a year, month, day, and GMT time value. |
|
|
DAY |
Returns a day of the month in the form of a number from 1 through 31. |
|
|
DAYOFYEAR |
Returns the day of the calendar year in the form of a number from 1 through 366. |
|
|
FORMATDURATION |
Formats the number of seconds with optional days, or the difference between times or dateTimes as HH:MI:SS. |
|
|
FROMUNIXTIME |
Returns the date time that represents the given number as the seconds elapsed since 1 Jan 1970. |
|
|
HOUR |
Returns the local time hour value without the date in the form of a number from 1 through 24. |
|
|
ISOWEEK |
Returns the ISO 8601-week number, from 1 through 53, for the given date. |
|
|
ISOYEAR |
Returns the ISO 8601 week-numbering year in 4 digits for the given date. |
|
|
MILLISECOND |
Returns a milliseconds value in the form of a number from 0 through 999. |
|
|
MINUTE |
Returns a minute value in the form of a number from 0 through 60. |
|
|
MONTH |
Returns the month, a number from 1 (January) through 12 (December) in number format of a given date. |
|
|
NOW |
Returns a date/time representing the current moment. |
|
|
SECOND |
Returns a seconds value in the form of a number from 0 through 60. |
|
|
TIMENOW |
Returns a time value in GMT representing the current moment. |
|
|
TIMEVALUE |
Returns the time value without the date, such as business hours. |
|
|
TODAY |
Returns the current date as a date data type. |
|
|
UNIXTIMESTAMP |
Returns the number of seconds since 1 Jan 1970 for the given date, or number of seconds in the day for a time. |
|
|
WEEKDAY |
Returns the day of the week for the given date, using 1 for Sunday, 2 for Monday, through 7 for Saturday. |
|
|
YEAR |
Returns the four-digit year in number format of a given date. |
|
Logical |
AND |
Returns a TRUE response if all values are true, and returns a FALSE response if one or more values are false. |
|
|
BLANKVALUE |
Determines if an expression has a value, and returns a substitute expression if it doesn’t. |
|
|
CASE |
Checks a given expression against a series of values. |
|
|
IF |
Determines if expressions are true or false. Returns a given value if true and another value if false. |
|
|
ISBLANK |
Determines if an expression has a value, and returns TRUE if it doesn’t. |
|
|
ISNULL |
Determines if an expression is null (blank), and returns TRUE if it is. |
|
|
ISNUMBER |
Determines if a text value is a number, and returns TRUE if it is. |
|
|
NOT |
Returns FALSE for TRUE and TRUE for FALSE. |
|
|
NULLVALUE |
Determines if an expression is null (blank) and returns a substitute expression if it is. |
|
|
OR |
Determines if expressions are true or false. Returns TRUE if any expression is true. |
|
Math |
ABS |
Calculates the absolute value of a number. |
|
|
ACOS |
Returns the arc cosign of the number in radians, if the given number is from -1 through 1. |
|
|
ASIN |
Returns the arc sine of the number in radians, if the given number is from -1 through 1. |
|
|
ATAN |
Returns the arc tangent of the number in radians. |
|
|
ATAN2 |
Returns the arc tangent of the quotient of y and x in radians. |
|
|
CEILING |
Rounds a number up to the nearest integer, away from zero if negative. |
|
|
COS |
Returns the cosine of the number in radians, if the given number is from -1 through 1. |
|
|
EXP |
Returns a value for e raised to the power of a number that you specify. |
|
|
FLOOR |
Returns a number rounded down to the nearest integer, towards zero if negative. |
|
|
LN |
Returns the natural logarithm of a specified number. |
|
|
LOG |
Returns the base 10 logarithm of a number. |
|
|
MAX |
Returns the highest number from a list of numbers. |
|
|
MCEILING |
Rounds a number up to the nearest integer, towards zero if negative. |
|
|
MFLOOR |
Rounds a number down to the nearest integer, away from zero if negative. |
|
|
MIN |
Returns the lowest number from a list of numbers. |
|
|
MOD |
Returns a reminder after a number is divided by a specified divisor. |
|
|
PI |
Returns pi. |
|
|
ROUND |
Returns the nearest number to a number that you specify, constrained by a number of digits. |
|
|
SIN |
Returns the sine of the number, where the number is given in radians. |
|
|
SQRT |
Returns the positive square root of a given number. |
|
|
TAN |
Returns the tangent of the number, where the number is given in radians. |
|
|
TRUNC |
Truncates a number to a specified number of digits. |
|
Text |
TEXT |
Converts a percent, number, date, date/time, or currency type field into text. |
Summary-Level Formula :-
Make sure to organize your report data before adding a summary formula, as summary formulas need at least one group to function.
❌ Limitations – Where You Can’t Use Summery-Level Formulas
|
Category |
Limitation / Rule |
|
Function Limitations |
- Summary formulas don’t support date or date-time functions or fields. - A summary formula can’t reference another summary formula or a row-level formula. |
|
Grouping & Filtering |
- You can’t group report data by summary formula columns. - You can’t filter report data by summary formula columns. - You can’t reorder summary formula columns. - Group report data at least once before adding a summary formula column. |
|
Character & Data Type Limits |
- Maximum of 3900 characters per summary formula. - Can include fields of different data types: number, currency, percent, and checkbox (true/false). |
|
Dashboard & Chart Display (Classic) |
- Decimal places in dashboard/report charts follow your default currency setting, not the formula's specification. |
|
Field Availability |
- If a field used in a formula is deleted or unavailable (due to field-level security), the formula is removed from the report. |
|
Summary Types Limitations |
- Sum, Largest Value, Smallest Value, and Average are not available for the Record Count field. |
|
Blank Values Handling |
- Smallest Value includes blank (null) or zero values. - Largest Value includes the largest blank (non-null) value. |
|
Percent Representation |
- Percent are represented as decimals (e.g., 20% is 0.20). |
|
Operators |
- You can use operators to create negative values (e.g., {!EMPLOYEES:SUM} + - {!SALES:SUM}). |
|
Matrix Reports |
- In matrix reports, Salesforce calculates results only if “Where will this formula be displayed?” is not set to All summary levels. - If the formula returns null, the cell is empty. |
|
Display Limit |
- Summary fields can display up to 21 digits. - If exceeded, result may be inaccurate or display "#Too Big!". |
|
Blank (null) Handling |
- Treated as zero in formulas. |
|
Formula Errors |
- “#Error!” appears if there's an error or division by zero. - Use alternate logic to prevent this. |
|
Joined Reports |
- Cross-block summary formulas are available. |
Display option in summery level formula: -
for Selected Group: - All groups listed on the report will be available for selection in the options.