|Posted: 2012-11-05 15:10|
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:
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.
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'.
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:
NOTE: Notice that we are using a different value for the separator each time, to demonstrate that you can use any string of text, as long as it is unique. i.e. do not use commas, full stops or dashes if your Activity Descriptions may use these characters, neither should you use brackets or percentages when you are working with the Opportunity Stages, and do not use forward slashes or dashes if you are working with dates.