Getting Started with Report Formulas

Formulas are a valuable tool within Salesforce to help with calculating values. You have probably created formula fields on your objects, but did you know that you can also use formulas within reports? Well, you can, and they can add tremendous value. Let’s start by taking a look at the two different types of report formulas.

1. Custom Summary Formulas
2. Used to calculate additional totals in Summary, Matrix and Joined reports
3. Based on numeric Fields, which can be summarized
4. Can be used with expressions, fields and values
5. They are unique to the report and cannot be shared to other reports

• Row-Level Formulas
• Allow calculations on every row
• Flexibility outside of numeric fields with standard formula operations
• Must be enabled
• Can reduce the number of formula fields in your org
• Also unique to the report and cannot be shared to other reports

Each type of formula has its own use case as well as its limitations. Understanding each of them will help to identify which might be the right solution based on your reporting needs. To showcase the use of these features we will examine four uses cases that put them into practice. These uses cases are captured in a video demo that looks at each end report and then shows how the corresponding reporting formula was created for use in the requirement.

Use Case #1 – Custom Summary Formulas

• The marketing department needs to know what percentage of Leads created were converted.

This is a common use case that nearly every marketing department uses to measure the success rate of their campaigns. There is out of the box functionality for this using the IsConverted function show in the “Converted” field. Using this gives you the calculation and allows it to be shown in a chart. What is doesn’t do is allow further calculations or upgraded charts based on more complex formulas. To help created an enhanced conversion rate functionality we will utilize a custom summary formula.

Use Case #2 – Custom Summary Formulas

• You want to give the sales team a visual representation of where their sales are compared to goal.

All sales teams have goals or quotas. Understanding where individuals as well as the team stand towards reaching these goals is an important thing to know. Utilizing several custom summary formulas we will create a report that produces this information. This specific example assumes that the organization is not using standard forecasting and does not have any custom object that hold their quotas.

Use Case #3 – Row-Level Formulas

• An executive wants to see the number of days between an when an opportunity was created and when it closed, without creating a new custom field.

Calculating the number of days that opportunities take to close is a very common practice by sales managers. Traditionally this has been accomplished by creating a formula filed on the opportunity object to make the calculation. This formula field is generally left off page layouts and only viewed within reports. With the release of row-level formulas, we no longer need to create unnecessary fields that are only used for reporting purposes and can achieve this right within the report itself.

Use Case #4 – Row-Level Formulas

• The territory manager wants to evaluate their current structure by looking at all opportunities that have a different owner than the related Account.

Territory management is common area of reporting frustration. Visualizing and analyzing territory structure, relationships and success can often be difficult. Comparing opportunities that have different owner than its related account can help shed light on how your territory alignment is working amongst teams. To do this we can create a simple text row-level formula to highlight the respective opportunities.

Video Demo

Related Trailhead Modules

Use Summary Formulas in Your Reports

Evaluate Each Record in Reports with Row-Level Formulas

Evaluate Report Data with Formulas