FetchXML reports do not trigger RetrieveMultiple plugins in CRM 2011

 

It is by going down into the abyss that we recover the treasures of life. Where you stumble, there lies your treasure.
Joseph Campbell

The CRM forums are a great resource for learning interesting solutions and problems in Microsoft Dynamics CRM

I read this interesting gotcha/CRM Quirk this week about a CRM developer who had written a plugin which was triggered on the RetrieveMultiple event on his custom entity.  The user was wondering why his plugin was not being triggered when the user ran a report using FetchXML.

RetrieveMultiple Plugins, What, Why and How?

So you can have plugins which trigger on RetrieveMultiple for an entity.  Some readers are thinking why would you want to do that?

Reasons like

  • Translate data held in the CRM database
  • Exclude data from RetrieveMultiple queries
  • Log RetrieveMultiple requests
  • Calculate some fields in the retrieve from other entities
  • Decrypting encrypted fields

There are lots of other reasons, but they give you an idea.  The user wouldn’t even know a plugin had run because they would just receive the results as normal.

The retrieve multiple is triggered in CRM when the user

  • Runs an advanced find
  • User clicks on a view (e.g. active accounts)
  • Quick search
  • Associated views
  • Plugins doing retrieve multiples
  • Console apps doing retrieve multiples

 

What about reports and FetchXML

So why don’t reports and FetchXML trigger the RetrieveMultiple plugin

The answer was explained in this forum post answer by CRM legend David Jennaway who is a CRM MVP, when I was starting in CRM development David had a fantastic CRM blog which I followed avidly and still do, but but unfortunately for the CRM community David rarely writes any blog posts these days (2 post in 2014).

Reports do not trigger plugins. SQL reports bypass CRM completely, and Fetch reports also bypass the plugin pipeline
 He also add this answer
Just to clarify the last post, in CRM 2011 fetch queries are submitted via the RetrieveMultiple message, whereas in CRM 4 they were submitted via the Fetch message and (bizarrely) you could handle them in plugins on the Execute message
This logically makes sense because SQL reports definitely would hit the plugin pipeline and I’m guessing FetchXML when run from reports must be run using a slightly different mechanism (I have no knowledge of this)
I’m not sure if this is the same in CRM 2013 but I’m guessing it’s probably going to be the same because I don’t think there have been changes in those areas of functionality.

Build Queries with FetchXML instead of QueryExpression

When developing I like a good QueryExpression because once you have got one in code it’s easy to change it to the new query you are doing.  I usually work out the rough logic with an advanced find and then download the xml to see the names of the fields etc.

You can read some of my great blog posts on QueryExpression’s below or read on for some FetchXML fun

CRM 2011 – QueryExpressions where a field is null

CRM 2011 – How to select an Id in a QueryExpression

CRM 2011 – How to do Like statement in QueryExpression

Recently I was doing a QueryExpression but I need to link the query expression to two or three different entities and the query expression was getting out of hand.  I could do the query with an Advanced find so I was thinking if I could just use that it would be a whole lot easier than trying to convert the FetchXML to a QueryExpression.   So after a bit of googling I found that you can use FetchXML queries inside plugins.

It was while I was studying for CRM 2011 Extending CRM exam that I found this page – Build Queries With FetchXML and this line

A FetchXML query can be executed by using the IOrganizationService.RetrieveMultiple method. You can convert a FetchXML query to a query expression with the FetchXmlToQueryExpressionRequest message.

If you want to use FetchXML in your plugin/Custom Workflow you will need to download the FetchXML from your advanced find.  There is a very helpful button on the advanced find that appears after you have run the advanced find, which you can see below magnetismsolutions blog)

You will have to do a bit of conversion from the downloaded fetchXML because all the fields will be in double quotes and these will need to be in single quotes and you will need to put an @ at the front and double quotes around the whole FetchXML query whilst you save it to a string variable.  You then pass the string RetrieveMultiple as shown in the example below

The example below is taken from here

