In Dynamics CRM 2011, sometimes we need to export data to an Excel file as Dynamic Pivot Table.

After you export data to Excel 2010 as Dynamic Pivot Table you may have not been able to choose “Data Filters” in the Excel Worksheet – option was greyed out.

clip_image001

To work around this:

1.     Export the information from CRM to Dynamic PivotTable Excel file as an XML file.

2.     Open the XML file in Excel.

3.     Export the Connection information by doing the following:

·       Go to the "Data" tab and click on "Properties" in the "Connections" group.

·       On the "Connection Properties" dialog box, switch to the "Definition" tab and click on "Export Connection File...".

clip_image002

·       Save the .ODC file with a suggestive name.

·       Close the "Connection Properties" dialog box.

4.     Close the XML file.

5.     Create a new Excel file.

6.     Create a new PivotTable by doing the following:

·       Switch to the "Insert" tab and click on "PivotTable".

·       On the "Create PivotTable" dialog box, select the "Use an external data source" option and click on "Choose connection...".

·       On the "Existing Connections" dialog box, click on the "Browse for More..." button.

·       Open the .ODC file created in step 3.c.

·       Click on "OK" to finalize the creation of the PivotTable.

clip_image003

·       After above step we will see below view

clip_image004

·       To change the PivotTable view we need to change following PivotTable set.

clip_image005

·       After above step we will see below view

clip_image006

But with correct working Date Filters:

clip_image007

 

Best Regards

Dynamics CRM Team