Last week I wrote a post called "Introducing the Diesel Xrm Service Wrapper," in which I presented a generic WCF wrapper for the Dynamics CRM Organization Service. Almost immediately I had several ideas about updates I wanted to make, so I've decided to host the project on GitHub. The project GitHub page is here, and you can find the code repository here.

Here's a current list of enhancements I plan to add over the coming months:

2. Storing query FetchXml and related configuration data as custom entities in Dynamics CRM
3. Role-based authorization for individual queries
4. Varying impersonation settings per individual query
5. Updates
6. REST/JSON interfaces

If you'd like to contribute or have any other interesting ideas, please drop me a line via my contact page.

In this post, I will present a generic Windows Communication Foundation (WCF) wrapper for the Dynamics CRM Organization Service that lets you turn any FetchXML query into a web service interface without needing to write any code at all. I call my solution the Diesel Xrm Service Wrapper after Diesel, my Great Dane. Just like the CRM Organization Service, he's big and sometimes aggravating, so it's a perfect name!

Usually I like to go into a lot of detail in my blog posts, but frankly WCF gives me such a headache that today I'm just going to explain how the things work at a high level and offer the solution for download without much commentary.

An administrator stores predefined FetchXML queries on the server where the service is hosted. The service's consumers all access a single endpoint, and a "query" parameter is used to load and execute the correct FetchXML query. The consumers authenticate using Dynamics CRM credentials, and a service account executes the queries while impersonating the calling user so that native Dynamics CRM security functionality authorizes all data requests.

Initial Configuration

First, download the solution here: DieselXrmSvcWrapper.zip (Updated Aug. 5, 2013 - This project is now hosted on GitHub. The original solution file download link in this post has been left unchanged, however.)

To configure the solution, you store the URL for your Dynamics CRM server and credentials for a user account to be used as a service account in the appSettings section of the web.config file. The service account should have fairly broad read permissions, including "Act On Behalf Of Another User." You can also modify the WCF configuration if you want to use different bindings or security modes. In the solution download, I'm using a basicHttpBinding with a "UserName" message clientCredentialType (that's WCF-speak for HTTP basic authentication over SSL).

Query Definition

After you've finished the configuration, you can now create the FetchXML queries that will be used for the actual web service interfaces. The queries are stored in individual XML files in a specific directory on the web server, and when a web service consumer calls the service, the "query" parameter maps queries to requests.

In addition to predefined FetchXML, queries can contain paramter substitution placeholders in the format of {$PARAMETER_NAME} that will be replaced with values supplied by the web service consumer. Here's an example query that is stored in a file called contacts.xml:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">  
 <entity name="contact">  
 <attribute name="fullname" />  
 <attribute name="contactid" />  
 <attribute name="ownerid" />  
 <attribute name="address1_stateorprovince" />  
 <attribute name="gendercode" />  
 <attribute name="createdon" />  
 <order attribute="fullname" descending="false" />  
 <filter type="and">  
 <condition attribute="lastname" operator="like" value="{$lastnameletter}%" />  
 </filter>  
 </entity>  
</fetch> 

Sample SOAP Messages

1
Here's what the corresponding SOAP request method for the query above looks like:

<soapenv:Envelope   
 xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"   
 xmlns:tem="http://tempuri.org/"   
 xmlns:dies="http://schemas.datacontract.org/2004/07/DieselXrmSvcWrapper"  
 xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
 <soapenv:Header/>  
 <soapenv:Body>  
 <tem:Retrieve>  
 <tem:query>contacts</tem:query>  
 <tem:inputParameters>  
 <!--Zero or more repetitions:-->  
 <dies:ParameterItem>  
 <dies:Name>lastnameletter</dies:Name>  
 <dies:Value i:type="b:string" xmlns:b="http://www.w3.org/2001/XMLSchema">C</dies:Value>  
 </dies:ParameterItem>  
 </tem:inputParameters>  
 </tem:Retrieve>  
 </soapenv:Body>  
</soapenv:Envelope>  

The number of ParameterItems in the request should match the number of substitution placeholders in the query. If there are no placeholders, then you should omit the InputParameters section completely.

The SOAP response for the request above is attached here: soap-response.txt.

Wrapping Up

Now that you understand how the solution behaves and how to configure it, here's an overview of the solution components:

1. FetchXML queries - As mentioned above, each query is stored in a separate XML file in a single directory. In my solution, the directory is called "RetrieveQueries," but you can change it in the web.config file.
2. CrmUsernamePasswordValidator.cs - This custom username/password validator lets WCF validate the Dynamics CRM user credentials via TransportWithMessageCredential security. This also stores the users' CRM roles in an HttpContext.User object, so you could add some sort of role-based authorization to your service if you want.
3. SoapSvc.svc - This actually does the request/response processing.

Once you have everything set up, you'll want to try it out, and I suggest using SoapUI. To get up and running with SoapUI, you basically just point it at your service's auto-generated WSDL, and SoapUI will figure out most everything it needs to work. If you use my solution without modifying the WCF configuration, you will need to change the WSS-Password Type setting for your request to "PasswordText," and you'll have to supply the username and password either in the request properties menu or on the Authorization tab. I've circled these fields in the screenshot below.

In the future, I plan to update the solution to handle create/update/delete requests, and I may look at setting up a REST interface, too. If you have any thoughts or feedback, please share in the comments.

Happy web service wrapping!

Although Dynamics CRM offers several different ways to report on data stored in the system, there is no out-of-the-box mechanism for reporting on how data changes over time. That is to say, while you can easily report on how many active accounts are owned by a particular sales rep today, you can't report on how the number of active account compares to last week, last month or even just yesterday. Salesforce.com supports this type of historical summary reporting with a feature called "analytic snapshots," but Dynamics CRM has typically required the use of an external data warehouse that is populated with summary data on a periodic basis. In this post, I will show how to set up a basic data snapshot framework inside Dynamics CRM that behaves like the analytic snapshot functionality of Salesforce.

Please note that this is not intended to replace a true enterprise data warehouse (EDW), but rather my solution is designed to complement existing CRM functionality by offering basic trend and summary data that can be used in reports and dashboard components. Using a separate data warehouse gives report writers a lot of power and flexibility, and I highly recommend using one to maximize your analytical capabilities.

Analytic Snapshots Explained

According to the Salesforce help documentation:

An analytic snapshot lets you report on historical data. Authorized users can save tabular or summary report results to fields on a custom object, then map those fields to corresponding fields on a target object. They can then schedule when to run the report to load the custom object's fields with the report's data. Analytic snapshots enable you to work with report data similarly to how you work with other records in Salesforce.

Translated to Dynamics CRM-speak, basically the an administrator creates a new custom entity to hold the summary data, maps the fields from an existing report to the custom object and then schedules the snapshot to run daily, weekly or monthly. Once the summary data is captured in the custom object records, the data is available for reporting.

My solution achieves the same thing using a FetchXML query, a custom snapshot target entity, a custom snapshot definition entity, a workflow and a custom workflow activity. The custom snapshot definition entity, workflow and custom workflow activity constitute the snapshot processing framework, and then you will need to write a FetchXML query, create a snapshot definition record and define a custom target entity for each snapshot you want to set up. Let's take a closer look at how these pieces fit together.

Getting Started

You can download my solution from the MSDN code gallery here: Taking Scheduled Data Snapshots in Dynamics CRM. Make sure to register the custom workflow assembly using the CRM SDK plug-in registration tool, and then import and publish the "DataSnapshots" CRM solution. The solution also contains a custom snapshot definition and target entity for this example.

If you would prefer to put together your own solution, skip ahead to the Snapshot Definition section and set up your own custom snapshot definition entity, workflow and custom workflow activity as explained. Once you're done, come back here to read about how to create the query and the target entity.

FetchXML Query

Because Dynamics CRM doesn't think of reports the same way as Salesforce, I decided to use a FetchXML query as the data source for my snapshot solution. This query will be stored on the snapshot definition record, but you need to prepare it before you define your target entity so you can make sure to create all the right fields to store your snapshot data.

Here is a basic query that aggregates contact count and sum of annual income by owner and state:

<fetch mapping="logical" aggregate="true" distinct="false">  
 <entity name="contact">  
 <attribute name="fullname" alias="fullname_count" aggregate="count"></attribute>  
 <attribute name="annualincome" alias="annualincome_sum" aggregate="sum"></attribute>  
 <attribute name="address1_stateorprovince" groupby="true" alias="state_province"></attribute>  
 <attribute name="ownerid" groupby="true" alias="owner"></attribute>  
 </entity>  
