Update View/Fields in Dynamics CRM 2016 Excel Templates

(4) Share

Report

Posted on by Community Member

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!

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (2)

Community Member on at

Like (1)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

I could use an answer to this as well!

Was this reply helpful? Yes No
Suggested answer

H on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

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.

Henri

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

Hi Henri,

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?

Was this reply helpful? Yes No
H on at

Like (1)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

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.

Was this reply helpful? Yes No
rpa_senthil on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

Hi Heidster, did you get any update from Microsoft on this, I am facing exactly similar problem

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

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.

Was this reply helpful? Yes No
rpa_senthil on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

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.

Was this reply helpful? Yes No
Verified answer

Mike Wan 190 on at

Like (3)

Report
RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

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.

Include the new field which you want to add to a brand new excel template and download it. (don't need to care about if the view is the same as before, just make sure the needed column is included)

Also download the template you want to update.

Open these two files side by side, and then copy the new column you need and "insert" it into the old version of template. (The key point is how to insert it. Make sure first of all, insert a blank column in the middle of old version of template, and then copy the new column and paste it into the column you inserted just now)

"Refresh Data" from the pivot table or pivot chart. Observe if this newly pasted column can be found from the right hand side field list.

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.

Was this reply helpful? Yes No