This article outlines how to create a simple sales report template for an account.
In this step-by-step description, we will create a template that gives an overview of an account’s related sales entities. This template will make use of the following features:
This guide is based on a sample template. To view the finished sample template, please see the attached document at the bottom of this article.
For a video version of this guide, please see the video below:
Step 1: Setting the base entity
When creating a template, it is always important to set your base entity first. This is because the fields displayed in the Template Designer will be determined by this base entity, and once a field has been inserted into a template, the base entity can no longer be changed. In this case, our base entity is Account, so we will set the Type accordingly.
Step 2: Creating the document title with Advanced String
To create the title for our document, we can use the Advanced String feature. You can do this by going to the Insert Field menu in the Template Designer and selecting Insert Computed Item->Advanced String.
In the window that appears, you can enter a value that will dynamically appear when the document is merged. For our template, we will set our value to the following:
This results in our Advanced String field looking like so:
You can also set the document’s dynamic name by following the steps here.
Step 3: Creating our Overview table
For our Overview table, we want to list the total number of Opportunities, Quotes, and Invoices. Additionally, we want to list the total revenues for each entity. To do this, we will take advantage of the relationship aggregation feature within the relationship designer.
First, we will create a simple 3×4 table using Word’s own table creator. We will populate it with the names of our entities, the table column names, and an Advanced String field that will give us the current year after merging.
Next, we need to open the Relationship Designer. Under the relation dropdown, select “activate aggregation.” This will enable the aggregation feature, which can be used to give us the aggregate amount of a particular attribute in our environment. In our case, since we need the total number of records and total revenue for each entity, we can set the “Attribute” and “Aggregate/Group by” columns to the following values:
Quote aggregated by Count will give us the total number of quotes in our environment. Total amount aggregated by Sum will give us the total revenue generated from all quotes in our environment.
This information should be set for each entity, except for Opportunity. Instead of Total amount, we aggregate it by Est. revenue instead.
Once the relations have been established, we can insert our newly created fields into the table.
IMPORTANT: When inserting the fields into the table, select No if you are prompted to insert the field as a new table. In this case, we just want to insert them as fields.
Once all the fields have been inserted, your table should look something like this:
Step 4: Creating our detailed tables
Our last step is to add a more detailed table per entity. In our case, we want to include four fields from each entity into their respective table.
This can be done by establishing another relation with each entity in the Relationship Designer. However, this time, we will leave the activate aggregation unchecked, and just select the fields we need from the list.
Once the relationships have been setup, we can create our tables.
During the table setup process, we will create our table with the following settings:
After the table is created, we can then populate it with our fields. We’ll also add our column names.
We can do the same with our other tables as well. After adding them all, this is what our “Details” section looks like:
Optional step: Adding a Table of Contents
If you want to organize this template, a Table of Contents (or TOC) can be helpful. The TOC can be added via Word under the References tab.
To add your tables to the TOC, you can highlight the first column header of each table and give it a Heading style.
Final step: The merged document
When your template is finished, save it and merge it with your data. Once merged, your generated document should contain the data from your environment.
That’s it! We appreciate your feedback! Please share your thoughts by sending an email to support@mscrm-addons.com.