Advanced highlighting for report data

Another interesting feature that’s used when displaying report data is the possibility to highlight numeric data ranges. However, the report must have at least one summary or custom summary formula field:

Conditional highlighting in summary fields

Fill in the range of colors and the threshold values shown in the preceding screenshot to highlight the critical values for your report. You should receive the following output:

Conditional highlighting display

In the Lightning Report Builder, we can set up to five highlight rules. If we apply more than three rules, conditional highlighting will only be editable from the Lightning Report Builder.

Bucket fields

Numeric, picklist, and free-text fields can be further grouped into a kind of ranged formula called the bucket column. We can use bucket columns to group similar values into the same category, such as the level of interest of an account based on its annual revenue field or industry type (such as manufacturing or technology) when given the industry field of accounts.

To create a new bucket column, click on the down arrow icon on the column of the report preview panel you wish to alter and select Create Bucket Column. Then, create the buckets for the numeric field, as shown in the following screenshot:

Bucket column configuration of a numeric field

We can also create a bucket column for picklist fields, as shown in the following screenshot:

Bucket column configuration of a picklist field

For picklist fields, you can select the Bucket remaining values as Other option to categorize unbucketed values into another category.

This is the result of the bucket columns we configured earlier:

Bucket columns displayed after configuration

Bucket fields behave like any other field on our report: we can group by bucket fields, chart them, or filter them.

The following limitations apply to bucket fields (as of Summer 2019):

• You can only have up to five bucket fields per report
• You can only have up to 20 buckets per field
• You can only have up to 20 values per bucket

Formulas in reports

Do you miss using formula fields? Regardless of whether the answer is yes or no, reports come with some useful formula options:

• Column summaries
• Summary formulas
• Row-level formulas (beta, as of Winter 2020)

Using column summaries is the least complex way of creating a summary formula out of a numeric field. This is achieved by opening any numeric field options on the corresponding report’s column header:

Creating summary fields

We can do one or all of the following operations:

• Sum
• Average
• Max
• Min

You can even click on the Columns sidebar to open up the Summarize popup:

Summarize popup from the Columns sidebar

In this example, we can see the subtotals and the summarized total:

Report summary displaying the subtotal and total

This information is also available on the report’s header:

Summaries on the report’s header

Let’s go a step further. With summary formulas, we can evaluate a report’s group subtotals and grand totals, such as their tax application or fixed margin calculation. This feature is available in both Lightning Experience and Salesforce Classic.

To create a new summary formula (in Lightning), click on the column’s sidebar options and choose Add Summary Formula. The following popup will appear:

Summary of the formula editor

The editor is divided into different sections:

• The left-hand side shows the fields that were used on the report and a list of available functions (mostly logical and math functions)
• The header displays the details of the formula (such as the return type, only numeric values, and name and description)
• The body of the formula and the chance to display the result of the formula at all summary levels, on the grand total only, or in specific groups (click on the Display tab for these options)