// Retrieve all accounts owned by the user with read access rights to the accounts and 
// where the last name of the user is not Cannon. 
string fetch2 = @"
   <fetch mapping='logical'>
     <entity name='account'> 
        <attribute name='accountid'/> 
        <attribute name='name'/> 
        <link-entity name='systemuser' to='owninguser'> 
           <filter type='and'> 
              <condition attribute='lastname' operator='ne' value='Cannon' /> 
           </filter> 
        </link-entity> 
     </entity> 
   </fetch> "; 

EntityCollection result = _serviceProxy.RetrieveMultiple(new FetchExpression(fetch2));

foreach (var c in result.Entities)
   {
   System.Console.WriteLine(c.Attributes["name"]);
   }

The results are same EntityCollection you would get from a QueryExpression, which makes it easy to reuse code you have written for query expressions.

The other thing you will probably need to do is integrate values and guids into the FetchXML.  You can pass in a guid and then put in double quotes, the guid variable with a + sign either side.  You can see the example below

Guid contactGuidVariable;

EntityReference contactEntityRef;

string fetch = @”<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’false’>
<entity name=’contact’>
<attribute name=’fullname’ />
<attribute name=’address1_upszone’ />
<attribute name=’address1_line1′ />
<attribute name=’address1_postalcode’ />
<attribute name=’createdon’ />
<attribute name=’contactid’ />
<order attribute=’fullname’ descending=’false’ />
<filter type=’and’>
<condition attribute=’statecode’ operator=’eq’ value=’0′ />
<condition attribute=’contactid’ operator=’eq’ uiname=’BEN HOSK’ uitype=’contact’ value='” + ContactGuidVariable + “‘ />
</filter>
</entity>
</fetch>”

if you pass in a guid you can just pass in the guid variable but if it’s an EntityReference then you would need to contactEntityRef.Id

Microsoft have written some good articles on this you can read here

Build Queries With FetchXML

Use FetchXML to Construct a Query – this one is good because it has code examples, which I have borrowed in this blog

Some good articles on this are

http://www.magnetismsolutions.com/blog/roshanmehta/2012/04/16/dynamics_crm_2011_querying_data_using_fetchxml

CRM 2011 – displaying similar quote products in a subgrid by injecting FetchXML

I had a requirement this week where a customer was adding products to a quote and when they selected a product they then want to see previous quotes which featured the same product and the price and quantity it sold for.  Basically the sales person wanted to see what the product had been sold for before to make sure the price they were quoting wasn’t too radical.

The idea I came up with was to have a grid on the Quote Product form, which displayed quote products of the same type as the quote product selected.

This would mean I would have to dynamically change a view or show items in the grid.

I created a view showing the fields of the Quote Product and then specified a product I was searching for.

I found this excellent blog post

CRM 2011 – Change Subgrid View Java Script

This blog post explains how you can load in new fetchXML into a subgrid, you can inject the FetchXML on the form onload, which enables you to get some variables from the form and inject them into FetchXML which then is used to update the SubGrid.

So on my Quote Product form I checked to see if a product had been selected on the form OnLoad, on this particular form the subgrid doesn’t load until you have selected a product, unit and a quantity, which then reloads the form.

The code itself which I have taken and changed from the blog post referenced above, is quite clever.  The function is called and if the SubGrid has not been loaded, it calls the function again after a small time delay.  This ensures the SubGrid is loaded before you try and inject the fetchXML into the view.

The FetchXML is created, I got the fetchXML by doing an advanced find on my previously created view, there is a button which allows you to retrieve the FetchXM, which is very useful.

