Transcript Technical Academy Fetch Xml • Were can Fetch Xml be used • Basic Fetch Xml and using advanced find to build your own.

Technical Academy
Fetch Xml
• Were can Fetch Xml be used
• Basic Fetch Xml and using advanced find to build your own Fetch
Query
• Creating Fetch Xml that containing outer joins
• Creating Fetch Xml Queries that contain aggregates
• How to generate advanced CRM views using Fetch Xml
• Reference Materials
@CRMUG
Were can Fetch Xml be used
 Using the CRM SDK using
OrganizationService.RetrieveMultiple request
 SSRS reports authored with Report Authoring
Extension
 Creating advanced views you are unable to create
using the GUI advanced find interface
 JavaScript using the SOAP protocol and an
OrganizationService.RetrieveMultiple request
– Usually Developers will user the ODATA Rest
Endpoint to retrieve multiple with JS because of its
ease of use
@CRMUG
C# Retrieve Multiple Example
string FetchXml = string.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical'
distinct='false'>
<entity name='systemuser'>
<attribute name='systemuserid' />
<order attribute='fullname' descending='false' />
<filter type='and'>
<condition attribute='internalemailaddress' operator='eq' value='{0}' />
</filter>
</entity>
</fetch>", ConfigurationManager.AppSettings["FromEmailAddress"]);
EntityCollection ec = service.RetrieveMultiple(new FetchExpression(FetchXml));
@CRMUG
C# Fetch Xml Warnings
 For larger data (larger than 5000) sets when
using Fetch Xml within CRM you will have to
paginate your retrieval of the records.
 Example
 The example provided just outputs the results
onto a console screen, when I develop items
I will with place them into a List<Entity> or
process 5000 records at a time depending on
the requirements.
@CRMUG
Report Authoring Extension
 Small application that must be installed on a
computer
 CRM 2015 Report Authoring Extension
– Visual Studio 2012 or 2010 with SQL Server Data
Tools required.
 CRM 2013 Report Authoring Extension
– BIDS and Visual Studio 2008 SP 1 required or Visual
Studio 2010 with SQL Server Data Tools
 CRM 2011 Report Authoring Extension
– BIDS and Visual Studio 2008 SP 1 required or Visual
Studio 2010 with SQL Server Data Tools
@CRMUG
Using Advanced Find to build
Fetch Xml
 Demonstration
 https://clecrmug.crm.dynamics.com
– [email protected]
@CRMUG
Fetch Xml Basics
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="contact">
<attribute name="contactid" />
<attribute name="fullname" />
<attribute name="emailaddress1" />
<attribute name="telephone1" />
<order attribute="fullname" descending="false" />
<filter type="and">
<condition attribute="address1_stateorprovince" operator="eq" value="OH" />
<condition attribute="ownerid" operator="eq-userid" />
</filter>
<link-entity name="account" from="accountid" to="parentcustomerid" alias=“account">
<attribute name="industrycode" />
<attribute name="revenue" />
<attribute name="name" />
<filter type="and">
<condition attribute="industrycode" operator="in">
<value>1</value>
<value>37</value>
<value>6</value>
</condition>
</filter>
</link-entity>
</entity>
</fetch>
@CRMUG
SQL Equivalent
select contact.contactid,
contact.fullname,
contact.emailaddress1,
contact.telephone1,
account.industrycode,
account.revenue,
account.name
from filteredcontact contact
inner join filteredaccount account on account.accountid=contact.parentcustomerid
where contact.address1_stateorprovince='OH'
and contact.ownerid=@UserGuid
and account.industrycode in (1,37,6)
order by contact.fullname asc
@CRMUG
Breaking Down the Basics
 Header and Xml Definition
 <fetch version="1.0" output-format="xmlplatform" mapping="logical" distinct="false">
 This line is static and should be used in each
of your fetch statements.
@CRMUG
Breaking Down the Basics
 Primary Entity
 <entity name="contact">
 Defines the entity that will be the frame of
reference for the remainder of the report.
 If you are using this for SSRS reporting and
want to use the Pre Filtering functionality this
can be done with XML formatted like:<entity
name=“contact” enableprefiltering=“true”
prefilterparametername=“ContactFilter”>
 SQL equivalent of from
@CRMUG
Breaking Down the Basics
 Attribute selection
