Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2019 release wave 2 Discover the latest updates and new features to Dynamics 365 planned through March 2020
Release overview guides and videos Release Plan | Preview 2020 Release Wave 1 Timeline
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
I created a new Excel Template based upon information in my current CRM environment, uploaded it, and tested it to see if it properly updates after syncing with CRM.. which it did. I had to create and add two new fields to the form that also needed to be reported on. I added the fields to the form AND to the view in which I created the Excel Template from. After exporting the template again and synching with CRM, the new fields did not appear in the table that my pivot tables/pivot charts are based on and I can't find a way to have those fields update. So l then I attempted to create a brand new excel template and it DID show the newly added fields. However, I still need the information created on the old template (to avoid having to redo 20+ pivottables). So then I tried to copy/paste the old pivot tables/charts onto the new template and am now unable to even Refresh with CRM or anything else because the data source cannot be found.
ANY SUGGESTIONS??? ANY HELP WOULD BE HIGHLY APPRECIATED!
I could use an answer to this as well!
It is possible, and without coding, but not necessarily straightforward. We have CRM2011 and Excel 2016. So here is what we do when we want to add some functionality to an existing dynamic Excel template. There may be better solutions out there but we made this work.
Essentially, we are recreating the query with the updated columns and filters we need and then insert that query text into the existing spreadsheet:
1. use the original CRM View that was used to create the Excel template, and modify it by adding the filters and/or columns you want.
2. Create a new, separate dynamic excel template, verify it is what you need, and temporarily save it as a new XLSX file on your desktop.
3. In the new XLSX template file, (in the ribbon) click on DATA -> CONNECTIONS -> PROPERTIES, then click on the DEFINITION tab. You will see two boxes. The text of the COMMAND TEXT box is your new script file that you want to use in the existing spreadsheet.
4. Copy and paste the text/query from the temporary spreadsheet to the existing spreadsheet. Of course you have to make sure you copy the new query text into the correct data connection. If you have multiple data connections you may have to determine which specific data connection text file you want to update.5. After pasting the text and saving it, hit REFRESH all. Your new spreadsheet should now have the additional columns, and based on whatever filter changes you made.
NOTE: you need to test this out first before using this on a production file as the lay-out of the data source for the pivot table(s) will likely have changed. But that is fairly simple to do.
We use the above process successfully if the templates and pivot tables are too cumbersome to re-build from scratch.
Hope this helps.
Thanks so much for the answer-- I was starting to think nobody has seen my post! :) The environment that I am using is CRM Online 2016 and I'm working with the "Excel Templates" feature which is new to 2016. See screenshot below.
Rather than using the "Export to Excel" which allows you to dynamically export records and create a one-time report/document, this new feature allows you to create a template (Excel or Word) based upon the current information and keep using it with updated data (similar to what an SSRS report would do). I would definitely be willing to try your advice above for these templates, however since there is no "live connection" things are configured a little differently for the Excel Templates feature. I believe they may be based upon the view you pull it from each time (?), but am unsure why they wouldn't allow new columns/fields to be added in and updated as well. Any other suggestions?
Though the "Excel Templates" sound similar to the Dynamic Excel spreadsheet export feature in CRM 2011, I am not familiar with the 2016 Excel Templates. But just in case... the "Export to Excel" has a live connection to CRM and is not just for one-time reports; they can be used, as we do, to create real-time reports with live feeds from CRM into the Pivot Tables. So whenever you open the spreadsheet they automatically update.
I have opened a case with Microsoft and they are still looking into how to do/fix this. I'll post an answer when it's confirmed and tested.
Hi Heidster, did you get any update from Microsoft on this, I am facing exactly similar problem
I have not yet received an answer from Microsoft of this issue, unfortunately. I have found a "workaround" for right now until this is fixed/resolved. However, depending on how many pivot tables and charts you have within your Excel Template, this could be VERY tedious (if you have over 30 per template like me).
After adding the new field(s) to the view you wish to export, you must also create a brand NEW template from that "new" view. In order to not have to re-create each pivot table and chart, you can copy/paste everything from your old Excel template into your new one. Upon doing so, you'll notice the pivot charts turn into just regular charts.
First, you must select every single pivot table one-by-one and go to the Analyze tab > Change Data Source > Type in Table1. After doing that for all pivot tables, select each chart one-by-one and go to the Design tab > Select Data > Select the pivot table data so that the entire table is selected.
Now, your pivot tables and pivot charts should be in alignment and pulling from the new data within your template and will be able to be successfully updated upon refreshing from CRM.
I'll eventually be writing a more detailed article with screenshots about this issue/bug and post it on here. Until then, I'll be continually looking for a better resolution and hopefully hearing back from MS Support.
Hi, I tried your steps, but as soon I did the "select every single pivot table one-by-one and go to the Analyze tab > Change Data Source > Type in Table1", my Pivot table got blank and the Charts too. if possible, could you share the step by step screenshot. I just have 4 pivots, but have lot of slicers with colorful formatting, it would take hours or even days to recreate them. Thanks in advance.
That's yet another issue I've found. Your tables and charts will blank out upon opening from CRM if you don't:
A) Create a brand new template before you copy/paste old info into the new one and edit the Data Source
B) Use the EXACT same view (just updated) to export/download that new template
I will try to create a document with screenshots on all of the workarounds when I have time and will post the link to it here.
I met similar issue today and when I search from the web, I found out your post.I was trying to add a new field to the uploaded excel template but the pivot chart can't identify it. After played for several hours, I finally got a working way.
if you can, update the pivot chart with it and then upload this updated template back to the CRM with a new name. It should be also working fine within Excel Online, at least it is from my side.
I was struggling with the same issue using Word Templates where I needed to include newly added custom fields, and found a similar solution that worked for me. Simply generate the new template containing the new fields, Insert the field that you want to add in the new template. Then copy the field from the new template to the old template.
Sadly, the XML Schema does not get updated with the new field to make it easier to edit in the future, but it does beat copying all of your document and formatting from the old template to the new template.
I have followed your steps (or I guess so) and even when I added the columns with the 2 new fields (instead of one, I had to add two), I see them available in the Pivot Table but they are not populated with data when downloading the new spreadsheet.
Can you add a couple of screenshots showing how you create the new column, copy and paste the new column?
Thank you very much for your time.
I ran into a similar issue the other day and I figured out a solution.
It turns out that Excel Templates have a sheet named "hiddenDataSheet" that contains the mapping from the CRM view columns to the Excel table column names. This sheet is "very hidden" so you need to use "View Code" to unhide it, see the following for details:
If you make the "hiddenDataSheet" sheet visible, you can find the column mapping in cell A1. The format of the mapping should be pretty self-explanatory, but essentially you would add something like this, escaping spaces as necessary:
&<CRM view column name>=<excel column name>
Once you've added your new columns to this mapping, you should be able to re-hide the "hiddenDataSheet" sheet and upload your new template.
Hope this helps!
Super helpful, Mike Wan!
Been looking for this solution for a bit.
I successfully used your solution steps together with "hiddenDataSheet" update as the fifth step, followed by a save of the new template locally and its upload to Dynamics org.
Hi Dave (& other following this thread) ... can't make you suggestion work - I'm in Dynamics 365 Online and newest version of Excel (from Office365) ... do you know if this tip works today?
I'm on a customer case and would really like to modify existing Dynamics 365 Excel template the easiest way :) In this case I need to add an extra column from CRM into the existing template.
Business Applications communities