Salesforce Reporting on Activities for Accounts and Opportunities

I want a simple ratio for how many times my people called a client to get that first demo.  From watching Shark Tank I know this is a key metric, they always ask “what is your cost to acquire a new customer”.  This should be in the Salesforce Wheelhouse right?  Not so fast, I’ll discuss some various options and ultimately share the solution we came up with.

Where are Calls and Meetings stored?

When I initiate my pursuit I don’t have an opportunity defined, I simply call on an account.  From the Account I click ‘Log a call’.  Because I also left the reminder checked by default my action resulted in two rows:

steve a

For the report I want to be mindful of this and only capture Calls by filtering on TaskSubtype.  Sending an email counts the same as calling, and it’s what I prefer.  Does anyone even listen to phone messages anymore?  In this case the TaskSubtype is Email.  After two emails and a phone call I set up a demo meeting by adding a new Event on the Account Open Activities related list.  Now my data looks like this:

steve b

And in the Event table we now have:

steve c

Add an Opportunity and engage with the client by calling and meeting

Now that we have identified an account with a need we set up an opportunity and give a more specific demo to a targeted audience.  I have now created an Opportunity associated with the Account and logged two calls and organized one meeting.  I now have 6 Tasks:

steve d

And 2 Events

steve e

Validate data before attempting to build an aggregate report

It’s tempting to start with the report, I’ve taken this approach, but when I attempt to reconcile the report with the data I sometimes find the wrong report type was used.

By creating a Tabular Report using the Task and Events Report Type we gain insight into how the data from the Task and Event table are joined and filtered.  Set the report to Show ‘All Activities’, ‘Open & Completed Activities’, and ‘Tasks and Events’.  When I run the report I only pull 7 rows when I expected 6 + 2 = 8.

steve f

The Task with the Not Started Status was filtered out.  That happens to be great, but it demonstrates that these built in Report Types are a bit of a black box.

Roadblock #1: I need fields from Accounts and Opportunities

The report type does have some of the popular built in Opportunity and Account fields, such as name; but not the custom fields I need.  I’ll create my own report type right?  Report Types are not created in the Reporting area, head over to Setup, Create, Report Types.  I desire a table relationship such as:

steve g

Notice something funny?  That’s right the letters are all wrong because I can’t have sibling relationships.  After I select Account as my primary object I can only nest one object below it.  The implications is that I cannot create a Report Type that returns Activities for both Accounts and Opportunities.

Roadblock #2: Activities with Opportunities contains custom fields for both Accounts and Opportunities

steve h

Previously I was working with the ‘with Accounts’ flavor and expected all of the built in report types with the same naming convention to be consistent.  In this case I’m glad that they are not.  I was about to resort to a Joined Report.  Happily this report type contains custom fields for both the Account and Opportunity object.  At this point had I not checked the expected data I might be wondering whether Activities for both Accounts and Opportunities are being returned?  I ran the report, they are….until I add Opportunity Name to the report.

steve i

Roadblock #3: Joined Report

The Joined Report is fairly complicated.  Starting out with a report type Activities with Accounts and then changing the format drop down to Joined Format the ‘Add Report Type’ button appears on the ribbon.  Next add the Activities with Opportunities report type to get the two block configuration displayed.  We can group by Account Name since this field is common between both report types, but we cannot group by Opportunity Name.  Ultimately without this grouping we are unable to hide details and display aggregate counts for activities.  I tried the ‘self-joined’ approach where I used the report type Activities with Opportunities, and then attempted to add an additional report type of the same type.  The report types already in use are removed from the selection dialog so I created a custom report type.  Only a single object from a custom report type is available for grouping.  Using the built in Salesforce reporting it is not possible to aggregate values across objects.

If at first you don’t succeed relax the requirements

I dropped the grouping by Opportunity and built the following report.

steve j

The Report Designer:

steve k

And the Report when it has been run:

steve k

I really don’t want the Task column on the report, but if I remove it then my custom formula is also removed from the report.  I would also like to remove the record counts in parenthesis, but there just is not a lot of control over the finished product.

Turning Reporting up to 11

The reality is you’re doing exceptional if you can get Salesforce reporting up to 10.  If your needs are greater you will likely need to install a third party reporting tool from the App Exchange, or utilize Heroku Connect in order to export and sync data to a data mart or reporting database and use a relational reporting tool.  If you need guidance for finding the best tool or technique to fit your Salesforce reporting needs reach out to Statera, we would be happy to work with you.

About the Author

Steve MunLeeuw is a certified Platform Developer at Statera with two years of CRM development experience.  Prior to working on the Salesforce platform he specialized in integration work with JBoss Fuse and worked with C# since it’s inception.  When not coding he likes to explore dirt roads and watch live music.



Creating a Custom Report Type for Activities, why can’t I get the activity type to display?

Reporting on large volumes of Activities: A typical solution architecture outside of Salesforce dictates that you replicate the database and report off of that.  Online Transaction Processing (OLTP) typically is not a good fit for aggregating large volumes of data.  In the case of Salesforce Activities reports can time out, that’s the motivation for specialized Activity report types.  Which do not solve the root problem if you actually need to aggregate all activities over a longer date range: