15.3 Microsoft Word & Excel Document Templates

Microsoft Word & Excel Document Templates

Standardized business documents—such as branded sales quotes, customer invoices, onboarding packets, NDA agreements, and analytical performance workbooks—are vital to enterprise operations. Microsoft Dataverse provides built-in Document Templates for Microsoft Word and Microsoft Excel. These templates allow functional consultants to merge live Dataverse data into professionally styled office documents with a single click. On the PL-200 exam, you must master template types (System vs. Personal), the Word XML mapping architecture (including 1:N repeating tables and N:1 lookups), dynamic Excel data refresh with security trimming, and solution-based ALM deployment.

---

1. Document Template Types & Architecture

Dataverse organizes document templates into two distinct formats and two distinct scope levels:

+-----------------------------------------------------------------------------------+
|                         DOCUMENT TEMPLATES ARCHITECTURE                           |
|                                                                                   |
|  +-------------------------------------+  +------------------------------------+  |
|  |      MICROSOFT WORD TEMPLATES       |  |      MICROSOFT EXCEL TEMPLATES     |  |
|  |      (.docx Document Generation)    |  |      (.xlsx Analytics & Reporting) |  |
|  |  - XML Schema Binding               |  |  - Pivot Tables & Charts           |  |
|  |  - Single Entity Fields             |  |  - Static Worksheets (Snapshot)    |  |
|  |  - N:1 Parent Lookups               |  |  - Dynamic Worksheets (Live ODC)   |  |
|  |  - 1:N Repeating Line Items         |  |  - Dataverse Security Trimming     |  |
|  +-------------------------------------+  +------------------------------------+  |
|                                                                                   |
|  +-----------------------------------------------------------------------------+  |
|  |                             TEMPLATE SCOPES                                 |  |
|  |                                                                             |  |
|  |   SYSTEM TEMPLATES                     PERSONAL TEMPLATES                   |  |
|  |   - Scope: Entire Organization         - Scope: Individual Creator          |  |
|  |   - Author: System Admin / Customizer  - Author: Any Authorized User        |  |
|  |   - Management: Settings -> Templates  - Management: Form / View Command Bar|  |
|  |   - ALM: Solution-Aware Components     - ALM: Cannot be added to Solutions  |  |
|  +-----------------------------------------------------------------------------+  |
+-----------------------------------------------------------------------------------+

System Templates vs. Personal Templates

• System Templates:
  • Available to all users across the organization who have read access to the underlying table.
  • Managed centrally in Advanced Settings -> Settings -> Templates -> Document Templates (or PPAC).
  • Solution-Aware: Can be packaged into Dataverse unmanaged and managed solutions, enabling automated deployment across Development, Test, and Production environments via Application Lifecycle Management (ALM) pipelines.
  • Requires administrative privileges (prvCreateDocumentTemplate, prvWriteDocumentTemplate) to create or publish.
• Personal Templates:
  • Created and uploaded by individual business users directly from the command bar (Word Templates / Excel Templates -> Upload Template).
  • Visible only to the user who uploaded the template.
  • Can be shared with individual colleagues or teams using the standard Dataverse Share dialog.
  • Limitation: Personal templates cannot be added to Dataverse solutions or deployed via ALM pipelines.

---

2. Authoring Microsoft Word Document Templates

Microsoft Word templates enable automated mail-merge operations where Dataverse field values dynamically populate pre-formatted Word documents.

+-----------------------------------------------------------------------------+
|                   WORD TEMPLATE AUTHORING WORKFLOW                          |
|                                                                             |
|  [1. DOWNLOAD]           [2. DEVELOPER TAB]        [3. XML MAPPING PANE]    |
|  Select Primary Table    Enable Developer Tab      Open XML Mapping Pane;   |
|  and 1:N / N:1           in Word Ribbon Options.   select custom Dataverse  |
|  relationships.                                    XML part.                |
|         |                         |                         |               |
|         v                         v                         v               |
|  +---------------+       +------------------+      +-----------------+      |
|  | Dataverse     |       | Microsoft Word   |      | Map Content     |      |
|  | Template File | =====>| Ribbon ->        | ====>| Controls:       |      |
|  | (.docx)       |       | Developer Tab    |      | - Plain Text    |      |
|  +---------------+       +------------------+      | - Repeating (Table)    |
|                                                    +--------+--------+      |
|                                                             |               |
|  [5. GENERATE DOC]       [4. UPLOAD TEMPLATE]               |               |
|  User clicks 'Word       Upload .docx to Dataverse <--------+               |
|  Templates' on form.     as System or Personal Template.                    |
+-----------------------------------------------------------------------------+

