Using Formulas in a Report

Summarizing on a numeric field allows you to do simple sums, averages, etc. But what if you want a more complex calculation?

For any summary report - you have the option of adding more complex formulas

In Summary Formats - you'll see a folder in the field list called "Formulas" double click on Add Formula to create a new formula field

In Summary Formats - you'll see a folder in the field list called "Formulas"  double click on Add Formula to create a new formula field

Let's say we wanted an idea of the value of all the service hours associated with each volunteer opportunity. Let's say that we value each hour at $21.79.

This opens a layout to create a custom summary formula

This opens a layout to create a custom summary formula

I have given this a specific name and description

The Format is Currency and I have two decimal places to it.

I want to see this $ total at all Summary Levels

Create the Formula

Create the Formula

First we want to take the SUM of the Volunteer Hours Served (a field that exists in the occurrence record).

Add Operator and Number

Add Operator and Number

I can drop down the operators picklist to get the multiplication symbol. I am saying multiply the hours by 21.79. And wind up with this formula. I can then hit OK.

(For complex formulas - use the "Check SYntax" box to make sure everything is OK.

See your Results

At first you'll just see the summary. But hit RUN report and you'll see the dollar value of service summarized for each opportunity and a grand total at the bottom!

Edit existing Formulas

Edit existing Formulas

To Edit (or Delete) an existing formula, just hover over that formula and two icons will appear to the right. The pencil icon will open the edit screen, and the trash can will delete it.

Advanced Users

Advanced Users

There is a lot you can do with Summary Formulas and Salesforce has all the help you could possibly need. To learn more about operators, formulas, and functions, Log into your Salesforce instance and visit Salesforce Help: Building Custom Report Formulas

0 Comments

Add your comment

E-Mail me when someone replies to this comment