CRM Software Logo

Microsoft Dynamics 365 & Dynamics CRM experts provide reviews and opinions to aid professionals with the Dynamics 365 selection process.

 
 
Mark Rockwell, Rockton Software

Word Document Templates that use FetchXML for Complex Data Retrieval

Email | Print

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.

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 2nd 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.

6 Responses to “Word Document Templates that use FetchXML for Complex Data Retrieval”

  1. Daoud Malikyar says:

    This looks very powerful. I have a couple of questions:
    1) If I have a Word Template with multiple sections (using tables), can I sort/filter the content of a given section via FetchXML like I can in SSRS FetchXML?
    2) I know the Developer Tab isn’t available in the MacOSX version of Word, therefore I can’t develop the report from a Mac. However, can I render the Word Template report as a user from CRM that’s running on a Mac?

    Thanks.

  2. Kevin Carter says:

    This is pure gold. Thanks for explaining the concept so well.

  3. finnegan says:

    Hi Mark,

    thank you for this fantastic article.

    I ran into one problem though:
    You cannot use the DocumentFormat.OpenXml library within a CRM plugin that is registered in sandbox mode. CRM online requires you to register a plugin in sandbox mode.

    In general, a plugin in sandbox mode is not allowed to call other dlls.

    Any ideas on how to get around this limitation? Otherwise your approach is limited to on-premise CRM installations, and even there it is not a good idea to run plugins outside the sandbox mode.

    Thanks!

    finnegan

    • Hi Finnegan,

      Thanks for your feedback and inquiry.

      You are absolutely correct with regards to the Sandbox Mode limitations.

      Another impediment to having this fully embedded in CRM is the fact that the DocumentFormat.OpenXml library is over 5MB on its own and CRM assemblies cannot be over 8MB.

      For those two reasons we isolated all of our document manipulation logic into an Azure Cloud Service that we call from within CRM.

      From there we also make a call out to a 3rd Party service called CloudConvert to convert our completed Word Documents into PDFs for portability when sending to customers.

      I probably should have done a better job explaining how we isolated that layer of our solution.

      Thanks,
      Shawn

      • finnegan says:

        Hi Shawn,

        thanks for your quick reply and for clearing things up.

        After my first post I found out that with VS2015 you could add shared projects so that all referenced libraries are compiled into one dll. But even then sandbox mode would not be possible for – as you point out in your kind reply – the resulting plugin dll would probably exceed the 8MB CRM assembly size limit.

        So it seems that even such seemingly trival tasks as merging a word template with CRM data in a plugin require major efforts.

        Thanks again for your inspiration!

        finnegan

Ask This Expert a Question / Leave a Comment

 

 
 
Show Buttons
Hide Buttons