Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 36 ยท Reports and Dashboards

Report Filters, Buckets and Formulas in Salesforce

Sharpen Salesforce reports: standard and field filters, filter logic, cross filters, bucket fields, row-level and summary formulas, and conditional highlighting, with JavaScript stand-ins.

A report that lists everything is rarely useful. The value comes from asking a precise question: open opportunities over 50,000 closing this quarter that belong to my team, grouped by stage. In Salesforce you express precision with filters, groupings, buckets and formulas. This lesson walks through each tool and shows when to use which. The menus are described in words, and the examples are labelled JavaScript stand-ins that model the same logic; no real Salesforce output is shown.

Standard filters and field filters

In the Filters panel of Lightning Report Builder you meet two kinds of filter.

Standard filters appear at the top and are scoped to the report type:

  • Show Me: All opportunities, My opportunities, My team's opportunities, depending on the object and the role hierarchy.
  • Date field and Range: which date field applies (usually Close Date or Created Date) and which period: Current FQ, Last 30 Days, Last Month, Custom with a start and end date, and so on. Relative ranges keep the report current without editing it.

Field filters are conditions you add: Stage equals Negotiation, Amount greater than 50000, Account Name contains Acme. Operators include equals, not equal to, less or greater than, contains, does not contain, starts with, and includes (for multi-select picklists).

You can make a filter editable at run time (the user changes the value without opening the builder), and add Filter Logic such as (1 AND 2) OR 3 to combine filter rows by their numbers. Without logic, all rows are joined with AND. Filter logic is a frequent source of mistakes: parentheses matter.

Text filters in reports are not case sensitive. To filter for a blank value, use the operator equals and leave the value empty.

Cross filters

A cross filter keeps or removes records based on related records you may not even show. Add it in the Filters panel (Add, then Cross Filter): choose the primary object, With or Without, the related object, and optional sub-filters. Examples: Accounts WITH Opportunities where Stage = Closed Won, or Accounts WITHOUT Cases created in the last 90 days. Cross filters are the clean way to answer "which X have no Y".

Groupings and summarizing

In summary and matrix reports, grouping fields turn rows into an analysis. Pick Group Rows By (and Group Columns By for a matrix) in the Outline. For a date grouping you set the granularity: Day, Week, Month, Calendar Quarter, Fiscal Year and so on. You can sort groups by name or by a summarized number. Number columns can be summarized as Sum, Average, Max, Min (and Median in newer releases), and Record Count is always available.

Bucket fields

A bucket field groups values of one column into categories you define, without creating a custom field. Add one in the Outline (Add Bucket Column). Types: Numeric (ranges), Picklist (group several picklist values) and Text (group text values). Example: bucket Amount into Small (0 to 9,999), Medium (10,000 to 49,999) and Large (50,000 and above), then group rows by the bucket to get a clean summary.

Buckets belong to one report only, and a report can hold a limited number of them. If you need the category in many reports, in list views or in automation, create a formula field on the object instead.

Formulas in reports

Two kinds of report formulas exist, and mixing them up is a classic mistake.

Row-level formulas compute a value for each row (a few per report). They use the fields of that row and functions like IF, AND, OR, TEXT, DATEVALUE and ROUND. Example: the age of a case in days, or a size label.

TEXT
CLOSE_DATE - CREATED_DATE
IF(AMOUNT > 50000, "Large", "Small")

Summary formulas compute across grouped values (the subtotals), for example a win rate or an average discount. In a summary report, add a Summary Formula column and choose the grouping levels where it is shown:

TEXT
WON:SUM / RowCount
AMOUNT:SUM / RowCount

Here AMOUNT:SUM is the sum of the Amount column for the group, and RowCount is the number of records. Use a summary formula when a percentage depends on totals, because averaging per-row percentages gives the wrong answer.

Conditional highlighting

For summary, tabular and matrix reports, Conditional Highlighting colors a summarized column by ranges (for example below 20 red, 20 to 50 yellow, above 50 green). It makes a report scannable at a glance, and it carries over to table components on dashboards.

Stand-in: filter logic

This is not Salesforce. It evaluates numbered filter rows with logic like (1 AND 2) OR 3, so you can see how parentheses change the result.

JS Stand-in: (1 AND 2) OR 3 versus 1 AND (2 OR 3)

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Stand-in: buckets and a summary formula

JS Stand-in: bucket column and win rate

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Tips for fast, accurate reports

  1. Start with filters (owner, date range, record type) so Preview is small and the final report is fast.
  2. Prefer relative date ranges over fixed dates for reports you reuse.
  3. Check the Show Me scope: "My" reports show different data to different people.
  4. Use Record Count to sanity check totals, and compare to a list view.
  5. Name reports by what they answer: "Open Pipeline by Stage - This Quarter".
  6. Test with a user who has a different role to be sure sharing does what you expect. Dashboards add a running user, covered in Dashboards and components.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which tool answers 'which accounts have no cases in the last 90 days'?

    A cross filter with WITHOUT removes accounts that have matching related records.
  2. 2. What is a bucket field?

    Buckets categorize values inside one report without changing the object.
  3. 3. Win rate per stage should be calculated with:

    Ratios of totals need a summary formula; averaging per-row percentages is wrong.
  4. 4. With no filter logic set, how are several field filters combined?

    All filter rows must be true unless you write custom filter logic.

Summary

  • Use standard filters (scope and date range) first, then field filters, and write Filter Logic with parentheses when mixing AND and OR.
  • Cross filters keep or remove records based on related records (WITH or WITHOUT).
  • Bucket fields categorize values inside a report; formula fields do it for the whole org.
  • Row-level formulas work per row; summary formulas work on grouped totals.

Next: Dashboards and components