It is easy to report on the first/last time that something happened using the MIN/MAX functions, for example, seeing the date of the last completed Activity that is related to a Lead:

• MAX(completed_date)

However, what if you want to see the corresponding Description or Type of the last Activity that was created? You cannot use the MIN/Max functions here, as rather than showing you the Description of the last Activity that was created, you would see the Description that comes last alphabetically. You also should not add a 'Value' column, as this will show you the Description of any Activity from within the grouping, which is not necessarily the earliest or latest one.

Instead, you need to use a slightly more complex calculated column. The example below returns the Description of the most recent Activity against a Person, but could be amended to show the content of other fields. You don't have to work through all the steps shown and could jump straight to the final formula if you want, but the steps describe how that formula is built and it would be helpful to understand the rationale, especially if you want to create a variation on this.

• Start by building a details report based on Activities. Make sure the Details view contains the relevant columns, like Completed Date, Description, Primary Contact, Type etc.
• Add a summary view to your report, and group by Primary Contact.
• Add a calculated column to the summary view. First we need to concatenate the list of Activity Descriptions together using the following formula:

  • GROUP_CONCAT(PARENT('Description'))

• Control the order in which the descriptions are display - either Ascending or Descending. You can choose which date to base this on, like Created At or Due Date, but here we've used the date the activity was completed. To order the description in ascending date order replace DESC with ASC:

  • GROUP_CONCAT(PARENT('Description') ORDER BY PARENT('Completed Date') DESC)

• Now you need some way to separate the different descriptions so you can easily identify where one stops and the next one starts. If you were looking at the Activity Type rather than the Description, you may be able to simply use a comma, but if you are not certain that there are no commas within the Activity Descriptions, then you need to use something more unique, like the word 'queen':

  • GROUP_CONCAT(PARENT('Description') ORDER BY PARENT('Completed Date') DESC SEPARATOR 'queen')

• Finally, we only want to output the text that displays to the left of the first separator.

  • SUBSTRING_INDEX( GROUP_CONCAT(PARENT('Description') ORDER BY PARENT('Completed Date') DESC SEPARATOR 'queen'), 'queen', 1)

Now you should have a column that returns you the description of the most recently completed activity.

NOTE: When you're displaying rich text fields such as the Description, it will include HTML tags so your columns might show text such as <div> or #39; etc. To display the text without these tags, open the column, choose the Advanced tab and use the dropdown picklist next to 'Display as' to change the setting from 'default' to 'plain text'.

Further examples

It is easy to replace the relevant parts of the formula based on what you are reporting on; just base your report on the relevant record type, change what you are grouping by, and then amend the relevant field within the formula:

• The state of the last Case that has been raised by a Customer - base the report on Cases, group by Primary Contact (or Primary Contact Employer) and amend the formula to:

  • SUBSTRING_INDEX( GROUP_CONCAT(PARENT('State') ORDER BY PARENT('Created at') DESC SEPARATOR ', '), ', ', 1)

• The value of the last Order raised by a Customer - base the report on Orders, group by customer and amend the formula to:

  • SUBSTRING_INDEX( GROUP_CONCAT(PARENT('Net Amount') ORDER BY PARENT('Customer Order Date') DESC SEPARATOR 'king'), 'king', 1) 

• The Stage of the first Opportunity against a Customer - base the report on Opportunities, group by Prospective Customer and amend the formula to:

  • SUBSTRING_INDEX( GROUP_CONCAT(PARENT('Opportunity Stage') ORDER BY PARENT('Close Date') ASC SEPARATOR '4242'), '4242', 1)