This is the second part of my 2-part series, continuing our evaluation of the new Word Templates feature versus the traditional CRM/SSRS Reports route. Word Templates were recently introduced as part of CRM 2016, and are one of the big new features that has got me really excited about the future of CRM. What I am keen to discover is if they can be utilised as effectively as .rdl CRM Reports to produce high quality and professional looking documents.

Last week we took a look at the process and steps involved in setting up a Report. So now, let’s make a start and go through the step-by-step process of setting up a Word Template document on a CRM 2016 instance:

  1. From a setup point of view, there is much less that is required in order to start working with Word Templates:
    • CRM Online 2016: If you don’t have access to a CRM Online 2016 instance, you can either reset a Sandbox Instance or start a free 30 day trial. My understanding is that Word Templates have been introduced as part of 2016 On-Premise CRM, but I’m unable to confirm this.
    • Microsoft Word 2013/2016
  2. Log into your CRM instance and navigate to a supported record type. For this example, we are going to use Lead:WordTemplate_2
  3. On the Form Ribbon, click on the ellipse to expand the button Options and select ‘Word Templates‘:WordTemplate_4
  4. You will then be greeted with the ‘Create template from CRM data‘ window, enabling you to specify the template type you want to create (Excel or Word), confirm the entity data you wish to use and choose whether to upload an existing template or create one from scratch. We’ll click on Word Template and then press ‘Select Entity‘ to proceed:WordTemplate_5
  5. Finally, CRM will display an informational window which gives the user a quick summary of the different related record types and, therefore, what additional fields can be displayed on your Word Template. This can be quite useful for novice CRM users or for those who are unfamiliar with how a particular CRM system has been customised. When you are ready to continue, press ‘Download Template‘ and then save the document to your local computer:WordTemplate_6
  6. Once downloaded, open the template. You’ll be greeted with a blank Word document, similar to the below:
    WordTemplate_8

Don’t panic though! The document has everything we need to make a start, but first we need to ensure that the Developer tab is visible. To switch this on, you will need to:

  • Go to File -> Options to open up the Word Options window:WordTemplate_10
  • Go to Customize Ribbon and make sure that the Developer Tab check-box is ticked. Once this is done, press OK:WordTemplate_11
  1. On the Developer Tab, you should see a button called ‘XML Mapping Pane‘. Click this button to open up a new pane to the right of the screen:

WordTemplate_12 WordTemplate_13

  1. Under the ‘Custom XML Part’ dropdown, you should see an option similar to this (may be different depending on the entity that you are building the template for):

urn:microsoft-crm/document-template/lead/4/

Once selected, you should see all of your entity fields appear below:

WordTemplate_15

  1. With the XML Mapping configured correctly, our CRM data fields can be moved onto our Word Document. To copy across each field onto the Word Document, all you need to do is right click the field, select ‘Insert Content Control‘ and then ‘Plain Text‘. Your field will be added onto your empty Word Document into the cursor area:WordTemplate_16

WordTemplate_17

  1. Now we can start to build our report! Here’s one I made earlier, with the help of some of the existing templates that Word provides for Letters:WordTemplate_18

With our document completed, we can now save it and upload it into CRM. To do this, we first need to navigate back to the ‘Create template from CRM data‘ from step 4) and, this time, select ‘Upload‘ instead of ‘Select Entity‘ to be greeted with the document upload window:

WordTemplate_20

  1. Once we have uploaded our document, we can then select our newly uploaded document from the Word Templates button on our Lead form to download the document, populated with our specific record information:WordTemplate_21 WordTemplate_22

Conclusions

So is it time to ditch .rdl Reports in favour of Word Templates then? I would certainly say so for instances where you just want to create documents which require very little data manipulation and where the key focus is around presentation of the document. Microsoft Word is certainly a much more accessible tool than SSRS when it comes to quickly creating documents that look visually appealing. That’s not to say that .rdl Reports will not still have a role moving forward, particularly when requirements are a little more complex. For example:

  • You need to use a customised FetchXML report to return data that is filtered a certain way or is, for example, returning multiple <link-entity> fields.
  • You are wanting to develop a report that a user needs to be able to filter at report run-time.
  • You need to leverage some of the advanced functionality made available via SSRS Expressions.

I therefore do not foresee a massive exodus towards Word Templates in the near future. It is more likely instead that Word/Excel Templates become the “preferred” report building tool, whereas .rdl Reports instead are used for “advanced” scenarios. I certainly am looking forward to using Word (and indeed Excel) templates moving forward and, as part of this, ensuring that some of our CRM Super Users receive training on how to use the feature as well. Giving users the power to create their own reports, using the tools they know and use every day, is very exciting!

