财神放到家里面哪个位置好

書(shū)名： Microsoft Dynamics CRM 2011 Reporting
作者名： Damian Sinay
本章字?jǐn)?shù)： 1231字
更新時(shí)間： 2021-08-13 16:58:59

FetchXML overview

FetchXML is a proprietary query language initially introduced in Dynamics CRM 3.0 and improved through the following versions. All the dynamics CRM views are created with this query language and we can now write reports in CRM 2011 using this query language as well.

The addition of this type of data source allows Dynamics CRM online to create reports where this is the only type of data source supported for CRM Online.

Note

The FetchXML queries have a limitation of 5000 records per page, so you need to have this in mind when working with them. If you are in CRM on-premise, you can tune this value by touching the registry, setting a value (1) of the TurnOffFetchThrottling DWORD key under HKLM\Software\Microsoft\MSCRM. For more information, refer to http://support.microsoft.com/kb/911510.

The best way to learn how to create a FetchXML query is by using the Advanced Find tool.

With this tool, you can easily create queries from where you can download the generated Fetch XML by clicking on the Download Fetch XML button.

Notice that this tool won't generate all types of queries (which we can generate manually with XML, such as grouping to create summary results, as we will see later on in this chapter).

Note

If you are more familiar with SQL sentences, there is an online tool available that converts SQL queries into Fetch XML queries, available at http://www.sql2fetchxml.com/.

A sample Fetch XML is as follows:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="name" />
    <attribute name="primarycontactid" />
    <attribute name="telephone1" />
    <attribute name="accountid" />
    <order attribute="name" descending="false" />
  </entity>
</fetch>

Every Fetch XML starts with a root node called fetch.

Note

The full schema of the Fetch XML can be found at http://msdn.microsoft.com/en-us/library/gg309405.aspx.

The first child is always the entity node where we specify the primary entity of our query.

To test the Fetch XML queries, we can download the CRM 2011 Fetch XML Execute Tool from CodePlex, which is located at http://crm2011fetchexecute.codeplex.com/.

When running this tool, we will need to enter the connection information as follows:

Both the Discovery Uri and Organization Uri URLs are the URLs we can find when we go to the CRM web interface and then go to Settings | customizations | Developer resources.

If you want to start writing a Fetch XML query manually, I suggest using the XML editor of Visual Studio; bind the fetch.xsd schema file that comes with the Dynamics CRM 2011 SDK, which can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=24004; fetch.xsd is located in the \sdk\schemas folder. Binding the schema will add IntelliSense into our XML editor to avoid misspellings when writing our Fetch XML queries.

If you don't know how to do that, the following is a step by step guide:

Open Visual Studio (it can be 2010 or 2012).
Go to File | New | File….
Select XML File and click on Open.
Go to the View menu and then click on the Properties window or hit the F4 key, as shown in the following screenshot:
In the Properties window, find the Schemas attribute and click on the … button, as shown in the following screenshot:
In the XML Schemas dialog, click on Add.
Find the fetch.xml file in the crm sdk\schemas folder as shown in the following screenshot:
Click on OK.
Now you will see full IntelliSense in your XML editor to write FETCH XML queries.

Select fields

To select fields, you add nodes of type attribute. For example, to return the telephone1 field of the account entity, we can write the following code:

<fetch version="1.0" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="telephone1" />
  </entity>
</fetch>

The following screenshot shows the selected field:

Tip

Notice the primary key field—in this case, the accountid field—will always be returned, so we don't need to add an attribute node for it.

To return more than one field, we just add more attribute nodes as follows:

<fetch version="1.0" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="telephone1" />
    <attribute name="name" />
  </entity>
</fetch>

Here is something you can do with Fetch XML that cannot be generated with the Advanced Find tool; it is equivalent to a select * from entity query in SQL.

To return all the fields of an entity, you can just pass the <all-attributes/> node; the following is an example:

<fetch distinct='false' mapping='logical'>
  <entity name='account'>  
    <all-attributes/>    
  </entity>
</fetch>

We will see the result as follows:

