Will it Excel Template? Overcoming Common Obstacles of Excel Templates
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:
Brighton and Sussex University Hospital Trust Presents: Taking Virtual Clinics from Concept to Completion
Before Dynamics 365, PowerObjects' customer Virtual Fracture Clinic, was utilizing tools found in the wider Microsoft stack, with a heavy reliance on Microsoft Excel and Microsoft Outlook for online referrals that were downloaded and filed to Excel daily. They also managed their patient tracking via Excel. Realizing that they needed a more robust and manageable system, VFC decided to go with an online implementation of Dynamics 365, taking virtual clinics from concept to completion. With Dynamics 365, they rolled out to other hospitals as a cloud-based service rather than having other hospitals add to their current user licenses.
One of the biggest solutions VFC implemented was a CRM portal. The portal is projected to manage approximately 100 referrals a week for the first six months. After the imbedding phase, this number is expected to double and VFC will be looking to configure the system to integrate with other hospital software platforms, which will auto-populate patient demographics to result in both time and cost savings.
VFC has seen extensive benefits from their CRM portal, especially on the patient end. Not only that, but with the outcome measures, VFC can continue to do ongoing research in terms of specific injuries and recoveries. Since they have all the data stored in CRM, they can continue their research on how virtual models can manage patients with specific injuries as well as chart demographics in terms of reach of patients, age and their interactions.
VFC wanted a CRM platform to be the model adopted by other hospitals as best practice so that the system could be adopted easily and so that it would be easier for the physiotherapists running the Virtual Fracture Clinic to be able to choose a diagnosis automatically to link the correct protocol to the patients.
As recent winners of the NHS innovation and AHA awards in 2016, VFC will be presenting a conference to demonstrate how to move from concept to completion of a virtual clinic with the support of digital technology. A few of the topics that will be covered are:
Virtual Fracture Clinic's development taking the 'lean' approach
Legal, Financial and Commissioning implications
Diverse use of virtual methodology to support the NHS 5 year forward and STPs
Central standpoint from professional bodies including the CSP and BOA
Using technology to support wide-spread adoption
Provision of a "best practice adoption pack" to support development
Joining Lucy Cassidy of BSUH will be PowerObjects team member Marco Amoedo, speaking alongside Microsoft's Sophie Richardson on using technology to support wide-spread adoption. Examples will mainly be from the Virtual Fracture Clinic model, but examples of where virtual technique can be applied to many outpatient settings supporting a very diverse model of adoption will also be provided. Clinicians from all areas are welcome!
Utilizing Dynamics CRM Document Templates through the Outlook Client
In Microsoft Dynamics CRM 2016, an awesome new feature has been introduced. It gives you the ability to create Microsoft Word and Microsoft Excel templates to easily get data out of CRM and into useful reports and spreadsheets. The best thing about the new feature, CRM Document Templates, is that it eliminates clicks when generating reports. Even if you are an avid CRM for Outlook user, you can still utilize this new feature. Today's blog will help you navigate through the CRM for Outlook client to be able to locate and use CRM Document Templates directly from Outlook.
If you are new to creating document templates, you might want to first check out the It's Never Been This Easy: One Click Document Generation in CRM 2016 blog to learn tips and tricks on creating document templates within CRM. Once you've learned how and created a document template, open Outlook and use the folder structure or navigation option to access the CRM section as shown below:
Figure 1: CRM in Outlook
Once you've navigated to the CRM section, navigate to the entity for which you created the template. In this case, we have created a template related to Opportunities. To navigate to the Opportunity entity, go to Sales > Sales > Opportunities.
Figure 2: Opportunity
When you find the record you want a document template for, highlight it in the list of records. Navigate to the top ribbon in Outlook where you will see a number of items to choose from along with Word and Excel templates. These are both a part of the document templates that can be created within CRM. In this case, we will select Word Templates and then select Word Templates again. You can see that we are able to view all the Word templates that have been created for this entity.
Figure 3: Opportunity Word Templates
When you have selected the correct Word template to run, the file will download and you will be able to view the data from CRM within the template that you have created. In some cases, if your Outlook window is smaller than normal, you will not see the Word or Excel templates in the top ribbon. In this case, you will navigate to the button labeled Data within the top ribbon. This is where you will be able to find the document templates location from Outlook.
Figure 3: Opportunity Word Templates from data button