How to add Formula Field in Salesforce Report

In this salesforce tutorial, we will learn to add a Formula Field in the Salesforce Report. Moreover, we will go through the steps using which we can add a formula field in the report using Salesforce Lightning and Salesforce Classic.

While working in an organization that uses salesforce as a CRM tool, I got a task where I need to perform complex calculations, custom metrics, data manipulation, and conditional logic. So, to fulfill this requirement, we add a formula field in the salesforce report.

If you want to learn How to add Formula Field in Report using Salesforce Lightning and Salesforce Classic, you have come to the right place.

The following are the topics that we will cover in this salesforce tutorial:

  • Formula Field in Salesforce
  • Report in Salesforce
  • Formula Field in Salesforce Report
  • Salesforce Formula Field Factors
  • How to add Formula Field in Report using Salesforce Lightning
  • How to add Formula Field in Report using Salesforce Classic

Formula Field in Salesforce

In Salesforce, the formula field is a custom field that we can create by specifying the formula using the formula syntax.

These fields can be built on salesforce standards as well as custom objects like accounts, contacts, opportunities, etc.

And, we can use the formula to perform different operations on values based on other fields in the same record.

Report in Salesforce

A report in Salesforce is a versatile tool that helps us to meaningfully organise and present our data. It consists of a list of records that adhere to the requirements.

These reports are additionally shown in rows and columns. Using Salesforce Report, even data may be sorted, aggregated, or shown as a graphical chart.

Reports also assist us in analysing our data, tracking our development, and coming to rational business judgements.

Formula Field in Salesforce Report

Let’s go on to learn about the formula fields in a Salesforce report after learning about Salesforce reports and formula fields.

In a Salesforce report, a formula field is a custom field that is created using a formula. Calculations on the values of other fields in the report can be done using this field.

Salesforce Formula Field Factors

The following are the Salesforce Formula Field considerations:

  • We are unable to add a custom summary formula filter criteria.
  • A custom summary formula cannot be used to group or bucket a summary report or matrix report.
  • Date and Date Time fields are not supported in summary-level calculations.
  • A row-level formula cannot be referred to in a summary formula.
  • Only 5 Custom Summary Formula fields are included in salesforce reports.
  • Up to 3,900 characters, including spaces, return characters, and comments, can be used in a formula field.

With this, we got an overview of the formula field, report, and formula field in the report. Next, we will move further and see how we can add Formula Field in Report using Salesforce Lightning

Read How to Create Cross-Object Formula fields in Salesforce

How to add Formula Field in Report using Salesforce Lightning

Here are the steps to add Formula Field in Report using Salesforce Lightning Edition:

Step 1: Log in to your Salesforce account and navigate to Setup.

Step 2: In the App Launcher search box, search for reports and select the Reports option.

How to add Formula Field in Report using Salesforce Lightning
Add Formula Field in Salesforce Report using Salesforce Lightning

Step 3: Click on the New Report button and create a new report.

How to add Formula Field in Report using Salesforce Lightning Example
New Report to add Formula Field in Salesforce Lightning

Step 4: The dialogue box appears, now choose our desired report category from the left side of the box. Here, I select the report category as Opportunities.

Step 5: Now, select the desired report type. Here, I choose report types as Opportunities.

Step 6: Click on the Start Report button.

add Formula Field in Report using Salesforce Lightning
Select Report Category, Type and Start Report in Lightning

Step 7: Make sure to save the report, by clicking on the Save button. Otherwise, we will get an error “Report Name can’t be Null” and not be able to add a field formula. Here, I save the report as Opportunity Expenditure Report.

Step 8: Click on the Save button to finally save the report.

How to add Formula Field in Report using Salesforce Lightning

Step 9 Click on Filters, then select the Close Date.

Step 10: Then, change the Range field to All Time and click on Apply. Now, the report shows all the opportunities. If still not getting all the opportunities, refresh the page.

add Formula Field in Report using Salesforce Lightning
Create Add Formula Field in Report using Salesforce Classic

Step 11: Click on Outline, then select the GROUP ROWS to group the opportunity data. Here, I select the Type and group the data based on the different types of opportunities.

add Formula Field in Report using Salesforce Lightning example
Example to Add Formula Field in Salesforce Lightning

Step 12: To view the latest changes, click on the Refresh option.

Step 13: Now, in the left-aligned, click on the dropdown arrow next to Fields.

Step 14: Click the Create Formula option in the Fields section.

