The Salesforce Report Cheat Sheet

Row-level vs custom summary start here

Reports give you two ways to calculate. Pick the wrong one and the function you need will not be in the editor. The difference is what each formula reads and where the answer lands.

Plain-English test: if the question is about one record ("how many days has this deal been open"), reach for a row-level formula. If the question is about a set ("what percent of this month did the West region close"), reach for a custom summary formula.

Neither type outputs text. Both return a number. When you want a word label ("New", "Aging", "Stale") or a color, use a bucket column or conditional formatting instead. See section 12.

The building blocks of every summary formula

Inside a custom summary formula you do not reference a raw field. You reference its rolled-up value by adding an aggregate suffix. The token before the colon comes from the editor's field picker. Examples below use AMOUNT for the Opportunity Amount field.

A summary formula only sees summarized fields and RowCount. It cannot read a single record's raw field value. That is what row-level formulas are for.

Average deal size

Sum divided by count. (AMOUNT:AVG does the same thing; use the explicit form when you want a custom denominator.) Format as currency.

AMOUNT:SUM / RowCount

Win rate needs a helper field

A summary formula cannot conditionally count on its own, so first add a small formula field on the Opportunity (Number) that flags wins, then summarize that flag.

IF(IsWon, 1, 0)

WON_FLAG:SUM / RowCount

Filter the report to closed opportunities first if you want win rate of closed deals rather than win rate of everything in the pipeline.

Attainment against a flat quota

Quick when the target is a single fixed number. Swap 250000 for the quota. Format as percent.

AMOUNT:SUM / 250000

Average sales cycle, in days

First build the row-level day count (section 5), then summarize that row-level column with AVG in the report. Row-level formula results can be summarized like any other numeric column, so no separate summary formula is needed.

PARENTGROUPVAL summary & matrix only

Reaches up the grouping tree to read a parent or grand-total value, so each group can be compared to the whole. Use GRAND_SUMMARY for the report total.

PARENTGROUPVAL(summary_field, grouping_level)

AMOUNT:SUM / PARENTGROUPVAL(AMOUNT:SUM, GRAND_SUMMARY)

On a matrix report you pass the parent row and parent column levels, and the grand-total keywords are ROW_GRAND_SUMMARY and COLUMN_GRAND_SUMMARY. The editor inserts the correct grouping tokens for you.

PREVGROUPVAL summary & matrix only

Reads the same summary value from the previous peer group. Group the report by Close Date (Calendar Month) and you get month-over-month math. The optional third argument is how many groups back to look (default 1).

PREVGROUPVAL(summary_field, grouping_level, increment)

AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)

IF(
  PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE) = 0,
  0,
  (AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE))
    / PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)
)

Both functions live only in custom summary formulas and only on reports that have a grouping. They are not available in row-level formulas.

Sales cycle length, in days

Close date minus the creation date. CreatedDate is a date/time field, so wrap it in DATEVALUE to get a clean date subtraction. Output type Number.

CloseDate - DATEVALUE(CreatedDate)

Opportunity age, in days

How long a record has existed as of today. Useful for open-pipeline hygiene.

TODAY() - DATEVALUE(CreatedDate)

Days until close (negative means past due)

CloseDate - TODAY()

Overdue flag (1 = open and past its close date)

A 1/0 flag is the row-level workaround for "I want to count records that meet a condition." Sum or average it in the report.

IF(AND(NOT(IsClosed), CloseDate < TODAY()), 1, 0)

Remember the cap: one row-level formula per report. If you need several derived numbers on the same report, build them as formula fields on the object instead, where there is no such limit.

The date toolkit

Days between two dates

Subtracting one date from another returns a whole number of days. Order matters: later date first for a positive result.

EndDate - StartDate

Shift a date forward or back

Add or subtract a number of days directly. Use ADDMONTHS when you mean calendar months, since month lengths vary.

CloseDate + 30

ADDMONTHS(CloseDate, 1)

Calendar quarter number (1 to 4) from a date

Round the month up in thirds. For fiscal quarters, use the record's Fiscal Quarter field instead, since fiscal periods follow the org's calendar.

CEILING(MONTH(CloseDate) / 3)

