Using Calculated Columns

Test if conditions are true or false 

IF( )

IF(logical_test, value_if_true, value_if_false)

where

logical_test:  the condition you want to check

value_if_true:  the value to return if the condition is true

value_if_false:  the value to return if the condition is false

eg, IF(activity_type LIKE ‘Phone Call‘, ‘True’, ‘False’)

on an Activity report would display a row populated with True for every Phone Call activity and False for any other activity type.

Compare a value to a series of values and return a specified value

CASE

CASE column_name WHEN choice THEN return value ELSE value END

eg,

CASE  status_name
WHEN ‘Open’ THEN ‘Not Started’
WHEN ‘ Closed’ THEN ‘Finished’
ELSE ‘In Progress’
END

on a Case report would create a column which shows Not Started for any Case with a Status value of Open and Finished for any Case with a Status value of Closed.  In addition, it will show In Progress for Cases that have a Status value of anything other than Open or Closed.

CASE WHEN multiple conditions THEN return value ELSE value END

eg,

CASE
WHEN status_name = ‘Open’ AND assigned_to_name = ‘Management’ THEN ‘Escalated’
WHEN status_name = ‘Bug’ OR status_name = ‘Enhancement’ THEN ‘Requires engineering’
WHEN ‘ Closed’ THEN ‘Finished’
ELSE ‘In Progress’
END

on a Case report would create a column which shows Escalated for any Case with a Status value of Open that is assigned to Management, Requires engineering on any Case with a Status value of Bug or Enhancement, and Finished for any Case with a Status value of Closed.  In addition, it will show In Progress for Cases that do not fit into the previous criteria.

Add a specified amount of time to a date 

DATE_ADD( ) 

DATE_ADD(CURDATE(), INTERVAL 7 DAY)

this adds 7 days to the current date

NOTE:  The following interval types can be used in place of DAY:  SECOND, MINUTE, HOUR, WEEK, MONTH, YEAR 

Subtract a specified amount of time from a date 

DATE_SUB( ) 

DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

this subtracts 1 month from the current date

NOTE:  The following interval types can be used in place of MONTH:  SECOND, MINUTE, HOUR, DAY, WEEK, YEAR 

Format a date 

DATE_FORMAT( ) 

DATE_FORMAT(column_name, ‘%D %M %Y’)

eg, DATE_FORMAT(created_at, ‘%D %M %Y’)

would show the date a record was created with the day displayed as an ordinal number followed by a four digit year, for example 1st January 2010.

To display a date as numbers, use the same letters as above but enter them in lower case:

eg, DATE_FORMAT(created_at, ‘%d %m %y’)

which would show the same date as 01 01 10.

There are additional ways to format a date, which can include the weekday, as shown below:

%a

Shows the abbreviated weekday name according to the current locale.

%b

Shows the abbreviated month name according to the current locale.

Calculate the difference in days between two dates 

DATEDIFF( ) 

DATEDIFF(first_column_name, second_column_name)

eg, DATEDIFF(CURDATE(), created_at)

would show the number of days between today and the date the record was created.

Insert the current date 

CURDATE() 

CURDATE()

Type in the above to return the current date. 

Insert the current time

CURTIME()

CURTIME()

Type in the above to return the current time. 

Calculate the mean average of a group of numbers 

AVG( )

AVG(column_name)

eg, AVG(amount)

on an opportunity report would return the mean average of the opportunity amounts. 

Add numbers 

+ 

eg, first_column_name+second_column_name

Subtract numbers 

– 

eg, first_column_name-second_column_name

Multiply numbers 

* 

eg, first_column_name*second_column_name

Divide numbers 

/ 

eg, first_column_name/second_column_name

Find the total of a series of numbers 

SUM( )

SUM(column_name)

eg, SUM(amount)

on an opportunity report would return the total of the opportunity amounts. 

Display a specified number of decimal places 

FORMAT( ) 

FORMAT(column_name, integer)

where

integer is the number of decimal places you want to display

eg, FORMAT(AVG(amount),5)

on an opportunity report would return the average opportunity amount to 5 decimal places.

Count the number of records 

COUNT( ) 

COUNT(id)

Use COUNT(DISTINCT id) if you want to count the number of unique records within the grouping

Display text in upper case 

UPPER( ) 

UPPER(column_name)

Display text in lower case 

LOWER( ) 

LOWER(column_name)

LEFT(column_name, integer)

where

integer is the number of characters you want to limit the string to

Combine two or more fields 

CONCAT( ) 

CONCAT(first_column_name,’ ‘,second_column_name,’ ‘,third_column_name)

NOTE: If any of the fields that are concatenated together return a NULL value, the whole formula returns a NULL value causing your report to show blank rows in its results. If this is the case, see CONCAT_WS below or include a criteria on your report to exclude blank values.

Summarise a column into a string

GROUP_CONCAT( )

GROUP_CONCAT(column_name SEPARATOR ‘, ‘)

eg, GROUP_CONCAT( campaign_membership.campaign_name SEPARATOR ‘, ‘)

On a Person report, this will join together all the Campaigns of which the Person is a member, separated by a comma.  (Remember that you’ll need to summarise this report in order to see each person on a separate row and we recommend summarising by the Object Reference.)

CONCAT_WS(‘ – ‘, first_column_name, second_column_name, third_column_name)

NOTE: This causes the formula to ignore NULL values thus you may see rows in your report that do not make sense e.g. CONCAT_WS(‘ – ‘, name, employer). If the employer is blank the formula will return “Jim Bob – “. Also, notice that the SEPARATOR is at the start of the formula and is in quotes. This string value will be added in between each field that is concatenated.