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 flexible tool that enables us to organize and present our data in a meaningful way. It is made up of a list of records that meet the specified conditions.

In addition, these reports are displayed in rows and columns. Even data can be sorted, grouped, or shown as a graphical chart using Salesforce Report.

Furthermore, Reports help us to examine our data, monitor our progress, and make logical business decisions.

Formula Field in Salesforce Report

After learning about formula fields and reports in Salesforce, let’s move on to learn about the formula fields in a salesforce report.

A custom field that we create using a formula is known as a formula field in a Salesforce report. This field can be used to do calculations on values of other fields in the report.

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.

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

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.

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.

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.

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.

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.

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.

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.

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

Step 3: Click on New Report.

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

Step 5: Then, click on Create.

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.

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

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

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.

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.

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.

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.

• How to add Bucket Field in Salesforce
• How to Create Field Update Using Workflow rule in Salesforce
• How to Create Roll-Up Summary Field in Salesforce