locked
CRM 2011 - Get Last PhoneCall and E-mail Activity of the Opportunity and Account RRS feed

  • Question

  • Hi All,

    I want to show last activity of type phone call and e-mail against account and opportunity in the SSRS report.

    This report table will have columns from all these 3 entities. Somehow I am not able to join these 3 where I can get

    Account name, Opportunity Revenue, last phone call created date, last email created date in one row of SSRS report.

    If someone can show me SQL query for above scenario then it will be much appreciated.

    Basically, our end user wants to know which was the last activity performed against particular Account and its related opportunity.

    Thanks in advance.

    -CT

    Thursday, February 13, 2014 10:21 PM

Answers

  • Hi

    Try this : 

    select A.name,X.name, ( select top 1 createdon from FilteredPhoneCall B join FilteredActivityParty C  on B.activityid = C.activityid where C.partyid = A.accountid  order by B.createdon desc) as PhonecallDate,
    ( select top 1 createdon from FilteredEmail D join FilteredActivityParty E  on D.activityid = E.activityid where E.partyid = A.accountid  order by D.createdon desc ) as EmailDate,
    X.estimatedvalue as Revenue, X.accountidname
     from FilteredAccount A
      left join FilteredOpportunity X
       on A.accountid = X.accountid
       order by A.name

    1. This will list activities where company is a direct party in Either To, From or Regarding fields. Activities on Companies contacts, qualified leads will not be listed in this.

    2. Regarding opportunity, there can be multiple opportunities on one company. So which one do you want to pick ? This query will give one row per opportunity. So if there will be multiple opportunity, you will have multiple rows for same account.

    • Marked as answer by CRM Thirsty Sunday, February 16, 2014 8:28 PM
    Friday, February 14, 2014 9:08 AM

All replies

  • Hi

    Try this : 

    select A.name,X.name, ( select top 1 createdon from FilteredPhoneCall B join FilteredActivityParty C  on B.activityid = C.activityid where C.partyid = A.accountid  order by B.createdon desc) as PhonecallDate,
    ( select top 1 createdon from FilteredEmail D join FilteredActivityParty E  on D.activityid = E.activityid where E.partyid = A.accountid  order by D.createdon desc ) as EmailDate,
    X.estimatedvalue as Revenue, X.accountidname
     from FilteredAccount A
      left join FilteredOpportunity X
       on A.accountid = X.accountid
       order by A.name

    1. This will list activities where company is a direct party in Either To, From or Regarding fields. Activities on Companies contacts, qualified leads will not be listed in this.

    2. Regarding opportunity, there can be multiple opportunities on one company. So which one do you want to pick ? This query will give one row per opportunity. So if there will be multiple opportunity, you will have multiple rows for same account.

    • Marked as answer by CRM Thirsty Sunday, February 16, 2014 8:28 PM
    Friday, February 14, 2014 9:08 AM
  • Hi,

    Thanks for your help. I think I was missing nested select statement trick.

    -CT

    Sunday, February 16, 2014 8:28 PM