Excel templates are a fantastic option for analyzing and presenting Dynamics 365 data in a repeatable manner. Charts, pivot tables, and even data sets enriched with data from other sources are all possible with Excel templates!
Sometimes it might not seem possible to get at the data you need in one template with valid results in your charts and pivot tables. With some forethought and planning, you might be surprised at what you can achieve. In this post, we will look at some hurdles and how to get past them. To learn the fundamentals of Excel templates, check out this blog post.
Scenario #1: Customer Support Manager
As a Customer Support Manager, you want to see the average number of activities by case type and activity type. You also want to see the same averages by the Support Representative assigned to the cases. Is it possible to do both in the same excel template? The answer is yes!
The first thing you need is a view of the data that you want to bring into the template. When you need to get data from multiple related entities, you should start from the entity that has the most records. In this example, we want to get all activities that are associated with cases.
From the Advanced Find, select Activity in the Look For dropdown and new from the View dropdown. Add the activity fields that you need. Then add the Regarding Case fields that you need.
What are the hurdles?
Double-counting related records – If you are retrieving all activities and related case data, it is possible that the same case will appear in your results twice. When you create a pivot chart or pivot table, you need to be able to count the number of unique cases. In your pivot table, this is called 'Distinct Count.' If you use the default options when you create your pivot table, Distinct Count will not be enabled. You need to add the data to data model. Doing this will also enable you to join your data to other data sources that you add to the excel template.
Missing columns included on the original template download – It is important to ensure that you have added all of the fields you need in your excel template before you download it. Adding additional columns after you upload a customized template is not supported in the UI and requires some complicated steps to accomplish.
Related data that's just out of reach – Sometimes you just can't get to the fields you need with a single view. In this case, you might need to look at other customizations to enable access to those fields. Calculated fields, rollup fields, and additional 1:N relationships can often take you past this hurdle.
Scenario #2: Marketing Manager
As a Marketing Manager, you want to be able to measure the effectiveness of campaigns by leads generated and related opportunity win rate. This example is exciting and relatively straightforward.
From the Advanced find, look for opportunity entity. Create a view of the opportunity columns, add the originating lead and any fields you need from the lead record. Then add the campaign and any fields that you need from the campaign.
What are the hurdles?
Related data that's just out of reach – In this scenario, the secret is ensuring the lead to opportunity relationship has the necessary relationship field mappings so that when a lead is qualified, the necessary data is populated on the related opportunity.
Double-counting related records – When you generate the template, columns related to lookup fields like related lead and campaign are hidden. These hidden columns are the key to counting unique leads, campaigns, or any other lookup field.
As you can see in these scenarios, Excel templates give you many options for analyzing and reporting on your CRM data. For more ideas for Excel and Word templates, check out these posts:
PowerSurveyPlus and Excel Templates: A Match Made in Reporting Heaven
Let's Dive Deeper: One Click Document Generation in CRM 2016
Happy Dynamics 365'ing!
