A custom summary formula is calculated after the report has grouped its rows, it can only reference summarised values, and the “display at” setting decides whether you get a number or a blank cell. PARENTGROUPVAL compares a group to the group containing it. PREVGROUPVAL compares a group to the one before it. Neither of them can see a single record, and almost every support question about summary formulas comes from expecting otherwise.
Two things called formulas, doing different jobs
Report builder offers two. A row-level formula is evaluated once per detail row and behaves like a formula field that lives inside the report. A custom summary formula is evaluated once per grouping and once per grand total. They are not interchangeable, and picking the wrong one is the first fork in the road.
If the answer you want exists on a single record, use a row-level formula. If the answer requires more than one record, use a summary formula. A ratio of two sums is a summary formula. Days between two dates on the same opportunity is a row-level formula.
What a summary formula is allowed to reference
Summarised values only. Sum, average, maximum, minimum, record count, and any summarisable field already in the report. You cannot reference a plain field, because at the point the formula runs there is no single value to read. There are twenty rows in that group.
Some other boundaries that catch people:
- A custom summary formula cannot reference another custom summary formula.
- You cannot filter a report on a summary formula. Filters happen before grouping, the formula happens after.
- Bucket fields cannot be built from one.
- A details-only export drops them entirely, because there are no groupings in that file.
The grouping-level trap
Every custom summary formula has a “where will this formula be displayed” setting: all summary levels, or one specific grouping. This is the single most common reason a formula returns blank.
Set it to a specific grouping and the formula renders only in that grouping’s subtotal rows. Every other row, including the grand total, shows nothing. People then assume the formula is broken and rewrite it, when the formula was fine and the display setting was wrong.
PARENTGROUPVAL
PARENTGROUPVAL returns a summarised value from a grouping above the current one. The classic use is percentage of parent total.
AMOUNT:SUM / PARENTGROUPVAL(AMOUNT:SUM, CLOSE_DATE)In a summary report the second argument names the parent grouping level. In a matrix report the function takes both a row grouping and a column grouping, because a cell in a matrix sits at the intersection of two hierarchies and “the parent” is ambiguous without both.
The grouping you name must actually exist in the report. Remove or reorder groupings later and the formula breaks, usually with an error that names the function rather than the grouping you deleted.
PREVGROUPVAL
PREVGROUPVAL reaches sideways instead of upwards, returning the same summary value from a previous group at the same level. Period-over-period change is the reason it exists.
AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)The optional third argument is how many groups back to look, defaulting to one. Pass 4 against a quarterly grouping and you are comparing to the same quarter last year.
Two warnings. The first group has no predecessor, so the formula returns zero rather than blank, which produces a spike at the left edge of every chart built on it. And “previous” means previous in the report’s sort order, not previous in time. Sort a date grouping descending and your growth figures silently invert.
Limits worth knowing
You get five custom summary formulas per report and one row-level formula. Each formula has a character ceiling in the low thousands, which sounds generous until you nest a few IF statements. Summary formulas also add cost proportional to the number of groupings, so a report with hundreds of groupings and five formulas is doing real work every run.
Frequently asked
Why is my formula blank in the grand total? Its display setting is pinned to a specific grouping. Change it to all summary levels, or accept that the grand total is not a meaningful place for that calculation.
Can I chart a summary formula? Yes. Summary formulas are available as chart values, which is often the only reason to build one.
Why does my percentage show as a decimal? The formula returns a number and the formatting is set separately, in the formula’s own format and decimal-places options. Multiply by 100 or set the type to percent, not both.
Do summary formulas respect field-level security? They respect it on the underlying fields. If the running user cannot see the field being summarised, the summary is not available and the formula fails rather than quietly returning zero.
