Dynamics CRM Word Document Templates that use FetchXML for Complex Data Retrieval

It is a little bit longer but informative post so please bare with me :)

Dynamics CRM now allows you to create and persist Document Templates using Microsoft Word. I 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. SO what if you want to pull in data from other entities that do not have a direct relationship in your data model? Or what if you want to retrieve data from another external system?

We have already created reports in SSRS that use FetchXML as the data source so what else can we do to leverage FetchXML as the data source for the new Word Document Templates?. This approach is possible and not at all difficult leveraging the Open XML SDK for Microsoft Office.

1. Retrieve your Data

Determine what data it to be be retrieves to display in the Word Template.

The easiest way to create your FetchXML query is to do an advanced find from within CRM and then 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 and how to get it, let’s move onto creating a Word Template.

2. Create Word Template in CRM

From the Settings menu select Templates and then Document Templates. 

From this page see the Available OOTB Templates that come with CRM and either Create a New Template or Upload one you already have.

For our approach, we will select New.

On the next screen, l select Word Template and for our example use the Account entity. Keep in mind that the entity you select is irrelevant with this 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  - pick any other Entities that have a direct Relationship to the Account. This is 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 select Download Template and 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.

follow the steps in this link: https://msdn.microsoft.com/en-us/library/bb608625.aspx

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

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

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.

We still have not specified what data this field should display. - 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 to return the results as a DataTable:

The call for that will look like the following:

This gives you a resulting XML in accountDataAsXML:

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, populate the tag on our Content Control so it looks like the following:

To keep this extremely simple since 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:

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

All Microsoft Office documents are just a bunch of files zipped together? 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.? You might see something like this:

We won’t examine all of these files, look at the document.xml in the word folder. If you open that file then 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? - 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.

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):

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.