Dynamics 365 CE / CRM
User Group community for all Microsoft Dynamics 365 CE / CRM apps and the classic on-premise solution
Organizer:
- Organized by
-
-
Word and Excel Template capabilities / limitations
-
Word and Excel Template capabilities / limitations
Hi all! We’re currently using CRM 8.2 on-premise and researching a move to Online, but we have a lot of SSRS reports that first need to be converted to Word and Excel Templates since we can’t use SQL calls in the CRM Online environments.
I’m hitting some real limitations with the templates and I’m curious if the community has any advice or workarounds.
For Word Templates I’ve found that I can insert discreet fields like a typical mail merge, but if I need to insert a list of something into the Word template, such as advanced find results, there’s no way to filter the list. For example, if I want to create a Word template that is run on Contact records, and I want it to include a list of only active Orders for that Contact, I can’t do that because there’s no way to filter the list of Orders related to that Contact (so it would insert ALL Orders, rather than just the active Orders). Does anyone know of a way around this?
For Excel Templates I have not found a way to insert a single specific field into one cell of the Excel Template. For example, let’s say I open a Contact and go to their related Orders. From there I run an Excel template on Orders, and I can export only the active Orders, but I want to insert the Contact’s full name into a single cell at the top of the Excel template. I don’t see a way to do this when building the Excel template (it only seems to handle lists, not discreet fields).
Any ideas?
-
2 Replies
-
Hi Mike,
Good luck on your upcoming cloud journey. Here are a couple of tips to help with your document quandry.
1) You CAN continue to use SSRS reports, but you’ll need to convert any embedded SQL to to FetchXML. There are some very useful conversion utilities in the XRM toolbox (https://www.xrmtoolbox.com/) where you can paste in the SQL and get back the FetchXML.
2) If you’re used to writing SQL queries, you can use SSMS (Sql Server Management Studio) to connect to your online instance just as easily as your on-prem instance. In the login prompt for the database, use your CRM-Online URL i.e.<companyname>.crm.dynamics.com, select Microsoft Entra MFA for the Authentication (hopefully your org is using multi factor authentication, otherwise use the appropriate option), and your full e-mail address as the username. The database is read-only, but you can run standard SQL queries this way online.
3) Depending on the types of documents you’re looking to convert, you may want to look at a third party product called DocumentsCorePack for Microsoft Dynamics 365 (mscrm-addons.com). It has a custom MS Word add-on that lets you easily insert placeholders for the Dataverse data. IMO, the real power of this product is the one-click actions where you select all the steps in the document handling. We have a single button that creates a customer quote from a custom entity. With one button click, it creates a pdf, attaches it to a pre-defined dynamic e-mail template, and saves a copy of the document in SharePoint. The user opens the .eml file in outlook, makes any final changes and hits send.
-
Thanks, Jeff! I’ve used XRMToolbox a lot, but not for that purpose. The plugin I see with the description closest to your comment is Jonas Rapp’s FetchXML Builder. That’s a possibility and I’ll look into it.
Our situation is a bit different in that we’re a SaaS provider of Association Mgmt Systems and we use Dynamics as our framework. We’ve got about 130 clients to migrate online, so the solution we use for the reporting limitation (Word Templates, rewrite reports in Fetch, etc) will have to be something OOTB from their perspective. That rules out things that involve running SSMS locally and connecting to their instance (which they won’t know how to do).
It sounds like Word and Excel templates simply have the limitations I encountered. That’s a shame. Seems like MS could have gone a small step further, and allowed filtering of embedded lists in a Word template, and I could get to where I need to go.
Thanks for the tips!
Log in to reply.
