In this blog, we highlight a lesson learned when we used a Dynamics 365 Customer Engagement online data source connector, called Instance Web API, for Power BI reports.
The ability to quickly generate Excel spreadsheets is an exciting new feature included with Dynamics CRM 2016. The feature allows you to create Excel templates tailored to display dynamic data on demand, and in today's blog, we will show you how easy it is to quickly generate excel templates in CRM! Let's begin!
There are two different paths you can follow to create an Excel template:
Method #1: From the Settings Menu
1. Click on Main > Settings > Templates.
2. Click on Document Templates.
3. On the top left corner, click on + New.
Once a pop-up window titled Create Template from CRM Data opens, choose the following options:
4. Select a template to create: Excel Template.
5. Filter by entity: Activity.
6. Used saved view: My Activities.
7. You can also click on Edit Columns, if you want to add, remove, reorder, or change the properties of the columns to be exported to Excel.
8. Click on the Download File button.
Method #2: From a List of Records (view)
1. Click on Main > Sales > Activities.
2. Make sure the My Activities view is selected.
3. Click on the Menu > Excel Templates > Create Excel Template.
4. Finish by following steps 6 to 10 from Method #1.
Formatting the Excel Template
Open the Excel template. If you didn't change any columns, the Excel file should look like the following:
The number of records you will see in the initial Excel template will depend on the number of activities you own.
Now this is the fun part! At this point, you can customize the spreadsheet as you wish. Feel free to add columns, add pivot tables, charts, format the list of activities, etc.
IMPORTANT: iOS does not update pivot data charts when using the Microsoft Excel app for iOs. The pivot table for the charts may be created by an Administrator or another user with elevated privileges in Dynamics CRM, however, Microsoft's recommendation is to not include confidential information in the pivot tables created for the charts.
Another recommendation is to set pivot chart data to refresh automatically. This will be effective in most scenarios, besides the one mentioned in the previous instructions. This way, every time the file is downloaded and opened, it will display the current data.
1. Right-click each on pivot table and choose PivotTable Options.
2. Go to the Data tab and check Refresh data when opening the file.