Customer Forum

Reporting on the last contact against an Opportunity

Sam (Workbooks Online) Posted: 2016-12-30 12:44

This forum post will show you how you can build a report to show the last time contact was made with a person related to an Opportunity. This will include both emails and Activities.




Why should I build this report?

This report will show you if your Opportunities are or aren't being followed up and therefore which ones need further action.


How do I build this report?

The steps below run through how to build this report.


Step 1: Create a report based on Opportunities

Navigate to Start > New > Report > Create a new blank report > Opportunities


Step 2: Add columns that are relevant to you

On the Details tab add the columns that are the most relevant and contain important information. You may find the following 'Opportunity' columns useful:

  • Id (rename to Opportunity Id)
  • Opportunity reference
  • Name (rename to Opportunity Name)
  • Prospective Customer Name
  • Assigned to name
  • Amount (home currency)
  • Stage


Because you don't email an Opportunity, you email the people related to the opportunity, you need to add the 'Email' columns you want to display by drilling through 'Related People > Emails > Email >', which may include:

  • Id (rename to Email Id)
  • Sent
  • Sender
  • Recipients
  • Subject
  • Context (You only need to drill through 'Related People > Emails >' to get to this one)


Add the 'Activity' columns you want to display by drilling through 'Activities >', which may include:

  • Id (rename to Activity Id)
  • Subject  (rename to Activity Subject)
  • Type (rename to Activity Type)
  • Completed date



Step 3: Add criteria to restrict your results

You may want to restrict your results further to only show a particular set of Opportunities, for example those which are open, assigned to particular people or not updated in the last 2 weeks. We've restricted the results by adding criteria to only show :

  • Opportunities which are in a State of Open. - State >  is > Open
  • Activities which are Completed (Record State is closed) - You need to add this as 'is not Open' rather than 'is Closed' otherwise it will exclude all Opportunities which may have emails but no Activities. - Activities > Record State > is not > Open
  • Emails which have been sent to a person related to the Opportunity (rather than received from) - Related People > Emails > Context > is not > From




Step 4: Create a summary view to group by Opportunity Id

This will allow you to see the last contact made with your Opportunities. 

  1. Click Add Summary View
  2. Add Grouping Column to group by 'Opportunity Id'
  3. Add Value Columns for:
    • 'Opportunity Name'
    • 'Amount (home currency)'
    • 'Assigned to name'
    • 'Stage'
  4. Add Summarised Columns for:
    • Completed Date
    • Sent
      For both of the above, set Calculate to 'Maximum' and un-tick 'Display summary' checkbox. 
  5. Add a Calculated Column called 'Last contact date', with the Formula below:


     IF(MAX(PARENT('Completed date'))>=MAX( PARENT('Sent')) OR MAX( PARENT('Sent')) IS NULL , MAX( PARENT('Completed date')), MAX( PARENT('Sent')))


  6. Add another Calculated Column called 'Days since last contact', with the Formula below:


    DATEDIFF(NOW(), IF(MAX(PARENT('Completed date'))>=MAX( PARENT('Sent')) OR MAX( PARENT('Sent')) IS NULL , MAX( PARENT('Completed date')), MAX( PARENT('Sent'))))



You can then hide any columns which aren't relevant anymore such as Opportunity Id to have a report which lists down all Open Opportunities and the last Contact date with them, as per the screenshot below. 



Step 5: Share this Report with those that will be using it.

By default, the report will only be shared with the System Admin Users. Use the padlock to share with any other users as appropriate.


Jeff Posted: Wed, 04.01.2017 - 18:22

Thanks so much. I was able to get the report to work. That works great for many cases. However, in some cases I could have one person that is related to more than one opportunity. I use the Workbook’s Outlook Connector to set the appropriate Opportunity for the email. So, is it possible to report off of only emails related to that Opportunity versus emails related to people that are related to that opportunity? Basically, report off of the Emails tab in the Opportunity versus off of the Related Items tab? Thanks so much.

Jeff Posted: Wed, 04.01.2017 - 18:36

After reviewing some, I may have that backwards. Report off the Related Items tab vs. Emails tab. Basically report off what you manually link to the Opportunity vs what is related to the person. Thanks.

Ross (Workbooks Online) Posted: Thu, 05.01.2017 - 12:17

Hi Jeff,

Thank you for your comments.

It is possible to Report on the emails that have been manually related to the Opportunities by instead of drilling through the related people, as suggested above, drilling straight through to the related emails themselves. This will only return results for emails that have the relationship as you have described above.

To illustrate this, the post above suggests using the following Column mapping;


The Column mapping that will bring only manually related emails as results is;


Please be aware that due to the nature of Reports it will not be possible to Report on the values from the two different Column mappings above using a single column. I would advise using Summary views or create a separate report to carry out the two different Reports.

Kind regards,

Workbooks Support