Technical Academy Fetch Xml - PowerPoint Presentation

Slide1

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 MaterialsSlide2

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 useSlide3

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

));Slide4

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.Slide5

Report Authoring Extension

Small application that must be installed on a computerCRM 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

ToolsSlide6

Using Advanced Find to build Fetch Xml

Demonstration

https://clecrmug.crm.dynamics.com

crmadmin@clecrmug.onmicrosoft.comSlide7

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>Slide8

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

ascSlide9

Breaking Down the Basics

Header and Xml Definition<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

This line is static and should be used in each of your fetch statements.Slide10

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 fromSlide11

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)

g

roupbySlide12

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 valueSlide13

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 falseSlide14

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 link-type to define if this is an inner or outer join.

More on this laterSlide15

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.Slide16

SQL Equivalent

select lead.fullname

from

filteredleads

lead

left outer join

filteredtasks

task on

lead.LeadId

=

task.RegardingObjectId

where

task.RegardingObjectId

is nullSlide17

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.Slide18

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 previouslySlide19

Creating Fetch Xml Queries that contain aggregates

Available aggregation types

sum

avg

min

max

c

ount(*)

c

ount(attribute name)Slide20

Creating Views in CRM with an Outer Join

Demonstrationhttps://clecrmug.crm.dynamics.com

crmadmin@clecrmug.onmicrosoft.comSlide21

Reference Materials

Fetch Xml schemaFetch Xml based Reports: Bits & Pieces

Microsoft Dynamics CRM 2011 – Develop Fetch Xml Based SSRS Reports in Visual Studio 2008

MSDN Building queries with Fetch XmlSlide22

Q&A

Connect

Learn

Share