function UpdateSubGrid() {
var quoteGrid = document.getElementById("Quote_Products");
var productId = Xrm.Page.getAttribute("productid").getValue();
if (productId != null){
//If this method is called from the form OnLoad, make sure that the grid is loaded before proceeding
 if (quoteGrid == null) {
 //The subgrid hasn't loaded, wait 1 second and then try again
 setTimeout('UpdateSubGrid()', 1000);
 return;
 }

var fetchXml = ""
+ " <entity name='quotedetail'>"
+ " <attribute name='productid' />"
+ " <attribute name='productdescription' />"
+ " <attribute name='priceperunit' />"
+ " <attribute name='quantity' />"
+ " <attribute name='extendedamount' />"
+ " <attribute name='salesrepid' />"
+ " <attribute name='uomid' />"
+ " <attribute name='manualdiscountamount' />"
+ " <attribute name='baseamount' />"
+ " <attribute name='new_quoteid' />"
+ " <attribute name='quotedetailid' />"
+ " <order attribute='productid' descending='false' />"
+ " <filter type='and'>"
+ " <condition attribute='productid' operator='eq' uiname='1 Function Easy Clean Mini Kit' "
+ " uitype='product' value='" + productId[0].id +"' />"
+ " </filter>"
+ " <link-entity name='quote' from='quoteid' to='quoteid' visible='true' link-type='outer' alias='a_ff5a49bd001a45f7a14dd99a28f37f91'>"
+ " <attribute name='customerid' />"
+ " <attribute name='name' />"
+ " <attribute name='quotenumber' />"
+ " <attribute name='quoteid' />"
+ " </link-entity>"
+ " <link-entity name='product' from='productid' to='productid' visible='false' link-type='outer' alias='a_e0b689bded554f07ab0b241a932d482e'>"
+ " <attribute name='productnumber' />"
+ " </link-entity>"
+ " </entity>"
+ "</fetch>";
 // alert(fetchXml);
 //Inject the new fetchXml
 quoteGrid.control.setParameter("fetchXml", fetchXml);
 //Force the subgrid to refresh
 quoteGrid.control.refresh();
}

CRM 2011 – How to execute Fetch XML in Javascript

[tweetmeme source=”BenHosk” only_single=false]

The chaps over at Customer Effective blog have written a really interesting blog post this week.  It’s called

Execute Fetch from Javascript in CRM 2011

They actually also wrote an interesting article about why Microsoft buying skype made sense, which you can read here, I found this interesting because I was thinking Microsoft had completely overpaid but then the article  mentions skype have 636 million users, Microsoft paid $8.6 billion and that works out at $14.70 per/user price.  Now that’s not much for each users and you would probably think that skype is going to grow the number of users it has.  You can see Microsoft bundling skype in with Xbox, CRM and office etc.  The last thought I had was Microsoft has money burning a hole in it’s pocket.  The only danger is that someone else/Google could create a skype competitor but then I thought the same about youtube when google brought that and it seems to be working out ok.

Anyway I’m here to talk about Javascript running fetch xml.  The article has some neat javascript, so neat that I read it about 4 times, thinking that is nice and simple (like all good code).

below is the code from the blog.   Brilliant blog entry and if you are interesting in CRM 2011 then you really should subscribe to the Customer Effective Blog because it is one of the best CRM blogs out there, go on go and subscribe here.

FetchUtil.js

var XMLHTTPSUCCESS = 200;
var XMLHTTPREADY = 4;

function FetchUtil(sOrg,sServer)
{
this.org = sOrg;
this.server = sServer;

if (sOrg == null) {
if (typeof(ORG_UNIQUE_NAME) != “undefined”) {
this.org = ORG_UNIQUE_NAME;
}
}

if (sServer == null){
this.server = window.location.protocol + “//” + window.location.host;
}
}

FetchUtil.prototype._ExecuteRequest = function(sXml, sMessage, fInternalCallback, fUserCallback)
{
var xmlhttp = new XMLHttpRequest();
xmlhttp.open(“POST”, this.server + “/XRMServices/2011/Organization.svc/web”,(fUserCallback!=null));
xmlhttp.setRequestHeader(“Accept”, “application/xml, text/xml, */*”);
xmlhttp.setRequestHeader(“Content-Type”, “text/xml; charset=utf-8”);
xmlhttp.setRequestHeader(“SOAPAction”, “http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute“);

if (fUserCallback!=null)
{
//asynchronous: register callback function, then send the request.
var crmServiceObject = this;
xmlhttp.onreadystatechange = function(){ fInternalCallback.call(crmServiceObject,xmlhttp,fUserCallback) };
xmlhttp.send(sXml);
}
else
{
//synchronous: send request, then call the callback function directly
xmlhttp.send(sXml);
return fInternalCallback.call(this,xmlhttp,null);
}
}

FetchUtil.prototype._HandleErrors = function(xmlhttp)
{
/// <summary>(private) Handles xmlhttp errors</summary>
if (xmlhttp.status != XMLHTTPSUCCESS) {
var sError = “Error: ” + xmlhttp.responseText + ” ” + xmlhttp.statusText;
alert(sError);
return true;
} else {
return false;
}
}

FetchUtil.prototype.Fetch = function(sFetchXml, fCallback)
{
/// <summary>Execute a FetchXml request. (result is the response XML)</summary>
/// <param name=”sFetchXml”>fetchxml string</param>
/// <param name=”fCallback” optional=”true” type=”function”>(Optional) Async callback function if specified. If left null, function is synchronous </param>
var request = “<s:Envelope xmlns:s=\”http://schemas.xmlsoap.org/soap/envelope/\“>”;
request += “<s:Body>”;

request += ‘<Execute xmlns=”http://schemas.microsoft.com/xrm/2011/Contracts/Services“>’ +
‘<request i:type=”b:RetrieveMultipleRequest” ‘ +
‘ xmlns:b=”http://schemas.microsoft.com/xrm/2011/Contracts“‘ +
‘ xmlns:i=”http://www.w3.org/2001/XMLSchema-instance“>’ +
‘<b:Parameters xmlns:c=”http://schemas.datacontract.org/2004/07/System.Collections.Generic“>’ +
‘<b:KeyValuePairOfstringanyType>’ +
‘<c:key>Query</c:key>’ +
‘<c:value i:type=”b:FetchExpression”>’ +
‘<b:Query>’;

request += CrmEncodeDecode.CrmXmlEncode(sFetchXml);

request += ‘</b:Query>’ +
‘</c:value>’ +
‘</b:KeyValuePairOfstringanyType>’ +
‘</b:Parameters>’ +
‘<b:RequestId i:nil=”true”/>’ +
‘<b:RequestName>RetrieveMultiple</b:RequestName>’ +
‘</request>’ +
‘</Execute>’;

request += ‘</s:Body></s:Envelope>’;

return this._ExecuteRequest(request,”Fetch”, this._FetchCallback, fCallback);
}

FetchUtil.prototype._FetchCallback = function(xmlhttp,callback)
{
///<summary>(private) Fetch message callback.</summary>
//xmlhttp must be completed
if (xmlhttp.readyState != XMLHTTPREADY)
{
return;
}

//check for server errors
if (this._HandleErrors(xmlhttp))
{
return;
}

var xmlReturn = xmlhttp.responseXML.xml;
xmlReturn = xmlReturn.replace(/</g, ‘&lt;’);
xmlReturn = xmlReturn.replace(/>/g, ‘&gt;’);

results = xmlReturn;

//return entity id if sync, or call user callback func if async
if (callback != null)
{
callback(results);
}
else
{
return results;
}
}

fetchExample.htm

<html>
<head>
<title>Fetch 2011 JavaScript Example</title>
<link rel=”stylesheet” type=”text/css” href=”/_common/styles/theme.css.aspx” />
<link rel=”stylesheet” type=”text/css” href=”/_common/styles/global.css.aspx” />
<link rel=”stylesheet” type=”text/css” href=”/_common/styles/fonts.css.aspx” />
<link rel=”stylesheet” type=”text/css” href=”/_common/styles/Dialogs.css.aspx” />

<script src=”ClientGlobalContext.js.aspx”></script>
<script src=”{pub}_FetchUtil.js”></script>

<script type=”text/javascript”>

var _oService;
var _sOrgName = “”;
var _sServerUrl = “https://{org}.api.crm.dynamics.com”;
function executeFetchCommand()
{

var sFetch = document.getElementById(‘txtFetch’).value;
_oService = new FetchUtil(_sOrgName, _sServerUrl);
var oEntity = _oService.Fetch(sFetch, myCallBack);
}

function myCallBack(results){
var sOut = “”;
sOut += “<b>XML Response</b><br />”;
sOut += results;
document.getElementById(‘dvData’).innerHTML = sOut;
}

</script>

</head>
<body>
<table style=”width: 100%; height: 100%;” cellspacing=”0″ cellpadding=”0″ border=”0″>
<tr>
<td id=”tdDialogHeader”>
<div id=”divTitle”>Executing Fetch from JavaScript in CRM 2011</div>
<div id=”divInstructions”>Enter some FetchXML to be executed against your CRM environment</div>
</td>
</tr>
<tr>
<td style=”height: 100%;”>
<div style=”padding: 14px;”>
<label>Fetch Command</label><br />
<textarea id=”txtFetch” rows=”8″ width=”100%” cols=”100″ ></textarea><br />
<input type=”Submit” value=”Fetch” onClick=”javascript:executeFetchCommand();” style=”width:100px;height:24px;”>
<div id=’dvData’ style=”width: 100%; height: 100%;”></div>
</div>
</td>
</tr>
<tr>
<td id=”tdDialogFooter”>
<table cellspacing=”0″ cellpadding=”0″>
<tr>
<td width=”100%”></td>
<td>&nbsp;<button onclick=”window.close();”>Done</button></td>
</tr>
</table>
</td>
</tr>
</table>

</body>
</html>

Now that we have our JavaScript utility and our Fetch example, we need to do a few things.

  1. We need a solution to put these two files into (feel free to add it to an existing solution or create one named whatever you like).
  2. Let’s now import the FetchUtil.js file and publish. Take note of the Publisher Prefix (the letters in front of the name).
  3. We now need to modify the fetchExample.htm file to point to this FetchUtil.js by replacing the {pub} with your Publisher Prefix (ex. “new_FetchUtil.js”).
  4. We also need to modify the _sServerUrl and change the {org} to the appropriate organization abbreviation. If you are using the onsite, you will need to change the entire URL to match accordingly.
  5. Finally, let’s upload the fetchExample.htm file and publish.

This should now bring you to a super fancy screen to paste some Fetch into.

image

Well there you have it. The next step is to parse the XML with jQuery, RegEx, traversing the nodes, or however you prefer. Hope you enjoy!

CRM 2011 – Creating Custom Reports with Bids

I have been dipping my toe into the world of custom reports and trying out the new Bids extension which allows you to create reports using FetchXML.

This is a great because you can do an advanced find in CRM 2011 and then download the fetchXML from this and use is as your datasets

For a great run through in creating your first Custom Report you should use this step by step walkthrough

http://blogs.msdn.com/b/crm/archive/2010/10/26/how-to-creating-custom-report-with-microsoft-dynamics-crm-2011-bids-fetch-extension.aspx

I also found this article which is very good

Getting start with Fetch xml reports in CRM 2011

I will admit to having difficulties in setting up the Datasource, I didn’t understand what I was meant to put in and if you are just trying to do it without the walk through above it’s baffling because it doesn’t really prompt you or indicate what you should put in

  1. Select Microsoft Dynamics CRM Fetch for type and fill in the connection string in the following format: ServerURL;OrganizationName;HomeRealmURL
    1. only ServerURL is required, this would be the url to your CRM server like:https://someserver.crm.dynamics.com
    1. The optional OrganizationName should also be included if you have multiple CRM environments, it will use the first organization otherwise – you will find this value within the Developer Resources area in the CRM client located within Settings > Customizations (refer to the red mark).
    2. The HomeRealmURL, also optional, it will be a registry value from HKLM\SOFTWARE\Microsoft\MSCRMBidsExtensions\HomeRealmUrl
I don’t know why you would need to put in the HomeRealmUrl?
This is what I had to put in
the first part is my CRM url and the second part is the organisation Name.  you can find this in the developer resources which are held in
SETTINGS/Customizations/
and then under Organisation Unique Name
This all seemed like a lot of effort, why could we just paste in the server url with the organisation in it.
Using FetchXML involves a lot of downloading and opening files and then pasting the contents into reports.  It’s a pity that CRM doesn’t offer you the option to save the fetchXml to the clipboard so you could then just paste it into report designer.
I still haven’t got the custom reports working, it’s giving me some kind of permissions error perhaps.  My first experience with the Bids reporting is it’s a good work around from Microsoft and I will give them some credit for that but it does feel like a workaround.  Things are not quite as smooth as they should, the process of doing an advanced find, then downloading it, opening it, changing the alias information and then copying it into SSRS.
Making a report in SSRS is slow process and I think this is an area Microsoft need to spend some time improving.