</fetch>  

Custom Snapshot Target Entity

After you figure out the data you want to capture, you have to create a custom entity to store it. For the query I showed above, I have created a custom entity called "Snapshot - Contacts by State" (la_snapshotcontactsbystate). You can name your entities anything you want, but I suggest some sort of common prefix to keep them organized. I have also chosen organization ownership, but user/team ownership shouldn't cause any problems.

On this entity, I have created custom fields as shown in the following screenshot:

Obviously your custom fields will vary depending on the data your query returns. One critically important thing to remember is that you must use the exact same data types for your custom fields as are returned by the query. My custom workflow activity code is relatively unforgiving, so even something like trying to store an integer value in a decimal field, which would not have resulted in data loss, caused errors in my testing.

Snapshot Definition

Once you have your target entity defined, you are now ready to create a snapshot definition record to store the query, field mappings and frequency/scheduling details. This shows a snapshot definition that corresponds to the query and target that I explained above.

1. The target entity name is the system name for the target entity, in this case la_snapshotcontactsbystate.
2. The next run date field is when the snapshot will execute. The workflow that executes the snapshot runs according to a timeout whenever this value changes, and it sets the next run date in the future according to the value of the frequency field (add one day/seven days/one month) at the end of the process, thus triggering the next run. If want to enable different frequency choices, you can update the picklist options and the next run date update logic in the workflow.
3. The query field is your FetchXML query.
4. The field mappings are in the format of source>target, each on a separate line. You can use an alternate separator character than ">" if you update the custom workflow activity code and redeploy the assembly.

Custom Workflow Activity

The custom workflow activity behaves a lot like the custom workflow activity I created for my Scheduling recurring Dynamics CRM workflows with FetchXML post. It takes a FetchXML query as an input parameter, executes the query and then loops through the results to do some additional work. For the data snapshots, we also need to know the name of the target entity and the field mappings. Here are the relevant input parameters:

[Input("FetchXML query")]  
public InArgument FetchXMLQuery { get; set; }  
  
[Input("Snapshot mappings")]  
public InArgument SnapshotMappings { get; set; }  
  
[Input("Target entity")]  
public InArgument TargetEntity { get; set; }  

In the interest of saving space, I'm not posting the entire Execute method, but here is the section that does the query/loop/create bits:

try  
{  
 //get the source->target field mappings from the input parameters  
 string mappingsText = SnapshotMappings.Get(executionContext);  
  
 //split up the lines into an array  
 string[] mappingLines = mappingsText.Split(Environment.NewLine.ToCharArray());  
  
 //create a dictionary object to hold the mappings  
 Dictionary<string, string> mappingDictionary = new Dictionary<string,string>();  
  
 //for each line in the array, split it on the ">" character and store the source->target as a key-value pair  
 foreach(string s in mappingLines)  
 {  
 try   
 {  
 string targetfield = s.Split(">".ToCharArray())[1];  
 string sourcefield = s.Split(">".ToCharArray())[0];  
 tracingService.Trace("line: " + s + " source: " + sourcefield + " target: " + targetfield);  
 mappingDictionary.Add(sourcefield, targetfield);  
 }  
 catch(Exception ex)  
 {  
 throw new InvalidPluginExecutionException("Error parsing source->target mappings.");  
 }  
 }  
  
 //get the target entity name from the input parameters  
 string targetEntityType = TargetEntity.Get(executionContext);  
  
 //execute the fetchxml query and loop through the results  
 EntityCollection recordsToProcess = service.RetrieveMultiple(new FetchExpression(FetchXMLQuery.Get(executionContext)));  
 recordsToProcess.Entities.ToList().ForEach(a =>  
 {  
 tracingService.Trace("instantiating new entity of type: " + targetEntityType);  
 Entity targetRecord = new Entity(targetEntityType);  
   
 //loop through the mapping key-value pairs  
 foreach (string key in mappingDictionary.Keys)  
 {  
 //check to see if attribute exists in this particular result  
 //fetchxml results don't return fields with null values  
 if (a.Contains(key))  
 {  
 //if the result field is an aliasedvalue, we need to handle it differently than non-aliasedvalue types  
 if (a[key].GetType().FullName.ToUpperInvariant().EndsWith("ALIASEDVALUE"))  
 {  
 //cast it to aliasedvalue  
 var resultfield = (AliasedValue)a[key];  
  
 //set the target field to the casted resultfield value  
 targetRecord[mappingDictionary[key]] = resultfield.Value;  
  
 //trace just in case  
 tracingService.Trace("new source column [" + key + "]: destination [" + mappingDictionary[key] + "]: " + (resultfield.Value).ToString());  
 }  
 else  
 {  
 //set the target field to the resultfield  
 //target field needs to be of EXACT same type as result field  
 //for example, this code doesn't work if you try to store an integer in a decimal field  
 targetRecord[mappingDictionary[key]] = a[key];  
  
 //trace just in case  
 tracingService.Trace("new source column [" + key + "]: destination [" + mappingDictionary[key] + "]: " + (a[key]).ToString());  
 }  
 }  
 }  
 try  
 {  
 //create the record  
 service.Create(targetRecord);  
 }  
 catch (FaultException e)  
 {  
 //catch orgservice faults  
 tracingService.Trace("Exception: {0}", e.ToString());  
  
 // Handle the exception.  
 throw new InvalidPluginExecutionException("Could not create snapshot record.");  
 }  
 catch (Exception ex)  
 {  
 //catch anything else  
 throw new InvalidPluginExecutionException("Could not create snapshot record.");  
 }  
  
 });  
}

The Snapshotting Workflow

Finally, I created a workflow process called "Take Snapshot." As explained above, it runs whenever the next run date field on a snapshot definition record changes. Here's the business logic it executes at a high level:

1. Timeout until snapshot definition's next run date
2. Execute the Take Snapshot custom workflow activity with input parameters taken from the snapshot definition query, mapping and target entity fields
3. Update the next run date based on the frequency

Snapshot Results

Once you set everything up and the workflow executes, you'll then be able to query snapshot data. Here's a screenshot of an advanced find view with the snapshot from this example.

Happy data snapshotting!

Last month I wrote a post about how to create a web resource dialog "launcher" that you can embed in a CRM form (both classic and updated modes) with JavaScript and an OData query. In today's post, I will show how to do the same thing using a FetchXML query. There are four changes you need to make to the web resource from the previous post.

ClientGlobalContext.js.aspx

First, because we're working with FetchXML, you need to add a reference to ClientGlobalContext.js.aspx JavaScript to your page head.

Add the buildFetchRequest function

You need to add a function to build a full SOAP request from a FetchXML query. This is the same buildFetchRequest function I have used in previous FetchXML-related posts.

function buildFetchRequest(fetch) {  
/// <summary>  
/// builds a properly formatted FetchXML request  
/// based on Paul Way's blog post "Execute Fetch from JavaScript in CRM 2011"  
/// http://blog.customereffective.com/blog/2011/05/execute-fetch-from-javascript-in-crm-2011.html  
/// </summary>  
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(fetch);   
  
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 request;  
}  

Update the getDialogList function

You also need to update the getDialogList function to build and execute a FetchXML query instead of an OData query. Here is the updated function:

