Word Document Templates that use FetchXML for Complex Data Retrieval

Dynamics CRM now allows you to create and persist Document Templates using Microsoft Word. We were intrigued by this functionality and began to explore it immediately, but discovered one shortcoming. The user interface in CRM forces you to select a single starting entity and only entities that have a direct relationship for the data. But what if you want to pull in data from other entities that do not have a direct relationship in your data model? Or better yet, what if you wanted to retrieve data from another external system?

 

Having already created reports in SSRS that use FetchXML as the data source we began to investigate what we could create to leverage FetchXML as the data source for the new Word Document Templates.

 

We discovered this approach is possible and not at all difficult leveraging the Open XML SDK for Microsoft Office and what follows are the steps we took in our approach.

 

1. Retrieve your Data

The first thing we will do is determine what data we will be retrieving to display in the Word Template. The easiest way to create your FetchXML query is to just do an advanced find from within CRM and export the FetchXML from there. So, for our example, let’s just run a default Fetch for Accounts.

Click the Download Fetch XML button and Save the resulting Fetch statement. You should have a simple FetchXML query that looks like the following:

Now that we know what data we will be retrieving and how to get it, let’s move onto creating our Word Template.

 

2. Create Word Template in CRM

From the Settings menu select Templates and then Document Templates. From this page, you can see the Available OOTB Templates that come with CRM and either Create a New Template or Upload one you might already have.

For our approach, we will select New.

On the next screen, we will select Word Template and for our example we will just use the Account entity to keep things simple. Keep in mind that the entity you select is irrelevant with our approach since we will be retrieving and binding the data on our own.

After clicking Select Entity you are presented with the Select Entity screen where you can pick any other Entities that have a direct Relationship to the Account. This is what we found to be limiting since there might be other entities that we would like to pull in (or you might have data from other systems as well you would like to retrieve and display in the report). At this point let’s just select Download Template and go do some design in Word.

 

3. Populate Word Template with Content Controls

At this point we have a not-so-exciting blank document Open in Word. The first thing we need to do is Show the Developer tab in the Word Ribbon. To do so follow the steps in this link: https://msdn.microsoft.com/en-us/library/bb608625.aspx

 

Now we can simply insert Plain Text Content Controls to display the data we would like in the document.

 

To do so click the 2^nd Aa button in the Controls section of the Developer tab.

Once you do this, you will see the Content Control appear in your document at the position of your cursor that reads "Click or tap here to enter text". The Content Control is basically just a placeholder that you can use to control the layout of your document. But we still have not told it what data this field should display. To do that, we will select the Content Control and click Properties from the Controls section of the Developer tab.

 

Now here is a bit of the magic, you must be familiar with what data you will be retrieving which we already determined in Section 1 when defining our Fetch. You will need the following function to execute your Fetch and return the results as a DataTable:

 

The call for that will look like the following:

 

This gives you a resulting XML in accountDataAsXML that would appear like the following:

 

Let’s say for this first control we want to display the Account Name. The XPath for that in XML will be: /ReportData/Account/name. XPath is straight forward you just follow down the hierarchy of the XML.With that information, we can now populate the tag on our Content Control so it looks like the following:

To keep this extremely simple since we just illustrating the concepts, we can save the template and upload it into CRM. Just like before, from the Settings menu select Templates and then Document Templates. From this page, you can select UPLOAD TEMPLATE and browse out to the Word Template on your local system.

 

4. Retrieve Word Template Contents

 

When you get ready to run the report (how you choose to do this is entirely up to you and can be custom workflow activity, plugin, etc.) you will need to retrieve the Word Template Contents. Here is a function to get the Document Template ID which is a basic retrieve of the DocumentTemplate entity with a name filter.

 

Once you have that, you can get the Document Template contents as a byte array like this:

 

5. Merge the data into the Word Document Template

 

So now at this point we have our CRM data in an XML string and our Word Document contents in a byte array and we need to get the two to merge. Here is an example of how to do that:

Wow, that’s a large amount of code, what does it all do? While most of this is probably understandable to a CRM developer, the main part to pay attention to is the using statement with the WordprocessingDocument. To understand that code, we must take a quick detour into the land of OpenXML.

 

Did you know that all Microsoft Office documents are just a bunch of files zipped together? Yep, it’s true. Take that Word Document Template we have been working with on your machine for this example and change the .docx extension to a .zip. Now extract that file. Whoa! Learn something new every day, huh? You might see something like this:

We won’t examine all of these files, but let’s look at the document.xml in the word folder. If you open that file you will see an XML representation of what is in your Document starting with a ton of namespaces for the different Office libraries. After that you get into the body of the document which will look something like this:

Notice anything familiar? Yes, that XPath that we entered on the Content Control is there in the tag right where we put it. The Open XML SDK for Microsoft Office is what allows you to manipulate this underlying XML. So let’s do a quick breakdown of that code above.

 

If you would like to save the generated document as a Note on an entity, you could do something like the following (which again is fairly common CRM coding):

 

The End:

 

Well, that was a whirlwind tour of how to leverage FetchXML for data retrieval and use that data within a Word Document Template.  To recap the steps are:

1. Retrieve your data
2. Create Word Template in CRM
3. Populate Word Template with content control placeholders
4. Retrieve the Word Template contents
5. Merge the data into the template using the Open XML sdk.

 

Keep in mind, that FetchXML is obviously only one choice for data retrieval and we chose that since we were porting over existing SSRS reports. You could also leverage other means of gathering data such as web services to external systems. The only real requirement is that the data you retrieve eventually ends up in an XML string so you can leverage XPath to bind it to the Content Controls.

 

Written by Shawn Strayer, Web Developer at Rockton Software, a Microsoft Dynamics CRM add-on partner.