You are here:
Evaluate Report Data with Formulas
Reports feature built-in basic math functions (sum, average, min, and max) that you can apply to any numerical column in a report. When you're ready to perform advanced logical or mathematical operations, write a formula.
Required Editions
| Available in: Salesforce Classic and Lightning Experience |
| Available in: Essentials, Group, Professional, Enterprise, Performance, Unlimited, and Developer Editions |
| Available in: Enhanced Folder Sharing |
| User Permissions Needed | |
|---|---|
| To create, edit, and delete formulas in private reports: |
|
| To create, edit, and delete formulas in public and private reports: |
|
There are three types of formulas, summaries, summary formulas, and row-level formulas. The type of formula you write depends in part on where you want that formula to apply.
Summaries
Summaries are mathematical functions that you can apply to numeric columns in a report. Apply summaries to find the sum, average, minimum, or maximum value. Summaries evaluate data in and show subtotals for each report group, plus a grand total for all data in the report. No need to write a formula to find these essential math functions; instead, apply them from a numeric column's More Actions menu.
Apply a summary to answer business questions like these:
- What is the total value of all our open opportunities?
- What is the average age of all our cases?
- Which of our accounts has the most employees, and which has the least?
Row-level Formulas
Row-level formulas apply to every record in a report, and can evaluate data mathematically or logically. For example, use a row-level formula in an opportunity report to subtract Created Date from Close Date to how long it took to close each opportunity. Write another row-level formula to flag each opportunity with an emoticon based on whether customer satisfaction score (CSAT) is above 9, above 6, or below 6.
Write a row-level formula to answer business questions like these:
- How many days did it take each opportunity to close?
- Which case subjects contain the word "widget"?
- As defined by a customer satisfaction (CSAT) score, which customers are satisifed or upset?
Summary Formulas
Summary formulas evaluate data in either a specified group, all groups, or all groups and the entire report. If an opportunity report is grouped by Type and Stage, a summary function can apply to any of these places:
- Just to Type
- Just to Stage
- Just to the grand total of all report data
- To Type, Stage and the grand total of all report data
Summary formulas are perfect for comparing grouped data to other groups or to report totals. For example, write a summary formula to evaluate cases by source or product.
Write a summary formula to answer business questions like these:
- After deducting taxes and expenses, what are our net earnings?
- What percentage of our opportunities are attributable to each lead source?
PREVGROUPVAL() and PARENTGROUPVAL()
PREVGROUPVAL() and PARENTGROUPVAL() are two types of special summary formulas that
are particular useful in reporting. Use PREVGROUPVAL()
to compare one group to other groups in the report. Use PARENTGROUPVAL() to compare a child group to a parent group or to a report
total.
Write summary formulas with PREVGROUPVAL()
and PARENTGROUPVAL() to answer business questions like
these:
PREVGROUPVAL(): How much more or less business did we do in February compared to January?PARENTGROUPVAL(): In which month did we do the most business last year?
- Summarize Report Data
When you want to know sum, average, highest, or lowest value of a numeric column (like Amount, Age, or Probability), summarize the column. Summaries calculate subtotals for every group in a report, as well as a grand total for all report data. - Evaluate Each Record in Reports with Row-Level Formulas
Answer business questions like "how many days did it take each opportunity to close?" by using row-level formulas that evaluate each record in a report. - Evaluate Groups and Totals with Summary Formulas
Write summary formulas to evaluate a report's group subtotals and grand totals. For example, summary formulas can adjust earnings after tax. - Compare Groups with PARENTGROUPVAL() and PREVGROUPVAL()
Write row level formulas to evaluate each record in a report. For example, see how many days it took each opportunity to close by subtracting the Open Date from the Close Date.
