Sunday, September 30, 2012

Outer Joins in Fetch XML queries in CRM2011

This blog will walk you through on how and when to use “Outer” joins in CRM2011 fetch queries. Outer joins can be very important for  fetch xml based SSRS reports.
Here are some of the examples.

Example 1

Creating a query to retrieve  opportunities with fields from related account entity. Here is the query.
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="opportunity">
    <attribute name="name" />
    <attribute name="customerid" />
     <attribute name="estimatedvalue_base" />
     <order attribute="name" descending="false" />
    <link-entity name="account" from="accountid" to="customerid" visible="false" link-type="outer" alias="accountid">
      <attribute name="telephone1" />
    </link-entity>
</entity>
</fetch>

This query will return name(Topic), customerid(Potential Customer), estimatedvalue_base(Estmated Revenue), telephone1(Main Phone from account entity).
This query will return the above columns for all the opportunities. What will happen if the potential customer on opportunity is a contact instead of an account? The answer is it will still return all the opportunities as there is an outer join between the two entities. It will return the empty telephone 1 column. If we remove the link-type="outer" then the query won’t return any opportunities that have contacts as potential customers.
This query is also possible using advance find.

Example 2

What if we want to return all the opportunities and also any phone call activities related to these opportunities. This is also achievable using outer joins. There is 1:N relationship between opportunity and activitypointer entity. Therefore the query will return multiple records for the same opportunity if there are more than one phone call recorded against an opportunity. Here is fetch xml for the query
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="opportunity">
    <attribute name="name" />
    <attribute name="customerid" />
    <attribute name="estimatedvalue_base" />
     <order attribute="name" descending="false" />
    <link-entity name="account" from="accountid" to="customerid" visible="false" link-type="outer" alias="accountid">
      <attribute name="telephone1" />
    </link-entity>
<link-entity name="activitypointer" from="regardingobjectid" to="opportunityid" visible="false" link-type="outer" alias="phonecall">
      <attribute name="subject" />
 <attribute name="description" />
 <attribute name="createdon" />
<filter type="and">
      <condition attribute="activitytypecode" operator="eq" value="4210" />
</filter>
    </link-entity>
  </entity>
</fetch>

The above fetch xml will retrieve the subject, description, createdon  columns for related phone call activities as well as the columns mentioned in example one.
I have created a sample SSRS report using the above fetch xml query. Here is result. You can change the display name for the columns in the data set as required.

image

5 comments:

  1. What if I want to use right outer Join in FetchXML?

    ReplyDelete
  2. Hii
    I Want to connect BIDS with my crm 2011 online 365 account but its gives error (Unable to connect data source 'Source'.The connection to server "https://disco.crm5.dynamics.com/XRMServices/2011/Discovery.svc"could not be established.Make sure the connection string and credentials are correct,and try again.)

    Please, how to solve this issue.

    Regards
    Mohit Sharma

    ReplyDelete
  3. i think outer joins not supported in CRM2011

    ReplyDelete
  4. Interesting blog. This is one of my favorite blog also I want you to update more post like this. Thanks for sharing this article.
    Best Telephone Connection Providers Chennai

    ReplyDelete