Introducing the fetch query capabilities for generating dashboard and reports using HTML or SSRS reports. We excited to highlight some of the capabilities that fetch xml customization to retrieve data for complicated data structure.

We can see below query for sql server where user can get data using left outer join.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

Consider below scenario when we use sql server :

Account Entity

Account IDAccountAddress
1Alfreds FutterkisteAvda. de la Constitución 2222
2Ana TrujilloObere Str. 57
3Antonio Moreno TaqueríaMataderos 2312
4Around the HornBerlin
11B’s BeveragesBerlin
6Blauer See DelikatessenBerlin
5Berglunds snabbkopNewyork

Order Entity

OrderIDAccount IDOrder Total
103082100000.00$
103653201232.00$
103554323240.00$
103834324324.00$
10289114324.00$
102785423423.00$
10280542342.00$
103845424322.00$

All Customers Order Details

AccountOrder ID
Alfreds Futterkistenull
Ana Trujillo10308
Antonio Moreno10365
Around the Horn10355
Around the Horn10383
B’s Beverages10289
Berglunds snabbköp10278
Berglunds snabbköp10280
Berglunds snabbköp10384
Blauer See Delikatessennull

When users cannot find the order details in related while looking for account

order-details

Some of the customer requirements are subjected to 360 view, where MSCRM dashboards have limitations. This deviates to the development of custom dashboards using HTML page. Same we can achieve on MSCRM by customizing the fetch query without using the XML toolbox.

Below advance find fetch all orders for customers with Order ID

fetch-order-id
list-tools

Download the fetch using advanced as shown above:
Customize below fetch xml

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>
<entity name=”salesorder”>
<attribute name=”salesorderid” />
<attribute name=”name” />
<order attribute=”salesorderid” descending=”false”/>
<link-entity name=”account” from=”accountid” to=”customerid” visible=”false” link-type=”outer” alias=”account”>
<attribute name=”name” />
</link-entity>
</entity>
</fetch>

After Customization this will look like below:
-replacing and inverting the link entity and attributes as shown below

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>
<entity name=”account“>
<attribute name=”name” />
<link-entity name=”salesorder” from=”customerid” to=”accountid” visible=”false” link-type=”outer” alias=”account”>
<attribute name=”salesorderid” />
<attribute name=”name” />
<order attribute=”salesorderid” descending=”false”/>
</link-entity>
</entity>
</fetch>

Let’s copy this to crm rest builder and evaluate the output needed

First two values are null same as we have seen in sql output above:
Remember Output in SQL account not having orders

All Customers and Order Details

AccountOrder ID
Alfreds Futterkistenull
Blauer See Delikatessennull

CRM Rest Builder Output:

value:[
• {
o @odata.etag:”W/”6377346″”,
o name:”Alfreds Futterkiste”,
o accountid:”d94b4766-bbe8-e811-a97f-000d3ab11b7a”

},
• {
o @odata.etag:”W/”6377356″”,
o name:”Blauer See Delikatessen”,
o accountid:”edd68291-bbe8-e811-a97f-000d3ab11b7a”

},
• {
o @odata.etag:”W/”6377348″”,
o name:”Ana Trujillo”,
o accountid:”82db5372-bbe8-e811-a97f-000d3ab11b7a”,
o salesorder_x002e_salesorderid:”e7a283e8-bbe8-e811-a97d-000d3ab27d56″,
o salesorder_x002e_name:”10308″
},
• {
o @odata.etag:”W/”6377350″”,
o name:”Antonio Moreno Taquería”,
o accountid:”a0a89a7e-bbe8-e811-a97f-000d3ab11b7a”,
o salesorder_x002e_salesorderid:”5f27bcfa-bbe8-e811-a97d-000d3ab27d56″,
o salesorder_x002e_name:”10365″
},
• {
o @odata.etag:”W/”6377352″”,
o name:”Around the Horn”,
o accountid:”ca497485-bbe8-e811-a97f-000d3ab11b7a”,
o salesorder_x002e_salesorderid:”cb5ac130-bce8-e811-a97d-000d3ab27d56″,
o salesorder_x002e_name:”10355″
},
• {
o @odata.etag:”W/”6377352″”,
o name:”Around the Horn”,
o accountid:”ca497485-bbe8-e811-a97f-000d3ab11b7a”,
o salesorder_x002e_salesorderid:”9a517543-bce8-e811-a97d-000d3ab27d56″,
o salesorder_x002e_name:”10383″
},
• {
o @odata.etag:”W/”6377354″”,
o name:”B’s Beverages”,
o accountid:”88758c8b-bbe8-e811-a97f-000d3ab11b7a”,
o salesorder_x002e_salesorderid:”adbfa555-bce8-e811-a97d-000d3ab27d56″,
o salesorder_x002e_name:”10289″
},
• {
o @odata.etag:”W/”6377358″”,
o name:”Berglunds snabbkop”,
o accountid:”0c238f97-bbe8-e811-a97f-000d3ab11b7a”,
o salesorder_x002e_salesorderid:”7e0ea265-bce8-e811-a97d-000d3ab27d56″,
o salesorder_x002e_name:”10278″
},
• { o @odata.etag:”W/”6377358″”,
o name:”Berglunds snabbkop”,
o accountid:”0c238f97-bbe8-e811-a97f-000d3ab11b7a”,
o salesorder_x002e_salesorderid:”6b6caf71-bce8-e811-a97d-000d3ab27d56″,
o salesorder_x002e_name:”10280″
},
• {
o @odata.etag:”W/”6377358″”,
o name:”Berglunds snabbkop”,
o accountid:”0c238f97-bbe8-e811-a97f-000d3ab11b7a”,
o salesorder_x002e_salesorderid:”c1c0b77d-bce8-e811-a97d-000d3ab27d56″,
o salesorder_x002e_name:”10384″
}

Hope this gives little information. You can do this for more complex requirements and data structure .Let me add in part 2 for complex fetching of data, in my next blog.
Happy Coding…!
Thank you.

NSquare Xperts
info@nsquarexperts.com