How to report on the first/last item in a group, e.g. description of the latest activity

Reporting on the First or Last Item in a Group

It is easy to report on the first/last time that something happened using the MIN or MAX functions. For example, if you want to see the date of the last completed Activity that is related to a Lead, use:

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 this would return the item that comes last alphabetically, not the most recent one. Similarly, using a Value column would return any Description from within the group — not necessarily the earliest or latest.

To do this, you need to use a more complex calculated column. The steps below explain how to return the Description of the most recent Activity against a Person. You can jump straight to the final formula if you wish, or work through the logic step by step to understand how it is constructed.

Initial Report Setup

1. Start by building a details report based on Activities.
2. Ensure the Details view includes fields like Completed Date, Description, Primary Contact, Type, etc.
3. Add a Summary view and group it by Primary Contact.

Step-by-step: Constructing the Formula

Add a calculated column to the summary view. We’ll use GROUP_CONCAT to list out values in a defined order.

• Start with a basic concatenation:

  GROUP_CONCAT(PARENT('Description'))

• Then, control the sort order (descending shows the latest Activity first):

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

• Next, add a separator that will not conflict with any potential values in the Description, e.g. queen:

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

• Finally, return only the first item (the latest one):

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

Further Examples

You can easily adapt the formula depending on the record type and fields you’re working with. Just change the base report, grouping, and fields used in the formula.

The state of the last Case raised by a Customer

• Base the report on Cases.
• Group by Primary Contact or Primary Contact Employer.
• Use this formula:

  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.
• Use this formula:

  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.
• Use this formula:

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