By default the setting within Microsoft Excel 2010 causes the export to dynamic pivot table functionality within Microsoft Dynamics CRM to fail.
This is caused by Microsoft Excel’s enhanced protection feature. When you select the ‘export to Excel’ option and choose Dynamic Pivot Table it create a .xls file in the following location C:\Users\>USERPROFILE<\AppData\Local\Microsoft\Windows\Temporary Internet Files, this location is considered unsafe by Microsoft Excel ‘s enhanced protection feature.
To resolve this issue please follow steps below.
1. Open up Microsoft Excel 2010
2. Select File and point to Options
3. Select Trust Centre from the left hand side
4. Click Trusted Centre Settings
5. Select Protected View
6. Un-tick the second option, Enable Protected View for files located in potentially unsafe locations
7. select Ok twice