Filters and conditions

To filter, you add nodes of type filter and specify the AND or OR operator in the type attribute. Filters are equivalent to the WHERE clause in SQL.

The following is an example:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="telephone1" />
    <filter type="and">
      <condition attribute="name" operator="eq" value="Remoting Coders" />
    </filter>
  </entity>
</fetch>

In this example, we query the Account entity and return the telephone1 field by filtering the account name field that needs to be equal to a value.

The operators available to use are as follows:

eq (equal)
like (contains)
not-like (does not contain)
ne (not equal)
null (does not contain data)
not-null (contains data)

The following are examples of commands using these operators:

To filter by a field that is equal to a value, we use the following command:
```
<condition attribute="name" operator="eq" value="Remoting" />
```
To filter by a field that contains a value, we use the following command:
```
<condition attribute="name" operator="like" value="%Remoting%" />
```
To filter by a field that starts with another string, we use the following command:
```
<condition attribute="name" operator="like" value="Remoting%" />
```
To filter by a field that ends with a part of a string, we use the following command:
```
<condition attribute="name" operator="like" value="%Remoting" />
```
To filter by a field that is not equal to a value, we use the following command:
```
<condition attribute="name" operator="ne" value="Remoting" />
```
To filter by a field that contains data, we use the following command:
```
<condition attribute="name" operator="not-null" />
```
To filter by a field that does not contain data, we use the following command:
```
<condition attribute="name" operator="null" />
```

Order by

To order a field, you add a node of type order and specify the field you want to order in the attribute and the direction in the descending order of attributes; the following example will return the name and telephione1 fields of the account entity in the ascending order of the name field:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="name" />
    <attribute name="telephone1" />
    <order attribute="name" descending="false" />
  </entity>
</fetch>

To sort in the opposite order, you just change the descending attribute to true as follows:

<order attribute="name" descending="true" />

If you want to order by more than one field, you just add another order node as follows:

    <order attribute="name" descending="true" />
    <order attribute="telephone1" descending="false" />

This previous code will sort first by name in descending order and then by telephone1 in ascending order.

Group by

To group, you add the aggregate='true' attribute to the fetch node.

Just as in SQL, when using the group by option, you will need to either return the fields that are grouped or use aggregated functions for the other fields. The aggregated functions that are supported are as follows:

sum
avg
min
max
count

Further, examples of grouping the accounts by the name field to return the count of records with different names are as follows:

<fetch distinct='false' mapping='logical' aggregate='true'>
  <entity name='account'>
    <attribute name='name' aggregate='count' alias='counter'/>
  </entity>
</fetch>

Testing this code will give us the following result:

Linking to other entities

If you want to get the fields of one related entity, you will need to use the link-entity node and specify the join type. There are two types of joins supported by Fetch XML:

Inner join
Outer join

Inner join

The inner join will only show records of the primary entity that has a related record. For example, in the following code, we will return all the accounts that have a primary contact associated and will return the full name of the contact:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="name" />
    <attribute name="telephone1" />
    <order attribute="name" descending="false" />
    <link-entity name="contact" from="contactid" to="primarycontactid" visible="false" alias="praccount">
      <attribute name="fullname" />
    </link-entity>
  </entity>
</fetch>

Outer join

Contrary to an inner join, the outer join returns records that do not depend on the secondary entity records. To use outer join, change the link-type attribute to outer as follows:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="name" />
    <attribute name="telephone1" />
    <order attribute="name" descending="false" />
    <link-entity name="contact" from="contactid" to="primarycontactid" visible="false" link-type="outer" alias=" praccount">
      <attribute name="fullname" />
    </link-entity>
  </entity>
</fetch>

This example will return accounts regardless of the primary contact value, meaning that if there is no primary contact for that account, the record will be returned in the result with null value in the primary contact field.

官术网_书友最值得收藏!

Microsoft Dynamics CRM 2011 Reporting

FetchXML overview

Note

Note

Note

Select fields

Tip

Filters and conditions

Order by

Group by

Linking to other entities

Inner join

Outer join