Skip to content

Reporting on Activities

If you’ve ever tried to create reports on the activities that your users are meticulously entering, you may have run into some perplexing behavior.

First off – “Activities” is an umbrella term for “Tasks” and “Events”. In reports, you can show both tasks and events in a list of results using any of the “… with Activities” standard reports types or the “Tasks and Events” standard report type. But when you work with these kinds of records in automation, you must access and manipulate the “Task” records and the “Event” records separately. To make things more confusing, if you want to create a custom field on either of “these objects”, you navigate to the “Activity” object in the setup menu and configure it there.

Secondly – the activity object has polymorphic lookup fields that allow you to relate the record to any object that allows activities. This is what makes the “Related to” (WhatId) and “Name” (WhoId) fields special – they are lookup fields that allow you to specify which object you want to use for that relationship on that specific record. While this is incredibly powerful for a streamlined experience with a ton of flexibility, it adds some complexity that you notice right away when reporting on activity records.

And thirdly – if you’re interested in this topic, it’s probably because you wanted to include more fields from other objects in an activity report and the standard report types weren’t cutting it. So a custom report type must be the answer, right? If you read the Ultimate Guide to Report Types series and went to create a “Deluxe” report type on the activity object, you were probably very disappointed to find that you cannot traverse those polymorphic Related To (WhatId) and Name (WhoId) fields. Here are some strategies I’ve used to get passed these roadblocks.

Start with the object the activity is related to and enforce activities as a child in a custom report type

We briefly covered this in an earlier post and it’s worth mentioning again if your situation allows for it. If your activity records are very consistent when it comes to specifying a Related To or Name record, you could use the relationships in place and create a custom report type. For example, let’s say your sales reps are logging calls and emails against their opportunities and you have validation rules in place to ensure they always specify an opportunity on these records. The opportunity object has a custom lookup field in place that allows one of the company’s satellite locations to be selected. You are trying to build a report that includes fields from the activity, opportunity, and the satellite location. But not every opportunity always specifies a satellite location.

Without adjusting your data model or creating any extra fields, you could create a custom report type using opportunity as the primary object and then enforce activities as a secondary object in the object relationships. Then edit the custom report type layout and use the “add fields related via lookup” link to traverse the satellite office relationship and include fields from that object. Every row in the report results is going to represent an activity record that is related to an opportunity and you will have access to any satellite office fields you included in the custom report type layout. Easy! But if things are more complex…

Create a formula field that indicates what object the activity is related to

Sometimes you want to show all activities that are related to two or more objects. The standard “Tasks and Events” report type gets you part of the way there, but it’s hard to filter down to the records you’re after. For this solution, you’ll need to create a custom field on the activity object and then use it as a filter in a report utilizing the “Tasks and Events” report type.

Navigate to the “Activity” object in the setup menu. Create a new custom field and choose “formula”. I named my field “Related To Object”. Then select the “text” data type. Here is the basic framework for the formula logic:

IF(!ISBLANK(WhatId),
    CASE(LEFT(WhatId, 3),
        "001", "Account",
        "006", "Opportunity",
        /*add other prefix / object name pairs here*/
        "Other"
    ),
/*ELSE*/
    NULL
)

You can add support for any standard object by including it’s prefix in the case function. Refer to this table for a list of all the standard object prefixes. For custom objects, navigate to a record page and find the record id in the URL. The first three characters are the prefix you can include in the formula above.

You can also replicate this approach for the Name (WhoId) field in another custom formula field:

IF(!ISBLANK(WhoId),
    CASE(LEFT(WhoId, 3),
        "003", "Contact",
        "00Q", "Lead",
        "Other"
    ),
/*ELSE*/
    NULL
)

Once these fields are in place, you can easily filter for activities that are related to opportunities or accounts or any combination of objects you are interested in.

This new formula field enables you to filter based on what object an activity is related to

Create a custom lookup field and populate it using automation

Finally, this solution will allow you to truly create an “Activities (Deluxe)” report type – where you can access any field from the related object in a custom report type that simply has “activity” specified as the primary object. This builds on the same concept from above where we can identify what object each activity is related to. Let’s go back to the example from earlier where activities were being related to opportunities, but change the scenario to where we can’t rely on every activity being related to an opportunity. How can we create a report that includes activity fields and opportunity fields for the records where an opportunity is related?

First you need to create a custom field on the activity object. Choose the “lookup relationship” data type, specify the appropriate object (in this example, specify the opportunity object), and give it a name. You do not need to include this field on any layouts – it will be populated using automation. You also do not need to include the related list on any layouts for the new parent object (in this example, the opportunity object).

For the automation, the new before-save flow feature will work great here. Create a new flow (or use an existing autolaunched flow if you follow the “one before-save flow per object” philosophy). Choose “Autolaunched Flow” on the first screen. Double-click on the “start” element and select the “New or updated records—flow makes fast field updates” option. In the second section, choose to start the flow when a record is created or updated. For the object, select “Task” or “Event” as necessary. You may need to set up a flow for both objects if you want to replicate this solution across both kinds of activities.

Start element settings

Once those settings are saved, add an assignment element to the canvas. Then set the value for the new lookup field, which we called “Opportunity” in this example, using a formula resource with the following logic:

IF(LEFT({!$Record.WhatId}, 3) = "006",
    {!$Record.WhatId},
/*ELSE*/
    NULL
)

Formula resource

Assignment element

Complete flow canvas

Connect the start element to the assignment element and you are ready to activate the flow. Anytime a record is created or edited, the custom lookup field will be populated with the correct value based on what the task is related to.

If you have existing records in place before you created this logic, you will want to edit them in order to have this logic fill in the necessary data. You can export all the relevant record IDs and use any mass data tool to simply call an update on each record Id. You don’t even need to map any other fields or actually edit the record – the flow logic will run and perform the necessary updates.

With this logic in place and all existing records updated, you can now create or edit an “Activities (Deluxe)” custom report type layout and traverse the relationship you just created in order to access any field from the related object. Refer to this post for step-by-step instructions.

If you have multiple key objects that activities are related to, you can replicate this solution for each of them. Simply create a corresponding lookup field on the activity object, a new formula resource within the flow using the appropriate 3-character prefix (Refer to this table for a list of all the standard object prefixes. For custom objects, navigate to a record page and find the record id in the URL. The first three characters are the prefix), and add another line of assignment within the single assignment element in that flow. Activate the flow and be sure to update any existing records.

Conclusion

With a little help from formula fields and automation, you can work through the quirkiness of creating reports on tasks and events. These tips help bridge the gap and make activities seem a little more like regular objects that you are used to working with in custom report types and in reports themselves. Please let me know in the comments if you have other scenarios that you encounter where reporting on activities creates a challenge.

Evan Ponter View All

Evan Ponter is a Salesforce Admin Hero from Baltimore, MD who has been focusing on declarative development since 2012. His desire to keep an org simple, streamlined, and maintainable by future admins has led him to being an expert on the declarative features of the platform. A deep understanding of reports, the importance of proper data modeling, and the utilization of declarative automation tools have propelled Evan along a blossoming Salesforce journey where he solves complex problems using clever solutions that provide the ultimate flexibility. When he's not logged into Salesforce, Evan enjoys playing bass guitar in a local rock band.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: