Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Using ActivityParty in a report RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    We have MSCRM 2013 and I am trying to create a report that will follow a sales cycle from campaign response to opportunity close.  When we get a campaign response for a lead that has never been in our database, we create a lead record and attach the campaign response to it. In some cases where leads have been converted to accounts and then gone cold we will get a new campaign response and attach it to the existing account record rather than create a new lead record. I need to be able to pull data from both Leads and Accounts in the same report. So far I am using the following tables: CampaignResponse, ActivityParty, Account, and Lead. I have Campaign response linked to ActivityParty using the ActivityId field in both tables, what field do I use to now link to both the Lead and Account tables from the ActivityParty. I have tried several different fields but all result in a blank report as soon as I insert a Lead specific field or an Account specific field.

    Thanks,

    Jean-Luc

    Monday, September 15, 2014 11:14 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Jean,

    Please use PartyId field to join Lead and contact.

    select A.name 
from FilteredAccount as A 
	Inner JOIN filteredActivityParty as AP on A.accountid = AP.PartyId
	
select L.firstname 
from Filteredlead as l 
	Inner JOIN filteredActivityParty as AP on L.leadid = AP.PartyId
    select A.name 
    from filteredActivityParty as AP 
         FULL OUTER JOIN FilteredAccount as A on A.accountid = AP.PartyId
    	FULL OUTER JOIN FilteredLead as L on L.leadid = AP.PartyId
    where (PartyObjectTypeCode = 1 or PartyObjectTypeCode = 4)


    Saad


    • Edited by Mohd Saad Tuesday, September 16, 2014 5:19 AM Added new query
    Tuesday, September 16, 2014 5:13 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    hi Jean-Luke,

    there is a view called filteredactivitypointer, filteredactivityparty that you could- join by activity id to get what you are looking for. Let us know if you need further help.

    please note that filtered views work only for onpremise. If you may go online in future, you have to look into fetch based reports.

    regards

    Jithesh

    Tuesday, September 16, 2014 8:57 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    thank you, will let you know how it goes
    Tuesday, September 16, 2014 8:21 PM