Evaluate Each Record in Reports with Row-Level Formulas
Learn About Row-Level Formulas
Gabriela Livingston, Ursa Major’s head of sales operations, wants a better handle on the timing of the Ursa Major Solar sales cycle. She’s got some numbers showing that opportunities are taking longer to close on average, but she isn’t sure what that means on a per opportunity basis. She’d like to see data on how many days it’s been taking for each individual opportunity to close. If she can only get that information, she’s confident that she can propose targeted actions to help tighten up the sales cycle.
No problem! Gabriela can get all the information she wants with a row-level formula. Row-level formulas let you evaluate each record in a report and provide record-specific answers.
See Record Level Results with Row-Level Formulas
Let’s show Gabriela how to add a row-level formula to an opportunity report. We’ll set up a row-level formula that calculates the number of days each opportunity takes to close.
Due to the nature of sample data, this formula may result in a negative "Time to Close". To see current results, you can update the Close Date on some opportunities to be more recent than the Created Date.
- Create an opportunity report.
- Click Save.
- Name the report
Opportunity with Formula.
- Click Save.
- From the Columns section of the Outline pane, click
| Add Row-Level Formula.
- From the Edit Row-Level Formula Column window, create the formula:
- In Column Name, enter
Time to Close.
- In Description, enter
Time to Close in Days.
- From Formula Output Type, select Number. Even though this formula works with date values, number is the output type needed because the formula performs subtraction and returns a number.
- Set Decimal Points to 0.
- In Formula, paste
CLOSE_DATE - DATEVALUE(CREATED_DATE)
- CLOSE_DATE and CREATED_DATE are the API names of the Close Date and Created Date fields. Formulas work with the API names of fields, not the display names. The easiest way to enter the API name of a field is to search for it from the FIELDS menu, select it, and click Insert >.
- Because CLOSE_DATE's data type is date and CREATED_DATE's data type is datetime, we convert the value of CREATED_DATE to the date data type with the DATEVALUE() function. The two values must have a matching data type so that we can subtract one from the other. (Datetime and date are different data types because datetime includes information about units of time smaller than a day - hours, minutes, seconds, and so on - and DATE does not.)
- To make sure that the formula is error-free, click Validate. If necessary, resolve errors.
- Click Apply.
- Click Save & Run.
That’s all there is to it! Now when Gabriela views the Opportunity report, she can scan the Time to Close column (the rightmost column) to see the number of days that each opportunity took to close.
Resources