One observation I had in regard to Word Templates is that Word would occasionally hang on my computer for approx. 15 seconds when moving some of the fields from CRM around the document. I am guessing this delay might be caused by the fact the document is attempting to connect back to your CRM instance on regular intervals. Apart from that, there were really no issues in terms of usability and setup – everything was really straightforward, quick and familiar. These are most certainly the key experiences that Microsoft are aiming for as part of this new feature, and I am reasonably confident that any teething problems will be addressed swiftly so as to encourage as many people as possible to start using this feature moving forward.

For those who have done a lot of work previously creating bespoke document templates within CRM, the only effective way in which you would traditionally do this within CRM was via a Report. For the uninitiated, reports are .rdl files that are created within CRM (for very basic reports) or via SQL Server Data Tools (for more complex/bespoke reports).

Dynamics CRM 2016 has potentially flipped this approach on its head with the introduction of Word Templates. Now, you can use Microsoft Word to develop and customise a template that can then be populated with the information you need from a CRM record. Given that Word is a far more accessible and familiar tool for many people, this new feature could be a game changer and major boon to CRM Administrators and Developers.

Having worked myself with SSRS a lot previously to create .rdl reports (and developed quite a fondness for it as a result), I am really interested in seeing whether it is more efficient and easier to use Word Templates compared to a .rdl report. So let’s find out by creating a very basic custom introduction letter that a business could use to generate for a Lead record. We’ll attempt both methods to see the steps, effort and ease of use involved for each, and then decide which tool is the winner. Given the number steps involved, this will be split across two separate posts, with the first post focusing on SSRS,

The steps below assume that you have not previously authored any custom SSRS reports on your system and that you have a FetchXML query ready to return data from CRM. We’ll be using the following basic query to return Lead data that should work with any CRM instance:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="lead">
    <attribute name="fullname" />
    <attribute name="companyname" />
    <attribute name="telephone1" />
    <attribute name="leadid" />
    <order attribute="fullname" descending="false" />
  </entity>
</fetch>

Please note the enableprefiltering=”1” option above, if you are using your own custom FetchXML query, then this line we will need to be added to the <entity> node. Otherwise, your report will not upload correctly later on:

  1. First things first, you will need to download all of the software you need in order to build the reports. Technet has a great article that goes over what you need, the salient bits of which are as follows:

Both downloads are fairly small and shouldn’t take long to install. During the Report Authoring Extension Setup, you may be prompted to install Microsoft Online Services Sign-in Assistant, press Yes if this the case:

SSRS_1

  1. Once installed, open up SSDT using Start -> Search or in Program Files -> Microsoft SQL Server 2012:

SSRS_2 SSRS_3

  1. Once open, go to ‘File’ -> ‘New’ -> ‘Project…’ You’ll see a Window similar to below:

SSRS_4

Select the ‘Report Services Project Template’, give your Project a name and then press ‘OK’. An empty solution file will be created, with the following folders visible on the right under Solution Explorer:

SSRS_5

  1. Typically, at this point you would create your Shared Data Sources/Datasets. Unfortunately, CRM reports do not support these so we need to create our report first. Right click the Reports folder and go to ‘Add’ -> ‘New Item…’. Select Report, give it a logical name, and press ‘Add’:

SSRS_6

The report will open for you within VS:

SSRS_7

Now we can start to create our data sources 🙂

  1. On the left hand side, under ‘Report Data’, right click on the Data Sources folder and select ‘Add Data Source…’ You’ll then need to enter your CRM instance settings:
    • Tick the box where it says Embedded Connection
    • On the dropdown list for Type, select ‘Microsoft Dynamics CRM Fetch’
    • Under Connection String, enter the URL for your CRM instance. There are also two additional parameters that can be specified, but only really useful if your URL points to multiple CRM organizations and/or if you have Active Directory Federation setup.
    • Under Credentials, if you are connecting to an On-Premise CRM instance, select ‘Use Windows Authentication (integrated security)’; if your are connecting to CRM Online, then select ‘Use this user name and password’ and enter your CRM Online login details.

    We’ll test these connection string settings in a few moments

  2. Now that we have our Data Sources, we can create our Datasets (too many Data’s, eh?). Right click on Datasets and select ‘Add Dataset…’:

SSRS_8

Enter the following settings:

  • Enter a name for your Dataset, ideally something descriptive in terms of what data the report is returning
  • Tick ‘Use a dataset embedded in my report.
  • Under Data Source, select your newly created CRM Data Source
  • Ensure that under Query Type, Text is selected
  • In Query, copy + paste or (for bonus points!) manually type your FetchXML query
  • Click Refresh Fields. After a few moments, you should then be able to click on ‘Fields’ on the left pane and see all of the fields from CRM. This means its working! If you get an error message, then double check your connection string details