– <attribute name="contactid" />
 The attribute name must match the schema
name from your Dynamics CRM environment.
 SQL equivalent of select
 Available attributes of the attribute tag
–
–
–
–
alias
aggregate
dategrouping(only for date fields)
groupby
@CRMUG
Breaking Down the Basics
 Filtering
–
<filter type=“and“>
<condition attribute=“address1_stateorprovince” operator=“eq” value=“OH” />
<condition attribute=“ownerid” operator=“eq-userid” />
</filter>
 SQL Equivalent of where
 Common operators
–
–
–
eq is the equal requires a value
eq-userid is current user no associated value
in is for Pick lists and Lookups ONLY and have <value> tags for each value
• <condition attribute="industrycode" operator="in">
<value>1</value>
<value>37</value>
<value>6</value>
</condition>
–
–
–
next-x-days requires an integer value
null is the does not contain data this has no associated value
not-null is the contains data this has no associated value
@CRMUG
Breaking Down the Basics
 Sorting
– <order attribute="fullname" descending="false" />
 SQL equivalent of Order By
 This line MUST be placed under an entity tag
and can never be placed under a linked
entity tag
– This means you are still bound by the limitation
that you can only sort by fields that exist with the
primary entity just like views in CRM.
 Descending attribute can be true or false
@CRMUG
Breaking Down the Basics
 Link Entity
– <link-entity name="account" from="accountid"
to="parentcustomerid" alias=“account">
 SQL equivalent of join
 Can contain an additional attribute of linktype to define if this is an inner or outer join.
– More on this later
@CRMUG
Creating Fetch Xml that
containing outer joins
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="lead">
<attribute name="fullname" />
<link-entity name="task" from="regardingobjectid" to="leadid" alias=“t" link-type="outer">
<attribute name="regardingobjectid" />
</link-entity>
<filter type="and">
<condition entityname=“t” attribute="regardingobjectid" operator="null" />
</filter>
</entity>
</fetch>



The above query is a list of all leads that do not have a task regarding them.
Things to note is the filter is outside of the link-entity and uses an aliased entity name to look for the null
reference.
Similar methodologies can be used to generate views with outer joins that you do not want to have an
inner join.
@CRMUG
SQL Equivalent
select lead.fullname
from filteredleads lead
left outer join filteredtasks task on lead.LeadId=task.RegardingObjectId
where task.RegardingObjectId is null
@CRMUG
Creating Fetch Xml Queries
that contain aggregates
 Why user aggregates instead of retrieving the
full data set and using grouping in SSRS
– Fetch Xml data sets can be very large and if you
are pulling the entire detail for larger data sets the
execution time can be quite long for users.
 There is a CRM parameter called AggregateQueryRecordLimit
which is set to 50,000 records. This means that no single
aggregate can contain more that 50,000 distinct recrods. For
CRM on premise this can be altered using C# or PowerShell
note: this change is at the server level and NOT at the
organization level. CRM online this value cannot be changed.
@CRMUG
Creating Fetch Xml Queries
that contain aggregates
<fetch distinct='false' mapping='logical' aggregate='true'>
<entity name='opportunity'>
<attribute name='opportunityid' alias='opportunity_count' aggregate='count'/>
<attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/>
<attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg'/>
<attribute name='actualclosedate' groupby='true' dategrouping='quarter' alias='quarter' />
<attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' />
<order alias='year' descending='false' />
<order alias='quarter' descending='false' />
<filter type='and'>
<condition attribute='statecode' operator='eq' value='Won' />
</filter>
</entity>
</fetch>

Notice the use of the group by to get groupings by year or to include which level you want your
aggregates.

Notice how the order uses the alias instead of the attribute that we used previously
@CRMUG
Creating Fetch Xml Queries
that contain aggregates
 Available aggregation types
–
–
–
–
–
–
sum
avg
min
max
count(*)
count(attribute name)
@CRMUG
Creating Views in CRM with
an Outer Join
 Demonstration
 https://clecrmug.crm.dynamics.com
– [email protected]
@CRMUG
Reference Materials
 Fetch Xml schema
 Fetch Xml based Reports: Bits & Pieces
 Microsoft Dynamics CRM 2011 – Develop
Fetch Xml Based SSRS Reports in Visual Studio
2008
 MSDN Building queries with Fetch Xml
@CRMUG
@CRMUG