All the Dynamics projects I’m working on right now are still on 2013-2015 versions of Dynamics, and they are all on-prem implementations. This is because all of them are for different government departments here in Ottawa, and, somehow, 2016 version still has not arrived to this place.
So, all those fancy features available out of the box in the 2016/365 versions are just making me feel somewhat jealous of everyone who is enjoying them today. And the ability to use Word/Excel templates is definitely one of such features.
This post is not necessarily going to reveal some new features, but, at the very least, it’s going to put those Excel/Word template features in perspective so my fellow government consultants knew what they are missing.. and so that others knew how lucky they are
1. If I had Word Templates, that would give me(and my CRM users) the ability to create summary/print forms easily.
As simple as that, but, without Word Templates, I have to rely on the SSRS reports for all those overviews/summaries, etc. And keep in mind it’s not just the ability to put entity fields into the document – it’s, also, the ability to use related entities, to create tables, and to use formulas in those tables.
Have you seen this page?
It’s talking about how to use formulas in Microsoft Word. Usually, we would be using formulas in Excel, but it’s also possible to do some of that in Word. We can use SUM(ABOVE) to get the sum of all cells above the current cell, or we can use SUM(LEFT) to get the some of all cells to the left of the current cell. We can even mix those in the formulas such as SUM(LEFT,ABOVE).
We can use mathematical functions, we can calculate averages, we can round the numbers, etc.
But that’s not all. Before there were Word Templates, it had always been a problem to generate the reports programmatically/in workflows. That is not a problem with Word Templates – there is an out of the box custom action for that:
Even if that custom action solves only half of the problem, it’s definitely the bigger half. We do need a custom workflow activity to extract note attachments after that, but it’s a routine development – no tricks are needed. If you are interested in more details, you can have a look at this post:
Either way, creating a word template like this does not take much time with Word Templates:
And, then, there are Excel Templates..
2. If I had Excel Templates, that would give me(and my CRM users) the ability to create nice excel reports easily.
Maybe you would argue that there have always been excel imports, dynamics spreadsheets, etc. It’s not the same, though. When there is an excel template, all users can be using the same template, so there is no problem of having multiple versions of the same excel spreadsheet without any synchronization between those versions. There is, also, no need to install Outlook client to ensure that Dynamics spreadsheet can connect to CRM. There is no need to set up a SQL data source either.. Users can just choose a view, choose a template, and they will get a nice dashboard like this one:
It’s almost like having an SSRS report.. although, it may, actually, be even better in many cases simply because it’s excel, after all.
Note: Right now I’m not sure if it’s possible to generate an excel file based on an excel template programmatically.
And if you are interested in more details on the personal templates vs system template, on the security settings etc, have a look at this post: https://community.dynamics.com/crm/b/crmandunifiedservicedesk/archive/2015/12/09/crm2016-template-management
It would be just awesome if we could use Excel/Word Templates on my current project where I had to develop a custom template engine that’s based on the usage of the rtf files – that would save me a lot of time.
So, to wrap it up.. If you are still on the earlier versions of Dynamics for whatever reason, I think you should seriously reconsider your attitude There are other new features, but, even if you only got Excel/Word Templates as a result of the upgrade, it might well be worth the efforts.
It’s prpbably worth adding a caveat that the Word templates, though awesome, do not allow you to filter the related entity data that you pull (i.e. you can’t only show Active related records). You also can’t sort the data before it is pulled, and it is limited to the first 100 records from the related entity.
As Ryan says the Word functionality is seriously restricted by the fact CRM returns all related records to the template and does not allow you to specify the view of the data you wish to return.
This is compounded by the fact you can’t easily add business logic into the Word template so instead of easily returning a sorted list of active orders you instead end up with a seemingly randomly sorted list of every order made by the customer including cancelled, fulfilled…..
Sadly its a case of so near yet so far and best to hunt for a 3rd party solution….
Thanks Ben and Ryan for the comments. I’ll add another one (which Parvez Ghumra made on linkedin) just so it’s all here:
***************************************
Whilst Word and Excel templates are quite a cool new feature of Dynamics, it’s also worth pointing out that, as with most other ‘new’ features, there are also some limitations to be aware of:
1. As a Systems Integrator, there’s no out of the box way including your templates into your solution packages for easier deployment. And because they are linked to their target entities based on the entity type code (which will be different in different environments for the same entity) you cannot easily transport them between environments using your normal reference data process either. You need to use one of the community tools to help with this, or write your own custom tool/logic to support this.
2. In building your templates and traversing your entity relationships to populate the data you require, there’s a limit on the out of the box way of doing it in terms of the depth of relationships you can traverse, which is 1. So for more complex queries and entity relationship structures you would still require custom development to get at the data you need, or alternatively, de-normalize your entities to make the right data elements available to your templates (and then implement something to keep those data elements in sync).
Pingback: Related entities in Word Templates - Microsoft Dynamics CRM Community