Reporting Tips
This page is dedicated to providing calculated column formulae with some worked examples. This will enable you to insert these into your Reports and hopefully achieve the outcome you desire in your Summary views.
| Formula | What does it do? | Use Case | Worked Example |
|---|---|---|---|
| IF( ) | Test if conditions are true or false | I want to display the value True on the rows of the report that have Activities of type “Phone Call”.
|
IF(activity_type LIKE ‘Phone Call‘, ‘True’, ‘False’) |
| CASE | Compare a value to a series of values and return a specified value | I want to a column to show “Not Started” for any Case with a Status value of Open, “Finished” if Closed and anything else: “In Progress” | CASE status_name WHEN ‘Open’ THEN ‘Not Started’ WHEN ‘ Closed’ THEN ‘Finished’ ELSE ‘In Progress’ END |
| CASE | Compare multiple values to a series of values and return a specified value | I want to show a value of Escalated for any Case with a Status of Open as well as Assigned To being “Management”, any Case with a Status of “Bug” or “Enhancement” = Requires Engineering, any Case with a Status of “Closed” = Finished. Anything else will be “In Progress”. | 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 |
| count(DISTINCT PARENT(‘Field Name’) | Count the number of unique values in a Summary report | I want to count the unique number of Opportunities in the Details report. Due to joining my Opportunities to Line Items and Activities I am seeing multiple rows for the same Opportunity reference. | count(DISTINCT PARENT(‘Opportunity Reference’)). |
| GROUP_CONCAT(column_name SEPARATOR ‘, ‘) | Summarise a column into a string | I want to show all the Campaigns that a Person is a member of, separated by a comma. | GROUP_CONCAT( campaign_membership.campaign_name SEPARATOR ‘, ‘) |
| DATEDIFF(first_column_name, second_column_name) | Calculate the difference in days between two dates | I want to show how long it has been since my Activities were created. (the difference between when it was created and today) | DATEDIFF(CURDATE(), created_at) |