Step-by-Step Authoring Process

1. Download the Template Template Shell from Dataverse

• In Dataverse, navigate to Settings -> Templates -> Document Templates (or click Word Templates -> Download Template on a record).
• Select the Primary Table (e.g., Quote or Account).
• Select the Relationships to include in the XML schema:
  • 1:N (One-to-Many): Used for repeating line items (e.g., quote_details for Quote Products, contact_customer_accounts for Account Contacts).
  • N:1 (Many-to-One): Used for parent lookup fields (e.g., quote_customer_accounts to pull Account details onto the Quote, or system_user_quotes to pull the Sales Rep's profile).
  • N:N (Many-to-Many): Used for peer relationships.
• Download the generated .docx file.

2. Enable the Developer Tab in Microsoft Word

• Open the downloaded .docx file in Microsoft Word Desktop.
• Navigate to File -> Options -> Customize Ribbon.
• Check the Developer checkbox in the right-hand column and click OK.

3. Open the XML Mapping Pane

• On the Word ribbon, click Developer -> XML Mapping Pane.
• In the Custom XML Part dropdown list, select the Dataverse schema part (starts with urn:microsoft-crm/document-template/...).
• Expand the primary entity tree to reveal all attributes and relationship nodes.

+-----------------------------------------------------------------------------+
|                        XML MAPPING PANE HIERARCHY                           |
|                                                                             |
|  Custom XML Part: urn:microsoft-crm/document-template/quote/1084/           |
|  └── quote                                                                  |
|      ├── name (Primary Column)                                              |
|      ├── quotenumber                                                        |
|      ├── totalamount                                                        |
|      ├── quote_customer_accounts (N:1 Lookup to Account)                    |
|      │   ├── name (Account Name)                                            |
|      │   ├── telephone1                                                     |
|      │   └── address1_line1                                                 |
|      └── quote_details (1:N Child Products)                                 |
|          ├── productname                                                    |
|          ├── quantity                                                       |
|          ├── priceperunit                                                   |
|          └── extendedamount                                                 |
+-----------------------------------------------------------------------------+

4. Map Single Fields and Parent Lookups (N:1)

• Position the cursor in the Word document where the text should appear.
• In the XML Mapping Pane, locate the desired field (e.g., quotenumber or quote_customer_accounts -> name).
• Right-click the field -> select Insert Content Control -> Plain Text (or Rich Text if mapping multi-line formatted columns).

5. Map Repeating Child Line Items (1:N Relationships)

To generate a dynamic table of products, invoice lines, or contacts:

1. Insert a standard Word Table containing a header row and a single data row.
2. In the document, highlight/select the entire data row (the <tr> row).
3. In the XML Mapping Pane, locate the 1:N relationship entity node (e.g., quote_details).
4. Right-click the quote_details entity node -> select Insert Content Control -> Repeating.
5. Click inside each individual cell of the repeating row, right-click the specific child field (e.g., productname, quantity, priceperunit), and select Insert Content Control -> Plain Text.

• Result: When generated, Word dynamically duplicates the table row for every related child product attached to the Quote.

[!IMPORTANT] Repeating Content Control Placement: For 1:N repeating tables, the Repeating content control must be applied to the entire table row, not to individual cells. Inside that repeating row, individual column cells are mapped to child attributes using Plain Text controls.

---

3. Authoring Microsoft Excel Document Templates

Excel templates provide high-impact analytical reporting, calculation models, and summary dashboards built on top of Dataverse entity views.

+-----------------------------------------------------------------------------+
|                        EXCEL TEMPLATE ARCHITECTURE                          |
|                                                                             |
|  [STATIC WORKSHEET / TEMPLATE]          [DYNAMIC WORKSHEET / TEMPLATE]      |
|  - Point-in-time snapshot of data       - Office Data Connection (ODC) query|
|  - Does NOT refresh from database       - Live refresh via 'Data -> Refresh'|
|  - Safe for external distribution       - Enforces Dataverse Security Roles |
|  - Fully editable without auth          - Requires Dataverse authentication |
|                                         - Requires Excel Desktop client     |
+-----------------------------------------------------------------------------+

Static vs. Dynamic Excel Templates

• Static Excel Template:
  • Contains a snapshot of data extracted at the moment the template was downloaded.
  • The data table does not contain connection strings or queries to Dataverse.
  • Best for fixed reporting templates, formulas, and visual calculators where external data connections are prohibited.
• Dynamic Excel Template (Dynamic Worksheet / Dynamic PivotTable):
  • Embeds an Office Data Connection (ODC) that links directly back to the Dataverse environment.
  • When a user opens the workbook in the Microsoft Excel Desktop application and clicks Data -> Refresh All, Excel connects to Dataverse and pulls real-time record updates.
  • Dataverse Security Trimming: The dynamic connection authenticates the user running the refresh. If User A has access to 50 Accounts and User B has access to 500 Accounts, running Refresh All on the identical Excel template displays only 50 rows for User A and 500 rows for User B.

Creating and Styling an Excel Template

1. In Dataverse, open the entity view (e.g., Active Opportunities).
2. On the command bar, click Excel Templates -> Download Template.
3. Open the downloaded .xlsx workbook in Excel.
4. Design Pivot Tables, Pivot Charts, summary KPI scorecards, conditional formatting, and Excel formulas (SUM, AVERAGE, XLOOKUP) referencing the underlying Dataverse data table.
5. Save the .xlsx file.
6. Upload the file back to Dataverse as a System Template (for organization-wide deployment) or Personal Template.

[!TIP] Dynamic Refresh Boundary: Dynamic Excel workbooks cannot execute live data refreshes inside Excel Online (web browser) because Excel Online does not support external ODC data connection refreshes. Users must open the file in the Excel Desktop Application to refresh dynamic Dataverse connections.

---

4. Document Generation & Power Automate Integration

Dataverse templates can be consumed interactively by users or fully automated through cloud flows.

+-----------------------------------------------------------------------------+
|                     DOCUMENT GENERATION CONSUMPTION MODES                   |
|                                                                             |
|  INTERACTIVE (MANUAL):                                                      |
|  Model-Driven App Form / Grid -> Click 'Word Templates' -> Select Template  |
|  -> Browser downloads populated .docx file immediately.                     |
|                                                                             |
|  AUTOMATED (POWER AUTOMATE):                                                |
|  Trigger: When a Quote is marked 'Approved'                                 |
|  -> Action 1: 'Perform an unbound action' (Action: `SetWordTemplate`)       |
|  -> Action 2: Convert Word Document to PDF (OneDrive / SharePoint)          |
|  -> Action 3: Send email with PDF attachment to customer via Exchange       |
+-----------------------------------------------------------------------------+

Interactive Document Generation

• Users navigate to a record form (e.g., an Opportunity or Quote), click Word Templates on the command bar, and select the template.
• Dataverse retrieves the record data, resolves 1:N and N:1 relationships, merges values into the content controls, and downloads the finalized .docx document to the user's browser.

Automated Generation via Power Automate

In enterprise ALM and automated sales pipelines, document generation is frequently automated without human intervention:

• SetWordTemplate Action: Power Automate flows utilize the Dataverse connector's Perform an unbound action step with the action name SetWordTemplate, passing the Target record reference and SelectedTemplateId.
• Word Online (Business) Connector: Alternatively, flows use the Populate a Microsoft Word template action to inject dynamic values into content controls, followed by Convert Word Document to PDF and Send an email actions to deliver customer-facing proposals instantly.

---

5. Summary Comparison of Interoperability Technologies