//function to retrieve the dialogs  
function getDialogList() {  
var dialogname = dataObj["dialogname"];  
var entitytypename = dataObj["entitytypename"];   
  
//only execute query if the record already exists  
if((recordId != null) && (recordId != '')) {  
  
//no point if entitytypename is provided  
if((entitytypename != null) && (entitytypename != ''))  
{  
//path to CRM root  
var server = window.location.protocol + "//" + window.location.host;  
  
//full path to CRM organization service - you may need to modify this depending on your particular situation  
var path = server + "/XRMServices/2011/Organization.svc/web";  
var fetchQuery = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">';  
fetchQuery += '<entity name="workflow">';  
fetchQuery += '<attribute name="workflowid" />';  
fetchQuery += '<attribute name="name" />';  
fetchQuery += '<attribute name="category" />';  
fetchQuery += '<attribute name="primaryentity" />';  
fetchQuery += '<attribute name="type" />';  
fetchQuery += '<attribute name="description" />';  
fetchQuery += '<order attribute="name" descending="false" />';  
fetchQuery += '<filter type="and">';  
fetchQuery += '<condition attribute="name" operator="like" value="'+dialogname+'%" />';  
fetchQuery += '<condition attribute="type" operator="eq" value="1" />';  
fetchQuery += '<condition attribute="category" operator="eq" value="1" />';  
fetchQuery += '<condition attribute="primaryentityname" operator="eq" value="'+entitytypename+'" />';  
fetchQuery += '</filter>';  
fetchQuery += '</entity>';  
fetchQuery += '</fetch>';  
  
var fetchRequest = buildFetchRequest(fetchQuery);  
  
$.ajax({  
type: "POST",  
dataType: "xml",  
contentType: "text/xml; charset=utf-8",  
processData: false,  
url: path,  
data: fetchRequest,  
//complete: function(xhr, status) {  
// console.log(xhr.responseText);  
//},  
beforeSend: function( xhr ){  
xhr.setRequestHeader(  
"SOAPAction",  
"http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute"  
); //without the SOAPAction header, CRM will return a 500 error  
}  
}).done(function(data) {  
//if successful, pass returned xml to the display function  
generateLauncherMenu(data);  
}).fail(function(jqXHR, textStatus, errorThrown ) {  
//if unsuccessful, generate an error alert message  
alert( "Request failed: " + textStatus + "\n" + errorThrown );  
});  
}  
}  

Update the generateLauncherMenu function

Finally, you need to update the generateLauncherMenu function to accept an XML input parameter and parse/process accordingly. Here is an updated version that, as in my previous example, generates buttons to launch the dialogs:

//callback function to parse the response and build the menu output  
function generateLauncherMenu(xml) {  
$(xml).find("a\\:Entity").each(function() {  
var workflowid, primaryentity, name, paramString;  
//inner loop  
$(this).find("a\\:KeyValuePairOfstringanyType").each(function() {  
var xmlElement = $(this);  
var key = xmlElement.find("b\\:key").text();  
var value = xmlElement.find("b\\:value").text();  
switch(key) {  
case "workflowid":  
workflowid = value;  
break;  
case "primaryentity":  
primaryentity = value;  
break;  
case "name":  
name = value;  
break;  
}  
paramString = "\"" + workflowid + "\",\"" + primaryentity + "\",\"" +recordId + "\"";  
});  
$('#launchDiv').append( "<button onclick='openDialog("+paramString+")'>Launch " + name + "</button>&nbsp;&nbsp;" );  
});  
}

Wrapping up

Once those four changes are made, this web resource will behave exactly like the OData version.

In today's post I will show how to set up a recurring process in Dynamics CRM that executes a FetchXML query to return a set of records and then starts a workflow for each of those records without requiring any external processes or tools. This is a generalized approach to solving a class of problems that includes the following scenarios:

1. The birthday greetings problem: How can you, on a daily basis, send an e-mail to every contact with a birthday = today (where the date value for today is obviously different every day)?
2. The monthly update problem: How can you, on a monthly basis, generate an activity for every account with status reason = X (where it's important that the process only runs on a certain day of the month based on status reason values as of that exact date)?

While you could accomplish both of these using workflows with timeouts, I think that would be a bad approach. In the case of the birthday greetings problem, I don't like to have workflows in a waiting state for an entire year. For the monthly update problem, a timeout-based approach could get extremely complex. Another more general issue with waiting workflows is that business rules can change, so if you have thousands of workflows in a waiting state and the business owner says she wants to update the process to do X instead of Y, it can be a serious pain to cancel the waiting workflows, update the definition and then restart them. (And yes, I speak from personal, painful experience.)

My solution requires three things:

1. A custom workflow activity (StartScheduledWorkflows) that can execute a supplied FetchXML query and initiate the workflow for each retrieved record.
2. A custom entity (Scheduled Process) to hold the FetchXML query and scheduling details.
3. A workflow (Scheduled Workflow Runner) to run the StartScheduledWorkflows activity on a recurring schedule.

How it works

A Scheduled Process record is created, which starts a corresponding Scheduled Workflow Runner workflow in a timeout state. When the next run date == the current time, the Scheduled Workflow Runner workflow initiates the StartScheduledWorkflows activity with the FetchXML query and workflow lookup from the Scheduled Process record. The StartScheduledWorkflows activity advances the next run date of the Scheduled Process record, and then it executes the FetchXML, loops through the results and starts the workflow from the lookup for each record. A newly started Scheduled Workflow Runner workflow then waits for the next run date to start the process again.

Creating the custom workflow activity

The custom workflow activity is easy to implement. As described above, a FetchXML query and a workflow lookup are supplied as input parameters, and then the workflow is started for each record returned by the FetchXML query. Here's the code for my StartScheduledWorkflows class.

public sealed class StartScheduledWorkflows : CodeActivity  
{  
[Input("FetchXML query")]  
public InArgument<String> FetchXMLQuery { get; set; }  
  
[Input("Workflow")]  
[ReferenceTarget("workflow")]  
public InArgument<EntityReference> Workflow { get; set; }  
  
//name of your custom workflow activity for tracing/error logging  
private string _activityName = "RunDailyProcess";  
  
/// <summary>  
/// Executes the workflow activity.  
/// </summary>  
/// <param name="executionContext">The execution context.</param>  
protected override void Execute(CodeActivityContext executionContext)  
{  
// Create the tracing service  
ITracingService tracingService = executionContext.GetExtension<ITracingService>();  
  
if (tracingService == null)  
{  
throw new InvalidPluginExecutionException("Failed to retrieve tracing service.");  
}  
  
tracingService.Trace("Entered " + _activityName + ".Execute(), Activity Instance Id: {0}, Workflow Instance Id: {1}",  
executionContext.ActivityInstanceId,  
executionContext.WorkflowInstanceId);  
  
// Create the context  
IWorkflowContext context = executionContext.GetExtension<IWorkflowContext>();  
  
if (context == null)  
{  
throw new InvalidPluginExecutionException("Failed to retrieve workflow context.");  
}  
  
tracingService.Trace(_activityName + ".Execute(), Correlation Id: {0}, Initiating User: {1}",  
context.CorrelationId,  
context.InitiatingUserId);  
  
IOrganizationServiceFactory serviceFactory = executionContext.GetExtension<IOrganizationServiceFactory>();  
IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);  
  
try  
{  
EntityCollection recordsToProcess = service.RetrieveMultiple(new FetchExpression(FetchXMLQuery.Get(executionContext)));  
recordsToProcess.Entities.ToList().ForEach(a =>  
{  
ExecuteWorkflowRequest request = new ExecuteWorkflowRequest  
{  
EntityId = a.Id,  
WorkflowId = (Workflow.Get(executionContext)).Id  
};  
  
service.Execute(request); //run the workflow  
});  
}  
catch (FaultException<OrganizationServiceFault> e)  
{  
tracingService.Trace("Exception: {0}", e.ToString());  
  
// Handle the exception.  
throw;  
}  
catch (Exception e)  
{  
tracingService.Trace("Exception: {0}", e.ToString());  
throw;  
}  
  
tracingService.Trace("Exiting " + _activityName + ".Execute(), Correlation Id: {0}", context.CorrelationId);  
}  
}

Creating the custom entity

To run the scheduled processes, we will need a custom entity called Scheduled Process to hold at least four pieces of data:

1. The FetchXML query to execute
2. The workflow that will be started for the retrieved records
3. The next run date/time on which the process should be executed
4. How often the job should be run (frequency)

In addition to those, I also have fields to hold the relevant entity name and the last run date. Neither of these are required for my approach to work, but they make managing the processes easier if you have a lot. Here's a screenshot of a Scheduled Process record:

Creating the scheduling workflow

Finally we need a Scheduled Workflow Runner workflow that will manage the execution of each scheduled process record. It should look like the following:

The workflow runs when the next run date value of Scheduled Process changes. Once we reach the next run date for a Scheduled Process, the workflow updates the last run date to the process execution time and the next run date to the process execution based on the Scheduled Process frequency. There are three "loop" conditional branches to handle this, so if the frequency is "hourly," then the next run date is set to the process execution time + one hour. You can modify your frequency values on the the Scheduled Process record, but you'll have to add a separate conditional check in the workflow for each one. Alternatively you could use another custom workflow activity to intelligently handle different frequency values, but I wanted to keep the coding to a minimum for this example.

Because next run date is based on when the Scheduled Workflow Runner actually executes, your next run date times may end up skewing over time. That is, if you start a Scheduled Process running at 6:00 a.m., by the fifth iteration, you might end up with 11:05 a.m. instead of 11:00 a.m. For this reason, I suggest you have a process in place to occasionally check these values and reset them as needed. Potential automated solutions include using another Scheduled Process to fix the time skew, or custom code in a plug-in or custom workflow activity to re-baseline the time. If anyone has any other suggestions, please share them in the comments.

After the updates are made, the workflow executes the StartScheduledWorkflows custom workflow activity to start the workflows for the relevant records, and you're done.

Putting it all together

I've uploaded the custom workflow activity code and an export of my unmanaged CRM solution to the MSDN developer samples code gallery (direct download link here). The solution export includes a built assembly with the custom workflow activity ready for registration in your CRM system.

Happy workflow scheduling!

Last week I wrote a post that showed how to retrive the raw SOAP response from a Dynamics CRM query in C#, but I didn't show how to do anything useful with it. In today's post I will show a practical example of how to execute a FetchXML request against a Dynamics CRM instance, capture the raw SOAP response and transform it with XSLT for display in an ASPX page. For demonstration purposes, I will be using the same query and XSL as I used in my "Displaying FetchXML results with XSLT on the client side in a Dynamics CRM 2011 web resource" post.

In this example I am using a WSDL-based proxy instead of the CRM SDK, but all of the code I am sharing can be easily modified to work with the SDK instead.

My ASPX page has five controls:

1. xmlTextBox textbox to input the FetchXML query
2. xslTextBox textbox to input the XSL for the transformation
3. executeButton button to retrieve and transform the data
4. resultsTextBox textbox to show the SOAP response
5. resultsLiteral literal to show the transformed data

When the executeButton button is clicked, the following steps take place:

1. Set up a WCF client connection to the CRM OrganizationService (with client message inspector as described in my last post)
2. Retrieve the results for the supplied FetchXML query
3. Show the SOAP response
4. Transform the SOAP response using the supplied XSL
5. Show the transformed results

Here's the code:

/// <summary>  
/// executes retrieve and transform functionality  
/// </summary>  
/// <param name="sender"></param>  
/// <param name="e"></param>  
protected void executeButton_Click(object sender, EventArgs e)  
{  
OrganizationServiceClient client = SetupClient();  
string results = RetrieveData(this.xmlTextBox.Text, client);  
  
string xsl = this.xslTextBox.Text;  
this.resultsTextBox.Text = results;  
this.resultsLiteral.Text = DoTransform(results, xsl);  
}  
  
/// <summary>  
/// instantiates an OrganizationServiceClient object for the username/password/organization service specified as constants  
/// </summary>  
/// <returns></returns>  
private OrganizationServiceClient SetupClient()  
{  
ClientCredentials credentials = new ClientCredentials();  
credentials.UserName.UserName = UserName; //UserName is a constant  
credentials.UserName.Password = UserPassword; //UserPassword is a constant  
  
OrganizationServiceClient client = new OrganizationServiceClient("CustomBinding_IOrganizationService",  
new EndpointAddress(OrganizationServiceUrl)); //OrganizationServiceUrl is a constant  
client.ClientCredentials.UserName.UserName = credentials.UserName.UserName;  
client.ClientCredentials.UserName.Password = credentials.UserName.Password;  
return client;  
}  
  
/// <summary>  
/// executes a fetchxml query with a given org service client and returns the SOAP response as a string  
/// </summary>  
/// <param name="fetchXml">query</param>  
/// <param name="client">client</param>  
/// <returns>response</returns>  
string RetrieveData(string fetchXml, OrganizationServiceClient client)  
{  
string response = "";  
  
//set up a client message inspector for the query  
//see http://alexanderdevelopment.net/post/2013/02/21/Accessing-raw-SOAP-requests-responses-from-Dynamics-CRM-web-services-in-C.aspx  
MyInspector inspector = new MyInspector();  
client.Endpoint.Behaviors.Add(inspector);  
  
//build the fetchxml  
FetchExpression fetch = new FetchExpression();  
fetch.Query = fetchXml;  
EntityCollection entities = client.RetrieveMultiple(fetch);  
  
//if there is a captured response  
if (inspector.receivedMessages.Count == 1)  
{  
response = inspector.receivedMessages[0];  
}  
  
//return it  
return response;  
}  
  
/// <summary>  
/// transforms supplied results xml with supplied xsl  
/// </summary>  
/// <param name="results">input xml</param>  
/// <param name="xsl">xsl style sheet</param>  
/// <returns>transformed output</returns>  
string DoTransform(string results, string xsl)  
{  
//create an xmldocument from the input xml  
System.Xml.XmlDocument xmlDoc = new XmlDocument();  
xmlDoc.LoadXml(results);  
  
//create an xmldocument from the input xsl  
System.Xml.XmlDocument xslDoc = new XmlDocument();  
xslDoc.LoadXml(xsl);  
  
//load a compiled xsl transform object from the xsl document  
XslCompiledTransform transform = new XslCompiledTransform();  
transform.Load(xslDoc);  
  
//prepare stringwriter and xmlwriter get string output from transformation  
StringWriter sw = new StringWriter();  
XmlWriter xwriter = XmlWriter.Create(sw);  
  
//execute the transformation  
transform.Transform(xmlDoc, xwriter);  
  
//return the output  
string output = sw.ToString();  
return output;  
}

My .aspx and .aspx.cs files are attached here for reference.

1. DisplayXml.aspx (3.16 kb)
2. DisplayXml.aspx.cs (4.95 kb)

A few weeks back, I wrote a post that showed how to retrieve and display FetchXML using jQuery in a Dynamics CRM web resource. In that example, I used jQuery's each() method to iterate through each result and append them to an HTML element on the page. Using each() is a good approach if you need to actually do something with each row, but if you just want to display data, XSLT is a much easier way to do it. In this post, I will show how to use client-side XSLT to format a FetchXML response for display.

Fetching the data

To display data, obviously we need to retrieve some data first. Using the approach from my earlier post, we will use the following FetchXML query to return a list of contacts:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">  
<entity name="contact">  
<attribute name="fullname" />  
<attribute name="contactid" />  
<attribute name="ownerid" />  
<attribute name="gendercode" />  
<attribute name="address1_stateorprovince" />  
<attribute name="createdon" />  
</entity>  
</fetch>

When I execute this query, I get the output in the attached contacts.xml file: contacts_raw.xml (50.68 kb).

Designing the display format

It we are going to use XSLT to format XML data, we will need an XSL style sheet. A detailed discussion of XSL is beyond the scope of this post, but I will point out a couple of things about my style sheet:

First, I use a template to match the root node, and then I loop through each entity with this for-each element:

<xsl:for-each select="//a:Entities/a:Entity">

The "//a:Entities" tells it to match an a:Entities element with any ancestors so you don't have to match all the way to the top-level s:Envelope ancestor.

Second, FetchXML represents an attribute (or formattedvalue) as an element with one "key" child and one "value" child. The "key" child is the attribute name, and the "value" is, obviously, the value. Thus, from each entity, I select the attribute and formattedvalue data to display using elements like this:

<xsl:value-of select="a:Attributes/a:KeyValuePairOfstringanyType[b:key='fullname']/b:value" />

What that means is "select the 'b:value' element that has an 'a:KeyValuePairOfstringanyType' that has a 'b:key' element equal to 'fullname.'" Alternatively, you can think abou it as "select the 'b:value' element that has a sibling 'b:key' element equal to 'fullname.'"

Here is the complete XSL style sheet: contacts.xsl (1.35 kb).

Once you have your XSL style sheet ready, upload it to your CRM instance as a web resource of type "Style Sheet (XSL)." Make sure you remember the relative path of the new resource. In my case it is "new_contacts.xsl."

Applying the transformation

Now that we have some FetchXML results and an XSL style sheet, all that's left to do is to transform the returned FetchXML and display it on the page. To do that, I have made a few modifications to my earlier FetchXML retrieval logic. Because jQuery retrieves things by default asynchronously, I start with retrieving the XSL document, then I use a callback function to retrieve the FetchXML results and finally I use a callback function to do the transformation. In the interest of not cluttering my code with global variables, I pass everything I need through the chain of callbacks. For example, my XSL retrieval method takes the FetchXML request as a parameter just so it can pass it to its callback. Then that callback takes the XSL document so that its callback has the XSL.

The code to do the apply and display the transformation is below:

function processData(xml, xsl) {  
/// <summary>  
/// transforms xml with a supplied xsl style sheet and appends the output to a div on the page  
/// </summary>  
  
//instantiate a variable to hold the transformed xml  
var resultDocument = "";  
resultDocument = TransformToHtmlText(xml, xsl);  
alert("Output started");  
$("#outputdiv").append(resultDocument);  
  
alert("Output complete");  
}  
  
function TransformToHtmlText(xmlDoc, xsltDoc) {  
/// <summary>  
/// Transforms a XML document to a HTML string by using a XSLT document  
/// 1. Use type XSLTProcessor, if browser (FF, Safari, Chrome etc) supports it  
/// 2. Use function [transformNode] on the XmlDocument, if browser (IE6, IE7, IE8) supports it  
/// 3. Use function transform on the XsltProcessor used for IE9 (which doesn't support [transformNode] any more)   
/// 4. Throws an error, when both types are not supported  
/// taken from "How to fix: DOMParser.TransformNode not supported in IE9"  
/// http://www.roelvanlisdonk.nl/?p=2113  
/// </summary>  
// 1.  
if (typeof (XSLTProcessor) != "undefined") {  
var xsltProcessor = new XSLTProcessor();  
xsltProcessor.importStylesheet(xsltDoc);  
var xmlFragment = xsltProcessor.transformToFragment(xmlDoc, document);  
return xmlFragment;  
}  
// 2.  
if (typeof (xmlDoc.transformNode) != "undefined") {  
return xmlDoc.transformNode(xsltDoc);  
}  
else {  
  
try {  
// 3  
if (window.ActiveXObject) {  
var xslt = new ActiveXObject("Msxml2.XSLTemplate");  
var xslDoc = new ActiveXObject("Msxml2.FreeThreadedDOMDocument");  
xslDoc.loadXML(xsltDoc.xml);  
xslt.style sheet = xslDoc;  
var xslProc = xslt.createProcessor();  
xslProc.input = xmlDoc;  
xslProc.transform();  
  
return xslProc.output;  
}  
}  
catch (e) {  
// 4  
alert("The type [XSLTProcessor] and the function [XmlDocument.transformNode] are not supported by this browser, can't transform XML document to HTML string!");  
return null;  
}  
  
}  
}

Two points to note:

1. The processData method doesn't apply the transformation itself. It passes the XML and XSL to another method called TransformToHtmlText.
2. The TransformToHtmlText method allows us to easily handle differences in the way that different browsers (or even different versions of the same browser) handle XML. For more information take a look at http://www.roelvanlisdonk.nl/?p=2113 and http://stackoverflow.com/questions/12149410/object-doesnt-support-property-or-method-transformnode-in-internet-explorer-1.

Trying it out

Here is a complete web page with the code for you to download and try out in your own CRM instance: fetchxml_xslt.htm (7.02 kb). Just upload it as you would any other web resource, publish and open. At that point you will be presented with a screen that looks like this:

Either leave the pre-populated FetchXML in the textarea or supply your own. Once you click the "fetch and process" button, you will start to see some output and a couple of status alert windows before the output finally displays. The end result should look something like this:

In part I of this series, I showed how to query Microsoft Dynamics CRM for aggregate data using FetchXML and then pass the results to Flot to generate a line chart. In this second part, I will expand on that to show how to query for and chart multi-series data. For my example today, I will be creating a chart that shows the number of contacts created by date and state (address, not statecode). In my CRM instance, I have created a set of contacts using characters from Cohen brothers movies. (Please, hold your "Big Lebowski" quotes.)

Changes to the previous approach

There are three main things we have to do differently here than before:

1. Update the FetchXML query to include an additional group-by field
2. Update the results parser to handle the additional group-by field
3. Send Flot an array of data series instead of just one

In addition to that, I have improved the original code in two more ways:

1. Fill in missing date values with 0 so the shape of the line better reflects reality
2. Shade weekend days in gray on the chart grid to help break up the weeks and highlight areas in which we might logically expect no activity to occur

Updated FetchXML

Changing the FetchXML query is easy. We just have to add the address1_stateorprovince field:

<fetch distinct='false' mapping='logical' aggregate='true'>  
<entity name='contact'>  
<attribute name='fullname' alias='plotValue' aggregate='count' />  
<attribute name='address1_stateorprovince' groupby='true' alias='seriesLabel' />  
<attribute name='createdon' groupby='true' dategrouping='day' alias='day' />  
<attribute name='createdon' groupby='true' dategrouping='month' alias='month' />  
<attribute name='createdon' groupby='true' dategrouping='year' alias='year' />  
</entity>  
</fetch>

The field we will use to create the data series is aliased as "seriesLabel" so we can have as generic a results parsing method as possible.

Updated results parser

The manner in which I wrote the getKeyValPairs function in part I won't return the seriesLabel field as part of the return object because only the numeric values come back in the a:FormattedValues element. The seriesLabel field is available via the a:Attributes element, so this required a minor change to my XML find logic. If you have downloaded my previous charting sample, you should be able to update the getKeyValPairs function in it with the newer version here without any issues.

function getKeyValPairs(entity, valuefield) {  
/// <summary>  
/// extracts an aggregate value and grouping components from a FetchXML response entity element and returns them in an array  
/// </summary>  
var outputArray = new Array();  
  
//first, get the aggregate value using its alias ("plotValue" in the original example)  
var pointValue = parseFloat(entity.find("a\\:KeyValuePairOfstringstring").filter(function() {  
return $(this).find('b\\:key').text() == valuefield;  
}).find("b\\:value").text().replace(/,/g,''));  
var groupingArray = [];  
  
//second, get the values for the group-by fields  
//unlike previous example, this uses the a:KeyValuePairOfstringanyType element   
//so we go down an extra "value" level when populating the value variable  
entity.find("a\\:KeyValuePairOfstringanyType").each(function() {  
return $(this).find('b\\:key').text() != valuefield;  
}).each(function() {  
var xmlElement = $(this);  
var key = xmlElement.find("b\\:key").text();  
var value = xmlElement.find("b\\:value").find("a\\:Value").text();  
var groupingText = '"' + key + '":"' + value + '"';  
groupingArray.push(groupingText);  
});  
var stringToEval = groupingArray.join();  
var dateFields = eval("({"+stringToEval+"})");  
outputArray.push(pointValue);  
outputArray.push(dateFields);  
  
return outputArray;  
}

Sending Flot the data

Flot expects data to be passed as an array of objects like this:

{   
label: "Series Label",   
data: [[x1,y1],[x2,y2]...   
}

In our example, we would have a separate object for each state's data. To build the array of data objects, we do the following:

1. Get a list of unique series labels (states)
2. Create a holding array for each series' data points
3. Assign each data point to its correct series in the holding array
4. Build the data array Flot expects from the holding array

As part of building the data array for Flot, we also do some date manipulation to fill in missing dates with 0 values.

Here are the functions that do all of this:

function processData(xml) {  
/// <summary>  
/// charts data returned in a FetchXML response using Flot  
/// </summary>  
  
alert("Charting started");  
  
//array to hold series labels  
var seriesList = [];  
  
//array to hold the series data before we get it ready for flot  
var pointSeries = [];  
  
//array to hold data to pass to flot  
var dataSeries = [];  
  
//array to hold all each unique date in our data set   
//this is so we can show dates with no data between the start and finish dates as 0  
var dateArray = [];  
  
//loop through xml and extract data from each "entity" element  
$("#chartdiv").append("Starting XML parsing . . . <br />");  
$(xml).find("a\\:Entity").each(function() {  
//pass the element to a function that extracts the aggregate value and the "group by" values  
//"plotValue" is the FetchXML alias for the data we want to display  
var pointData = getKeyValPairs($(this),"plotValue");   
  
//for each unique series label value  
if(seriesList.indexOf(pointData[1].seriesLabel) == -1) {  
//add label to array of labels  
seriesList.push(pointData[1].seriesLabel);  
  
//instantiate a new array to hold data for this series  
pointSeries.push(new Array());  
}  
  
//Flot requires datetimes to be passed as timestamp values  
var pointDate = new Date(pointData[1].year, pointData[1].month-1, pointData[1].day);  
var timestamp = pointDate.getTime();  
  
//for each unique date value  
if(dateArray.indexOf(timestamp) == -1) {  
//add date timestamp to array of dates  
dateArray.push(timestamp);  
}  
  
//get the index of the series to which this particular value corresponds  
var seriesNumber = seriesList.indexOf(pointData[1].seriesLabel);  
  
//add this particular data value to the correct array in the point series array  
pointSeries[seriesNumber].push([timestamp, pointData[0]]);  
  
});  
  
//sort the unique dates so we can find the min and max dates later  
dateArray.sort(function(a,b){return a-b;});  
  
//we have a function that fills in missing dates with 0 values  
//so to make sure all series have the same set of data, we will add a dummy date to both ends of the range with 0 values  
//after we have "filled out" any missing values in the series, we will remove the dummy dates  
//get milliseconds per day so we can just add/subtract the value of the "day" variable later  
var minute = 1000 * 60;  
var hour = minute * 60;  
var day = hour * 24;  
  
//for each series  
for(var i=0; i<seriesList.length; i++) {  
//the label we pass to Flot is the value from the series label array  
var seriesLabel = seriesList[i];  
  
//get a time series data variable from the correct array in the pointseries aray  
var tsData = pointSeries[i];  
  
//add a day with a 0 value to the beginning of the series  
tsData.push([dateArray[0]-day,0]);  
  
//add a day with a 0 value to the end of the series  
tsData.push([dateArray[dateArray.length-1]+day,0]);  
  
//sort the data points by date  
tsData.sort(function(a,b){return a[0]-b[0];});  
  
//fill in missing dates with 0-value points  
tsData = newDataArray(tsData);  
  
//remove the first "dummy" date  
tsData.shift();  
  
//remove the last "dummy" date  
tsData.pop();  
  
//create a new object to represent this series  
var seriesObj = new Object();  
  
//set a label property  
seriesObj.label = seriesLabel;  
  
//set a data property  
seriesObj.data = tsData;  
  
//add this object to the dataseries array that will be passed to Flot  
dataSeries.push(seriesObj);  
}  
  
//plot the data with some display options  
//see examples and API documentation at http://www.flotcharts.org/  
$.plot($("#chartdiv"),   
dataSeries, {   
series: {  
lines: { show: true },  
points: { show: true }  
},   
grid: {   
hoverable: true ,  
markings: weekendAreas //calls the weekendAreas function to shade weekend days in gray  
},  
xaxis: {   
tickLength: 5,  
mode: "time",  
timeformat: "%m/%d/%y"  
},  
yaxis: {  
tickDecimals: 0,  
min: 0  
}  
}  
);  
  
alert("Charting complete");  
}  
  
//this function is used to fill in the missing date values with 0 values  
//taken from http://stackoverflow.com/questions/14522667/how-to-configure-flot-to-draw-missing-time-series-on-y-axis-at-point-zero  
function newDataArray(data) {  
var startDay = data[0][0],  
newData = [data[0]];  
  
for (i = 1; i < data.length; i++) {  
var diff = dateDiff(data[i - 1][0], data[i][0]);  
var startDate = new Date(data[i - 1][0]);  
if (diff > 1) {  
for (j = 0; j < diff - 1; j++) {  
var fillDate = new Date(startDate).setDate(startDate.getDate() + (j + 1));  
newData.push([fillDate, 0]);  
}  
}  
newData.push(data[i]);  
}  
return newData;  
}  
  
  
/* helper function to find date differences*/  
function dateDiff(d1, d2) {  
return Math.floor((d2 - d1) / (1000 * 60 * 60 * 24));  
}

Format for the weekends

The final difference from part I is calling a custom function to format the weekends on the chart. This is done in this part of the processData function above:

grid: {   
hoverable: true ,  
markings: weekendAreas //calls the weekendAreas function to shade weekend days in gray  
},

The weekendAreas function:

//helper for returning the weekends in a period  
//taken from http://www.flotcharts.org/flot/examples/visitors/index.html  
function weekendAreas(axes) {  
var markings = [];  
var d = new Date(axes.xaxis.min);  
// go to the first Saturday  
d.setUTCDate(d.getUTCDate() - ((d.getUTCDay() + 1) % 7))  
d.setUTCSeconds(0);  
d.setUTCMinutes(0);  
d.setUTCHours(0);  
var i = d.getTime();  
do {  
// when we don't set yaxis, the rectangle automatically  
// extends to infinity upwards and downwards  
markings.push({ xaxis: { from: i, to: i + 2 * 24 * 60 * 60 * 1000 } });  
i += 7 * 24 * 60 * 60 * 1000;  
} while (i < axes.xaxis.max);  
  
return markings;  
}

Trying it out

Here is a complete web page with the code for you to download and try out in your own CRM instance chart_example2.htm (12.34 kb). Just upload it as you would any other web resource, publish and open.

When I run it in my CRM instance, this chart is generated.

As you can see, the chart gets a little busy, and it's hard to differentiate between the different series where they overlap, but there's a lot more you can do with Flot formatting options to make it look better. Also, I haven't covered everything Flot can do, so I encourage you to look at the Flot examples page to see some other possibilities like bar charts, stacked charts, panning/zooming, etc.

Earlier this week I posted an entry about using FetchXML and JQuery in a Dynamics CRM 2011 web resource. The reason I first started looking at those two together was that I wanted to see if I could generate better looking line charts than are available out of the box (spoiler alert: I did). In this post I will show how to combine FetchXML and JQuery with the Flot plotting library to produce line charts from CRM data aggregations.

In my career, one of the data visualizations I have encountered the most is to plot some value over unit time. For example, we can try to measure an agent's productivity by counting the number of phone calls per day. Because of the way Dynamics CRM stores data, if we restrict our queries to its database instead of a data warehouse, generally we can report on actions taken per unit time, but we can't report on how things change over time. We can say Agent X created 100 phone call records last week, but we can't tell how many overdue phone call records Agent X had in the queue yesterday. In my example here, I will show how to plot number of contacts created per day.

Top of the page stuff

Charting with Flot requires jQuery, Flot (obviously) and Excanvas in Internet Explorer. Here is how I pull those libraries in from CDNs, but if your CRM instance is accessed over a LAN, you should host them locally.

<!--[if lte IE 8]><script language="javascript" type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/flot/0.7/excanvas.min.js"></script><![endif]-->  
<script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.9.0.min.js"></script>  
<script src="https://cdnjs.cloudflare.com/ajax/libs/flot/0.7/jquery.flot.min.js"></script>

Also, Flot doesn't work so well in Internet Explorer quirks mode, so make sure you set the page to display in standards mode by declaring an HTML doctype like so in the first line:

Writing the query

As I explained previously, if you want to query aggregate data from inside JavaScript, you have to use FetchXML. While researching this topic, the examples I found weren't necessarily the clearest, but MSDN has a good overview here.

Once you get the hang of the FetchXML syntax, if you can write your query in SQL group-by syntax, it shouldn't be too difficult to translate it to FetchXML. Here is the query we will use to count contacts by their createdon date:

<fetch distinct='false' mapping='logical' aggregate='true'>  
<entity name='contact'>  
<attribute name='fullname' alias='plotValue' aggregate='count' />  
<attribute name='createdon' groupby='true' dategrouping='day' alias='day' />  
<attribute name='createdon' groupby='true' dategrouping='month' alias='month' />  
<attribute name='createdon' groupby='true' dategrouping='year' alias='year' />  
</entity>  
</fetch>

We have a single data field aliased as plotValue that is equivalent to SQL's count(fullname) as plotValue, and then there are three groupby fields that are based on the createdon field. They have each been given an alias that corresponds to the specific datepart.

Executing the query

To execute the FetchXML and retrieve the results, we will use the same basic technique I showed in my previous post.

1. Get FetchXML query from a textarea field.
2. Build a SOAP message.
3. Send the SOAP message to CRM and register a callback function to process the data.

To do this, we will use the same function executeFetchCommand, buildFetchRequest and sendQuery methods from my previous post. We will be using a different processData method, though the name will stay the same.

Charting the data

Once the data is returned by CRM, we can generate the chart. To do that, we will do the following:

2. Loop through XML to extract count for each date.
3. Convert the count and date values to an array of X and Y values for Flot to plot.
4. Call the Flot plotting method.
5. Bind an event to our chart to display tooltip messages that show the X and Y values when the user mouses over a specific point.

First, our new processData callback function looks like this:

function processData(xml) {  
/// <summary>  
/// charts data returned in a FetchXML response using Flot  
/// </summary>  
  
alert("Charting started");  
  
//create a label for the chart points  
var yLabel = "Contacts created";  
  
//create an array to hold the plot data  
var series1 = [];  
  
//loop through xml and extract data from each "entity" element  
$("#chartdiv").append("Starting XML parsing . . . <br />");  
$(xml).find("a\\:Entity").each(function() {  
//pass the element to a function that extracts the aggregate value and the "group by" values  
//"plotValue" is the FetchXML alias for the data we want to display  
var pointData = getKeyValPairs($(this),"plotValue");   
  
//Flot requires datetimes to be passed as timestamp values  
var dateString = (pointData[1].year + "-" + pointData[1].month + "-" + pointData[1].day).replace(/,/g,'');  
var timestamp = new Date(pointData[1].year.replace(/,/g,''), parseInt(pointData[1].month)-1, pointData[1].day).getTime();  
  
//add x (timestamp) and y (aggregate data value) values to the data series array  
series1.push([timestamp, pointData[0]]);  
});  
  
//sort series array by date - Flot plots in order, so line can look wrong if we don't do this  
series1.sort(function(a,b){return a[0]-b[0];});  
  
//plot the data with some display options  
//see examples and API documentation at http://www.flotcharts.org/  
$.plot($("#chartdiv"),   
[ { data: series1, label: yLabel} ], {   
series: {  
lines: { show: true },  
points: { show: true }  
},   
grid: { hoverable: true },  
xaxis: {   
tickSize: [1, "day"],  
mode: "time",  
timeformat: "%m/%d/%y"  
}  
}  
);  
  
alert("Charting complete");  
}

In this method first we loop through the data and pass each element to a getKeyValPairs helper function to extract the necessary fields. It returns an array with the Y value to plot as element 0 and a JSON object containing all other fields as element 1. It looks like this:

function getKeyValPairs(entity, valuefield) {  
/// <summary>  
/// extracts an aggregate value and grouping components from a FetchXML response entity element and returns them in an array  
/// </summary>  
var outputArray = new Array();  
  
//first get the aggregate value using its alias ("plotValue" in the original example)  
var pointValue = parseFloat(entity.find("a\\:KeyValuePairOfstringstring").filter(function() {  
return $(this).find('b\\:key').text() == valuefield;  
}).find("b\\:value").text().replace(/,/g,''));  
var groupingArray = [];  
entity.find("a\\:KeyValuePairOfstringstring").filter(function() {  
return $(this).find('b\\:key').text() != valuefield;  
}).each(function() {  
var xmlElement = $(this);  
var key = xmlElement.find("b\\:key").text();  
var value = xmlElement.find("b\\:value").text();  
var groupingText = '"' + key + '":"' + value + '"';  
groupingArray.push(groupingText);  
});  
var stringToEval = groupingArray.join();  
var dateFields = eval("({"+stringToEval+"})");  
outputArray.push(pointValue);  
outputArray.push(dateFields);  
  
return outputArray;  
}

Once the data fields are returned by they getKeyValPairs method, the processData function creates a timestamp from the supplied month, day and year fields (Flot requires timestamps to plot by datetime), and then adds the timestamp and count to an array.

After the loop is complete, we sort the array by date to make sure the line will be plotted along our points in the correct order. Finally we call the Flot plot function with the data array and some pre-determined options. There are a lot of cool configuration options you can specify for Flot plots, but explaining them is beyond the scope of this point. Please take a look at the Flot examples page for more.

Finally we need some code to do the tooltip display. First we have a generic showTooltip method that will be executed when the mouse moves over a data point. You can customize the style to your liking.

function showTooltip(x, y, contents) {  
/// <summary>  
/// function to show tooltip when user mouses over data points  
/// </summary>  
$('<div id="tooltip">' + contents + '</div>').css( {  
position: 'absolute',  
display: 'none',  
top: y + 5,  
left: x + 5,  
border: '1px solid #fdd',  
padding: '2px',  
'background-color': '#fee',  
opacity: 0.80  
}).appendTo("body").fadeIn(200);  
}

Then we have code that binds a function to the chart plothover event so that the showTooltip method gets executed at the right time with the right data:

//bind a function to execute the showTooltip function at the appropriate time  
var previousPoint = null;  
$("#chartdiv").bind("plothover", function (event, pos, item) {  
$("#x").text(pos.x.toFixed(2));  
$("#y").text(pos.y.toFixed(2));  
  
if (item) {  
if (previousPoint != item.dataIndex) {  
previousPoint = item.dataIndex;  
  
$("#tooltip").remove();  
var date = new Date(item.datapoint[0]);  
var x = item.datapoint[0].toFixed(2),  
y = item.datapoint[1];  
  
//call showTooltip with the timestamp converted back to mm/dd/yyyy format  
showTooltip(item.pageX, item.pageY,  
item.series.label + " on " + (parseInt(date.getMonth()+1)) + "/" + date.getDate() + "/" + date.getFullYear() + " = " + y);  
}  
}  
else {  
$("#tooltip").remove();  
previousPoint = null;   
}  
  
}); 

Trying it out

Here is a complete web page with the code for you to download and try out in your own CRM instance chart_example.htm (8.30 kb). Just upload it as you would any other web resource, publish and open. At that point you will be presented with a screen that looks like this:

Either leave the pre-populated FetchXML in the textarea or supply your own that does a count by month, day and year. As long as it returns those three group-by fields and a Y value aliased as "plotValue," it should work. Once you click the "fetch and process" button, you will start to see some output and a couple of status alert windows before the output finally displays. The end result should look like this:

Of course this chart is actually not that exciting, but once you take a closer look at how you can format the line chart output with Flot, you'll see there are all sorts of interesting things you can do. You can also embed the charts in dashboards as a replacement for the existing Dynamics CRM line chart controls. Stay tuned for my next post when I show how to generate multi-series charts.

Over the weekend I started looking at a hobby project that involved querying and working with aggregate data from Dynamics CRM 2011 inside a hosted web resource using jQuery. Initially had I planned to use the OData/REST endpoint since that is much sexier than SOAP lately, but after a quick web search I realized that OData doesn't support "group by" queries, so that left me looking at FetchXML. As I mentioned in a post last month, I've generally avoided working with FetchXML, so I wasn't even sure where to get started to use it inside JavaScript. After another quick web search, I came across a great post on the Customer Effective blog called Execute Fetch from JavaScript in CRM 2011. That post shows a custom FetchUtil.js library that does three things:

1. Create a properly formatted SOAP request for a FetchXML query
2. Execute the request using the XMLHttpRequest object
3. Return the XML response to the browser

Using this original post to point me in the right direction, I combined the logic for creating the SOAP request with new jQuery code to handle the request and response. Here's how it works:

Getting the FetchXML

First we need to get the FetchXML to execute. For the purpose of this example, suppose we allow a user to execute ad-hoc FetchXML queries, so we need a method to read FetchXML from a textarea on a web page and then start processing. This method will be executed when a button is pushed:

function executeFetchCommand() {   
/// <summary>  
/// reads user-supplied FetchXML contained in the txtFetch textarea and starts processing  
/// </summary>  
  
//generate the SOAP envelope  
var fetchRequest = buildFetchRequest($("textarea#txtFetch").val());  
  
//send the request to CRM  
sendQuery(fetchRequest);  
}

The buildFetchRequest and sendQuery methods are described in the following sections.

Generating the request

First we need to generate the SOAP envelope from a FetchXML query. This method takes the query as an input parameter, properly encodes it and wraps it inside the envelope.

function buildFetchRequest(fetch) {  
/// <summary>  
/// builds a properly formatted FetchXML request  
/// based on Paul Way's blog post "Execute Fetch from JavaScript in CRM 2011"  
/// http://blog.customereffective.com/blog/2011/05/execute-fetch-from-javascript-in-crm-2011.html  
/// </summary>  
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(fetch);   
  
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 request;  
}

Executing the query

Once you have the SOAP envelope ready, you need to send the request to your CRM server. This method takes the SOAP envelope generated in the previous step, makes an asynchronous call to the CRM 2011 organization service using the jQuery ajax method and registers a callback function to process the returned results. (It also writes some status info to an output region of a web page to make it easier for you to follow the code execution.)

function sendQuery(fetchRequest) {  
/// <summary>  
/// uses jQuery ajax method to executes a FetchXML query and register a callback function  
/// </summary>  
  
//path to CRM root  
var server = window.location.protocol + "//" + window.location.host;  
  
//full path to CRM organization service - you may need to modify this depending on your particular situation  
var path = server + "/XRMServices/2011/Organization.svc/web";  
  
$("#outputdiv").append("Starting ajax request . . . <br />");  
$.ajax({  
type: "POST",  
dataType: "xml",  
contentType: "text/xml; charset=utf-8",  
processData: false,  
url: path,  
data: fetchRequest,  
beforeSend: function( xhr ){  
xhr.setRequestHeader( //without the SOAPAction header, CRM will return a 500 error  
"SOAPAction",  
"http://schemas.microsoft.com/xrm/2011/ Contracts/Services/IOrganizationService/Execute" //remove the extra space inserted to help this line wrap  
);   
}  
}).done(function(data) {  
//if successful, pass returned xml and the plot label to the charting function  
processData(data);  
}).fail(function(jqXHR, textStatus, errorThrown ) {  
//if unsuccessful, generate an error alert message  
alert( "Request failed: " + textStatus + "\n" + errorThrown );  
});  
$("#outputdiv").append("Ajax request complete. Output will be processed asynchronously. <br />");  
}

Here are a few things to note:

1. The inside the "done" function is where we tell jQuery what to do when a successful response returns data. We will look at the processData method more closely in the next section.
2. The "fail" function generates an alert window with error output. You could do something more sophisticated.
3. You probably want to leave all the other ajax configuration options alone unless you have a good reason not to. It took a good bit of trial and error for me to get this right (probably because I was up too late at night working it out, but I digress . . . ).

Handling the response

Assuming that everything went according to plan, our callback function will be called with the query results. At this point, you can do anything with them that you would normally do with XML inside of JavaScript. You can also convert the XML to JSON if that's more your speed. For demonstration purposes, I will show how to loop through the results and display the key-value pairs for each field returned.

Before I show the code to process the results, let's take a look at what the results would look like for this theoretical query:

<fetch distinct='false' mapping='logical' aggregate='true'>  
<entity name='contact'>  
<attribute name='fullname' alias='contact_count' aggregate='count' />  
<attribute name='createdon' groupby='true' dategrouping='day' alias='day' />  
<attribute name='createdon' groupby='true' dategrouping='month' alias='month' />  
<attribute name='createdon' groupby='true' dategrouping='year' alias='year' />  
</entity>  
</fetch>

In this query I am counting the number of contacts created by day, month and year. When I execute it against CRM, I get output that looks like the contents of the attached file: response.xml (6.76 kb) Here are some important points:

1. Each row of data is represented as an "a:Entity" element.
2. Individual attribute names and values can be found in the an entity's "a:Attributes" element and also in its "a:FormattedValues" element. In this example I will be working with the "a:FormattedValues" element because it's marginally easier to parse, however you may find times that it makes more sense to work with "a:Attributes" instead.
3. Everything under "a:FormattedValues" is represented as a string, so we would need to convert to other datatypes where necessary (ie. building a date object from the returned month / day / year values).
4. Each key-value pair under "a:FormattedValues" is contained in an "a:KeyValuePairOfstringstring" element.

Now that you know what the response from CRM looks like, here is the method that parses it and writes the output:

function processData(xml) {  
/// <summary>  
/// writes key-value pairs to a div  
/// </summary>  
  
alert("Output started");  
$("#outputdiv").append("OUTPUT FROM CRM <br />");  
  
//outer loop  
$(xml).find("a\\:Entity").each(function() {  
//write entity  
$("#outputdiv").append("Entity <br />");  
//inner loop  
$(this).find("a\\:KeyValuePairOfstringstring").each(function() {  
var xmlElement = $(this);  
var key = xmlElement.find("b\\:key").text();  
var value = xmlElement.find("b\\:value").text();  
$("#outputdiv").append("  Key: " + key + " Value: " + value + "<br />");  
  
});  
});  
  
alert("Output complete");  
}

You'll see there are two loops that use the jQuery find method. First the outer loop finds each a:Entity element, and then it passes that element to another function that executes the inner loop to find each a:KeyValuePairOfstringstring element. Then that element is passed to another function that extracts the text of the "b:key" and and "b:value" elements and writes them to the web page output div using jQuery's append method.

Trying it out

Here is a complete web page with the code for you to download and try out in your own CRM instance: jquery_fetchxml.htm (4.53 kb). Just upload it as you would any other web resource, publish and open. At that point you will be presented with a screen that looks like this:

Either leave the pre-populated FetchXML in the textarea or supply your own. Once you click the "fetch and process" button, you will start to see some output and a couple of status alert windows before the output finally displays. The end result should look like this:

As mentioned earlier, the attribute values retrieved are all strings, so you'll see the years have commas in them (2,013 instead of 2013). The solution is to either get the integer value from the XML response or parse the text value into an integer.

Caveats:

1. In the demo web page, I am using the jQuery library hosted by the Microsoft Ajax Content Delivery Network. If your users are accessing a CRM instance on your LAN, it would be better to host the jQuery library inside of CRM.
2. I don't know how this page hosted as a web resource will work in browsers other than Internet Explorer. Initially I did most of the jQuery prototyping work with a locally stored XML file on my PC using Chrome, and that code didn't work in IE without a lot of changes. Particularly noteworthy is that Chrome could find the XML elements without me needing to specify a namespace ("a:" or "b:"), but IE wouldn't. I did not go back and test whether the modified find selectors worked in Chrome.

I've typically preferred to access Dynamics CRM data using SQL queries, so I have never worked much with FetchXML. I've recently started working on a bit of a hobby project where it makes more sense to populate a datatable from using FetchXML than to use a SQL query, but unfortunately I have encountered three differences between FetchXML and direct SQL that I don't particularly like:

1. FetchXML results do not include attributes that are not populated in CRM, even if they are explicitly requested in the query.
2. You can't retrieve the label for a picklist using the attribute name + "name" convention.
3. Your code has to add additional logic to deal with AliasedValues, EntityReferences, etc.

Here is a class I wrote to execute a FetchXML query and populate a datatable with the results.

class QueryUtility  
{  
public DataTable ExecuteFetchXml(string fetchXmlFragment, IOrganizationService service)  
{  
//execute fetchxml  
FetchExpression fetch = new FetchExpression(fetchXmlFragment);  
EntityCollection fetchresults = service.RetrieveMultiple(fetch);  
  
DataTable resultsTable = new DataTable("results");  
if (fetchresults.Entities.Count > 0)  
{  
for(int i=0;i<fetchresults.Entities.Count;i++)  
{  
var entity = fetchresults.Entities[i];  
DataRow row = resultsTable.NewRow();  
foreach (var attribute in entity.Attributes)  
{  
if (!resultsTable.Columns.Contains(attribute.Key))  
{  
resultsTable.Columns.Add(attribute.Key);  
}  
row[attribute.Key] = getAttributeValue(attribute.Value).ToString();  
}  
foreach (var fv in entity.FormattedValues)  
{  
if (!resultsTable.Columns.Contains(fv.Key + "name"))  
{  
resultsTable.Columns.Add(fv.Key + "name");  
}  
row[fv.Key + "name"] = fv.Value;  
}  
  
resultsTable.Rows.Add(row);  
}  
}  
return resultsTable;  
}  
  
private object getAttributeValue(object entityValue)  
{  
object output = "";  
switch (entityValue.ToString())  
{  
case "Microsoft.Xrm.Sdk.EntityReference":  
output = ((EntityReference)entityValue).Name;  
break;  
case "Microsoft.Xrm.Sdk.OptionSetValue":  
output = ((OptionSetValue)entityValue).Value.ToString();  
break;  
case "Microsoft.Xrm.Sdk.Money":  
output = ((Money)entityValue).Value.ToString();  
break;  
case "Microsoft.Xrm.Sdk.AliasedValue":  
output = getAttributeValue(((Microsoft.Xrm.Sdk.AliasedValue)entityValue).Value);  
break;  
default:  
output = entityValue.ToString();  
break;  
}  
return output;  
}  
}