Step 15: Enter a name for the formula field in the Column Name field. Here, I set its name “Expenditure”.

Step 16: Optionally, we can also add the description, and select the formula output type and the decimal points.

Step 17: Now, search the fields in the search fields box, that we need to create a formula.

Step 18: After searching the field, select it, and click on the Insert button.

Step 19: Now, complete the formula expression using operators.

Step 20: Click on the Apply button.

add Formula Field in Report using Salesforce Lightning example
Create Formula Field in Report in Salesforce Lightning

Step 21: Finally, click on the Save button on the top right side of the page to save the changes in the report.

Step 22: Once the formula is applied to the field refresh the preview, and salesforce will automatically show the formula field that we have defined in the report.

How to add Formula Field in Report using Salesforce
Formula Field in Report using Salesforce

With this, we have learned to add a formula field in Salesforce reports using Lightning Edition. Now, we will move ahead and learn to add a formula field in Salesforce Report using Classic.

Read Create Custom Picklist Field in Salesforce

How to add Formula Field in Report using Salesforce Classic

Here are the steps to add Formula Field in Report using Salesforce Classic Edition:

Step 1: Log in to your Salesforce Classic account. Click on Avtar and then select Switch to Salesforce Classic.

How to add Formula Field in Report using Salesforce Classic
Switch to Salesforce Classic

Step 2: Look at the tabs and select the Report.

How to add Formula Field in Report using Salesforce Classic Example
Add Formula Field in Report using Salesforce Classic Example

Step 3: Click on New Report.

add Formula Field in Report using Salesforce Classic
Formula Field in Report using Salesforce Classic

Step 4: Select the Report Type. Here, I select Opportunities as Salesforce Report Type.

Step 5: Then, click on Create.

add Formula Field in Report using Salesforce Classic example
Formula Field in Opportunities Report using Salesforce Classic

Step 6: Make sure to save the report, by clicking on the Save button. Otherwise, we will get an error “Report Name can’t be Null” and not be able to add a field formula. Here, I save the report as Opportunity Sub Total Report.

How to add Formula Field in Report using Salesforce Classic
Formula Field in Report using Salesforce Classic

Step 7: Select the Range dropdown arrow and set it to All Time.

How to add Formula Field in Salesforce Classic Report
Add Formula Field in Salesforce Classic Report

Step 8: Click on the Format dropdown arrow adjacent to Preview, and set it to Summary.

salesforce add formula field in report
Add Formula Field in Summary Report using Salesforce Classic

Step 9: Click on a specific field, then click on the dropdown arrow and select by Group by this Field to group the opportunity data field. Here, I click on the dropdown arrow adjacent to the Account Name.

add formula field in report in salesforce classic
Group By Salesforce Field in Report

Step 10: In the left-aligned, click on the Add Formula option under Formulas.

Step 11: Enter a name for the formula field in the Column Name field. Here, I set its name to “Sub Total”.

Step 12: Optionally, we can also add the description, and select the formula type and the decimal places. Here, I set the formula type to “Currency”.

Step 13: In addition, optionally we can select the report level, where we want to display the formula calculation. Here, I set it to Grouping 1: Account Name.

Step 14: Select the fields from the Summary Fields dropdown, that we need for the creation of the formula. Here, I choose the Amount field with the SUM function.

Step 15: Next, select the appropriate operator from the Operators dropdown.

Step 16: Click on the OK button.

How to add Formula Field in Report using Salesforce Classic Example
Add Formula Field in Report using Salesforce Classic Example

Step 17: Finally, click on the Save button on the top left side of the page to save the changes in the report.

Step 18: Once the formula is applied to the field, salesforce will automatically show the formula field that we have defined in the report.

salesforce classic create formula field
Sub Total Formula Field in Report using Salesforce Classic

With this, we have learned to add formula fields in reports using Salesforce Classic Edition.

Conclusion

In conclusion, Formula Field in a Salesforce report is a custom field that we create using a formula. Both Salesforce Classic and Salesforce Lightning provide a simple procedure to add Formula Fields in Salesforce Reports.

In addition to this, the following are the topics that we have discussed:

  • Formula Field in Salesforce
  • Report in Salesforce
  • Formula Field in Salesforce Report
  • Salesforce Formula Field Factors
  • How to add Formula Field in Report using Salesforce Lightning
  • How to add Formula Field in Report using Salesforce Classic

You may also like to read the following Salesforce tutorials.