SSRS_9

SSRS_10

  1. Now the fun part – time to build the report! I will probably do a future blog post on some of the cool things you can do with SSRS. Suffice it to say, for the purposes of this post, we are just going to create a very basic report that displays some field data in a tablix. The report will be run from an individual record, thereby pulling in its data fields. First of all, we will add a tablix to the report. This is as simple as right clicking on the report area, selecting Insert -> Table. The tablix will then appear with a dotted line on the report area:

SSRS_11 SSRS_12

  1. Because there is only one dataset configured for the report, the tablix will automatically associate itself with this. We can therefore start to add in the field by click on the top left a column (where the small table icon appears) and then selecting each individual data field we want to add. SSRS will also add on the appropriate header text for each field you put onto the tablix:

SSRS_13

SSRS_14

For this report, we will add on the fullname, companyname and telephone1 fields. After adding on some customised text to make the report look like a letter, the report should look something like this:

SSRS_15

Very basic I know! But the report will illustrate well what .rdl reports can do within CRM.

  1. Now that the report is finished, we can upload it onto CRM. To do this, we will need to locate the .rdl file for the report first. Press “Build” on your Visual Studio solution (in order to ensure that you have the most up to date version saved to disk) and then open up your Project solution folder in Windows Explorer to find your newly created report:

SSRS_16

SSRS_17

 

  1. Moving across now into CRM, we need to either go into our target Solution or alternatively customise the system via the Default Solution (not recommended for development/Production environments!). On the left-hand bar, we will see a section called Reports. Click on it, and then on New in the centre area to open a pop-up window, which lets us start adding a report into CRM:

SSRS_18 SSRS_19 SSRS_20

It’s useful at this stage to explain what some of the different fields/options mean:

  • Report Type: There are three options here. The first is Report Wizard Report, which takes you through a wizard in CRM to enable you to create a basic report utilising Advanced Find-like filter criteria. You can even make a copy of an existing report and modify it, though you will be limited in your customisation options. The second option is Link to Webpage and enables you to specify a URL to a report that exists outside of CRM (e.g. SAP Crystal Report). The final option, and the one we will be using today, is Existing File and lets you upload a .rdl file into CRM.
  • Parent Report: Similar to what you can do within SSRS Server, you can setup Parent/Child Reports to link together similar reports (e.g. you could have a Parent Account report, that then has a Child Report which shows all of the Contact Details for the Account)
  • Categories: These are grouping options to indicate the type of report you are adding.
  • Related Record Types: Here you need to specify which CRM entities the report can be run from.
  • Display In: Indicates where the report will be visible from. You can select one or many of the options. Reports Area will display the report from the Sitemap Report area, Forms for related record types will make the report available from the Form of the entities you have specified in Related Record Types and, finally, Lists for related record types will do the same, but from the Entity View page instead.
  1. Because we want our report to run on Lead forms only, we need to ensure that the Related Record Types contains Lead, and to ensure our Display In options are configured accordingly. Finally, we need to upload a report by selecting Choose File and then populate the other details accordingly. Your pop-up window should look similar to the below when you are ready to save:

SSRS_21

  1. Save and the Publish your changes. Assuming no problems, you should now see your Report on the Run Report button on your Lead form:

SSRS_22

As you can see, creating a CRM .rdl report for the first time can be quite time consuming and then, depending on the complexity of the report you are trying to create, could take even longer on top of that. I am therefore really interested in finding out as part of next week’s blog post what the process is like for Word Templates, and whether it is a quicker and more effective means of creating reports.

The Scenario: You are running CRM Online in conjunction with some legacy database/application systems. These systems are setup with a SQL Server Reporting Services instance that is looking to either an SQL Server, OLE DB etc. database.

The Problem: You need to make data from your legacy systems visible within your CRM. The information needs to be displayed on the Entity Form and show specific information from the legacy database that relates to the CRM record.

Admittedly, the above is perhaps somewhat unlikely situation to find yourself in, but one which I recently had to try and address. I suppose the most straightforward resolution to the above is to just say “Get rid of the legacy system!”. Unfortunately, the suggestion didn’t go down to well when I voiced it myself…

So at this point the next best answer looked to be try and utilise what we have within the existing infrastructure: an all singing, all-dancing SSRS and SQL Server database instance.

What if we were to try uploading an .rdl file that includes a FetchXML and our SQL/OLE DB database data source into CRM? Whenever you try to perform this, you will get this error message:

ReportUploadError_NoFetchXML

 

Rats! So there is no way in which we can include a non-fetch XML Data Source to our separate SSRS report instance. So is there anything else within CRM that can be utilised to help in this situation? Let’s first take a quick look at the following nifty little feature within CRM, courtesy of our good friend MSDN:

