“Jury Rig” your Activity Reporting in Salesforce

The Activities object doesn’t seem to be a favored one in Salesforce. We all use it but encounter several limitations on a semi-regular basis.

For example, instead of your average lookup to a single object, we are given the innovative and yet absurdly pesky “WhatId” and “WhoId”. These two standard fields relate the Activity to the Account, Opportunity, Contact, or Lead, effectively serving as buckets. However, they aren’t available for reporting, making it difficult to track activities per object.

In the instance when you want to report on Activities by Object, you need to do two things: create a custom formula field on the Activities object and then create a summary report on Activities – Tasks and Events.

Use the following formula:

IF(
BEGINS(WhatId, “006”), “Opportunity”,
IF(
BEGINS(WhatId, “001”), “Account”,
IF(
BEGINS(WhoId, “00Q”), “Lead”,
IF(
BEGINS(WhoId, “003”), “Contact”,””))))

This formula checks the first three characters of the Salesforce Id (each object has a unique marker at the beginning of its records’ IDs and those markers are the ones that are listed above). The formula then assigns a text value based on those three characters.

After you have saved your formula, go to your Reports tab and create a new report. On the following page, expand the plus sign next to “Activities” and select “Tasks and Events.”

Then change the necessary filters and your report type to a “Summary” report type.

Save your report and run it. What you will end up with is something like the following:

If you want to limit your report to just, say, the Lead and Contact objects, you can do so using a filter much like the standard ones we edited before. Just drag the “Object” field into the blank space beneath the other filters and type the name of the desired object, separated by commas.