Weekend close flag (1 = Saturday or Sunday)

IF(OR(WEEKDAY(CloseDate) = 1, WEEKDAY(CloseDate) = 7), 1, 0)

Aging tiers as a numeric code

Nested IF because ranges need greater-than tests, and Salesforce CASE only matches exact values. This returns 3 for stale, 2 for aging, 1 for fresh. To show words instead of a code, use a bucket column (section 12).

IF(
  TODAY() - DATEVALUE(CreatedDate) > 90, 3,
  IF(
    TODAY() - DATEVALUE(CreatedDate) > 30, 2,
    1
  )
)

Date windows without a single function most useful for reporting

These are filter values, not formula functions. Type them into the value box when you filter on a date field. They are the cleanest way to make a report roll forward on its own, so you never hard-code TODAY() math into a filter.

Fiscal variants follow the fiscal year defined in Setup. If your fiscal year is not the calendar year, the calendar and fiscal literals will select different ranges.

Branching and null handling

Comparison operators: = equal, <> not equal, < > <= >=. You can also write && for AND and || for OR, though the named functions read more clearly.

Map stages to a numeric score

Picklists compare cleanly when wrapped in TEXT(). CASE matches exact values, with a final fallback.

CASE(TEXT(StageName),
  "Prospecting",  1,
  "Qualification",2,
  "Proposal",     3,
  "Negotiation",  4,
  5
)

Alternative for a single picklist value: ISPICKVAL(StageName, "Closed Won") returns true or false without converting to text.

Safe division (no divide-by-zero error)

Division by zero shows as #Error!. Guard it with a zero check on the denominator.

IF(RowCount = 0, 0, AMOUNT:SUM / RowCount)

Numbers and rounding

MCEILING and MFLOOR exist for rounding negative numbers by magnitude rather than direction. Reach for them only when negative values need special handling.

Working with text inside a numeric formula

Report formulas return a number, so text functions are used as tests or measurements, not to output a string. CONTAINS feeds a yes/no into an IF, LEN returns a count, and so on.

IF(CONTAINS(Name, "Renewal"), 1, 0)

LEN(Description)

Bucket columns for word labels

A bucket column groups values into named categories without a formula, which is the right tool whenever you wanted a text label that a formula cannot produce. Examples: deal sizes into SMB, Mid, Enterprise; ages into New, Aging, Stale; lead sources into Paid and Organic.

• Numeric and date fields bucket by ranges you define.
• Picklist and text fields bucket by dragging values into named groups.
• Everything not placed falls into an Other catch-all you can rename.

Conditional formatting for color

In the Lightning report builder, color a summarized number by thresholds without any formula. Set rules such as green above target, yellow near it, red below. Applies to summary and matrix reports and reads at a glance on a dashboard.

If your goal is "make the big numbers stand out" or "label these rows," start here before writing a formula. Less to maintain, and no character limits to fight.

The things that quietly break formulas

• Limits. One row-level formula per report. Up to five custom summary formulas, each up to about 3,900 characters. For more derived numbers, build formula fields on the object instead.
• Output is numeric. Neither report formula type returns text. Want a word or a color? Bucket column or conditional formatting (section 12).
• #Error! usually means divide by zero, or a function that is not valid in this formula type. Guard division with an IF on the denominator.
• Grouping functions need a grouping. PARENTGROUPVAL and PREVGROUPVAL only work in custom summary formulas on summary or matrix reports.
• DATEVALUE on a date/time converts using GMT. Near midnight, the resulting date can land a day off from local time. Worth knowing when day counts look one off.
• Blank summaries. A blank numeric value can poison a division. Wrap it with BLANKVALUE(field, 0) first when that is the safe default.
• The function dropdown is the final word. If a function is not listed in that specific formula editor, it is not allowed there, no matter what any cheat sheet says.
• Changing a field's data type can break formulas that reference it. Check dependent reports after a field change.

Standard fields you reach for most

In the editor you insert fields from the picker rather than typing them, so the exact token may show as the field label or the API name depending on context. Custom fields end in __c.

Field availability and exact names vary by org and by report type. When a field you expect is missing from the picker, check the report type rather than the field itself.