locked
FetchXML: Campaign Response to Opportunity RRS feed

  • Question

  • Hi,

    I have been trying to write a FetchXML that will show me all campaign reponses that are associated with all opportunties.  Using the code below, I have been pretty successful.  The only problem that I have run into is that only opportunities that have campaign responses are included in my results.  I would like to show all opportunties regardless of whether they have an associated campaign response.  Any suggestions for how I might do this?

    Thanks,

    Rachel

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="opportunity">
        <attribute name="name" />
        <attribute name="customerid" />
        <attribute name="estimatedvalue" />
        <attribute name="statuscode" />
        <attribute name="createdon" />
        <attribute name="opportunityid" />
        <order attribute="name" descending="false" />
        <link-entity name="lead" from="leadid" to="originatingleadid" alias="aa">
            <attribute name="leadid" />
            <attribute name="campaignid" />
            <attribute name="createdon" /> 

          <link-entity name="activityparty" from="partyid" to="leadid" alias="ab">
          <link-entity name="campaignresponse" from="activityid" to="activityid" alias="ac">
             <attribute name="activityid" />
             <attribute name="regardingobjectid" />
             <attribute name="receivedon" />
          </link-entity>
          </link-entity>
          </link-entity>
      </entity>
    </fetch>

    Friday, May 13, 2011 9:37 PM

Answers

All replies

  • Use link-type="outer" to specify an outer join in your <link-entity /> nodes.
    --pogo (pat) @ pogo69.wordpress.com
    • Marked as answer by R.Claudet Friday, May 20, 2011 10:18 PM
    Friday, May 13, 2011 10:25 PM
  • Another option can be just to simplify the query as I presume now no filter criteria for opportunities.

     


    Mohit Agrawal
    Sunday, May 15, 2011 4:23 PM
  • Hi Pat,

    Thanks for your response.  I tried adding the link-type="outer" to my <link-entity /> nodes.  I pasted the FetchXML below.  Unfortunately, it seems to return a result for each activity for a specific originating lead.  So if an originating lead has 50 emails, that result is returned 50 times in my report whether there is a campaign response for this originating lead or not.  BIDS appears to only return the first 500 results, so it doesn't look like this option will work for me.

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="opportunity">
        <attribute name="name" />
        <attribute name="customerid" />
        <attribute name="estimatedvalue" />
        <attribute name="statuscode" />
        <attribute name="createdon" />
        <attribute name="opportunityid" />
        <order attribute="name" descending="false" />
        <link-entity name="lead" from="leadid" to="originatingleadid" alias="aa" link-type="outer">
            <attribute name="leadid" />
            <attribute name="campaignid" />
            <attribute name="createdon" />

          <link-entity name="activityparty" from="partyid" to="leadid" alias="ab" link-type="outer">
          <link-entity name="campaignresponse" from="activityid" to="activityid" alias="ac" link-type="outer">
             <attribute name="activityid" />
             <attribute name="regardingobjectid" />
             <attribute name="receivedon" />
          </link-entity>
          </link-entity>
          </link-entity>
      </entity>
    </fetch>

    Monday, May 16, 2011 9:48 PM
  • Hi Mohit,

    Thanks for your response.  What do you mean by simplifying the query?  Can you please go into more detail?  Thanks again for your help.

    -Rachel

    Tuesday, May 17, 2011 2:52 PM
  • I think now you simply want to see all the opportunities irrespective of campaign response.
    Mohit Agrawal
    Wednesday, May 18, 2011 3:36 PM
  • Hi Pat,

    Thanks again for your answer.  I does work.  I just had to figure out how to return more than 500 records by importing the rdl into the CRM.  I'm still very new to this.

    Thanks again,

    Rachel

    Friday, May 20, 2011 10:17 PM