You can use an IFRAME to display the contents from another website in a form, for example, in an ASP.NET page. Displaying an entity form within an IFrame embedded in another entity form is not supported.

Use the getValue method on the attributes that contain the data that you want to pass to the other website, and compose a string of the query string arguments the other page will be able to use. Then use a Field OnChange event, IFRAME OnReadyStateComplete event, or Tab TabStateChange event and the setSrc method to append your parameters to the src property of the IFRAME or web resource.

You may want to change the target of the IFRAME based on such considerations as the data in the form or whether the user is working offline. You can set the target of the IFRAME dynamically.

Source: https://msdn.microsoft.com/en-gb/library/gg328034.aspx

Having worked extensively with SSRS in the past, I am also aware that you can use an SSRS URL string in order to specify properties about how the report is rendered, its size and – most crucially – what the value of required parameters should be. The friend that keeps on giving has a great article that goes through everything that you can do with an SSRS report URL and also how to use Parameters as part of your URL. So in theory therefore, we can place an IFRAME on our form and then use JScript to access form-level field values and modify the IFRAME URL accordingly.

Here are the steps involved:

  1. Go into Form Editor and add a new IFRAME to the form, specifying the following settings:

Name: The Logical name of the control, this will be required as part of the JScript code used later, so make a note of it.

URL: As this is a mandatory field, you can specify any value here as it will change when the form is loaded by the user. This is not practical as we don’t want this to be displayed if, for example, the field that we are passing to the URL has no value in it. Our JScript code will sort this out in a few moments

Label: This can be anything, and defaults to whatever is entered into the Name field

Restrict cross-frame scripting, where supported: Untick this option

Ensure that ‘Visible by default’ is ticked

Your settings should look something like this:

IFRAMESettings

  1. Create or modify an existing JScript Library for the form, adding in the following function (after modifying the values accordingly):
function onLoad_LoadSSRSReport() {

    //First get the page type (Create, Update etc.)

    var pageType = Xrm.Page.ui.getFormType();
    
    //Then, only proceed if the Form Type DOES NOT equal create, can be changed depending on requirements. Full list of form types can be found here:
    
    //https://msdn.microsoft.com/en-us/library/gg327828.aspx#BKMK_getFormType

    if (pageType != "1") {

        //Get the value that you want to parameterise, in this case we are on the Account entity and need the Account Name

        var accountName = Xrm.Page.getAttribute("name").getValue();

        //In order to "accept" the parameter into the URL, spaces need to be replaced with "+" icons

        accountName = accountName.replace(/ /g, "+");

        //Now, get the the name of the IFRAME we want to update

        var iFrame = Xrm.Page.ui.controls.get("IFRAME_myssrsreport");

        //Then, specify the Report Server URL and Report Name.

        var reportURL = "https://myssrsserver/ReportServer?/My+Reports/My+Parameterised+Report&MyParameter=";

        //Now combine the report url and parameter together into a full URL string

        var paramaterizedReportURL = reportURL + accountName;

        //Finally, if there is no value in the Account Name field, hide the IFRAME; otherwise, update the URL of the IFRAME accordingly.

        if (accountName == null) {
            iFrame.setVisible(false);
        }
        else {

            iFrame.setSrc(paramaterizedReportURL);
        }
    }
}
  1. Add the function to the OnLoad event handler on the form. Now, when the form loads, it will update the IFRAME with the new URL with our required parameter.

And there we go, we now have our separate SSRS instance report working within CRM! A few things to point out though:

  • If the report parameter supplied does not load any matching records, then SSRS will display a standard message to this effect. You would need to modify the report settings in order to display a custom message here, if desired.
  • It is recommended that you have https:// binding setup on your report instance and supply this to as part of the setSrc method. http:// binding works, but you may need to change settings on your Web Browser in order to support mixed mode content. Full instructions on how to set this up can be found here.
  • This may be stating the obvious here, but if your SSRS instance is not internet-facing, then you will get an error message in your IFRAME if you are not working from the same network as your SSRS instance. Fortunately, SSRS can be configued for an Internet deployment.
  • The steps outlined in 1) can also be used to specify a non-parameterised SSRS report within an IFRAME dashboard too. I would recommend using the following SSRS system parameters as part of the URL though:
    • rs:ClearSession=true
    • rc:Toolbar=false

e.g.

https://myssrsserver/ReportServer/Pages/ReportViewer.aspx?%2fMy+Reports%2fMy+Non+Parameterised+Report&rs:ClearSession=true&rc:Toolbar=false

One of the most challenging things about any system migration is ensuring that information from other business systems can be made available, and it is good to know that CRM has supported approaches that can help to bridge the gap.