Do you want unleash the power of Microsoft Excel to analyze your CRM data? Exporting to a dynamic pivot table can help. Excel provides sophisticated data analytics capabilities through pivot tables. Use them to quickly summarize data across a wide number of entities and fields. Excel 2010 onwards provides additional filtering capabilities using slicers.
Let us step through how to extract data from CRM into a pivot table. In this example, we will focus on sales data. However, Microsoft Dynamics CRM provides out of the box functionality to export data from any view.
Step-by-Step
3. Select the "Dynamic PivotTable" radio button to create an export that will update directly from CRM when you refresh the Excel worksheet. Click "Next >".
4. Select the record fields that you would like to include in the export and then click the "Export" button. CRM will save the export as an XML file.
Tip A – select all fields/columns by clicking the checkbox in the upper left corner next to "Display Name" to maximize the analysis options within the Excel pivot table.
5. Navigate to the file within windows explorer. Open the file using Microsoft Excel.
Tip B – within the Internet Explorer browser, you can navigate to the containing folder by clicking the "Open folder" button.
Tip C – within the Chrome browser, you can navigate to containing folder by clicking the inverted chevron and selecting "Show in folder".
Tip D – top open the file without Excel being open, right click on the file and select "Open with Excel".
Tip E – the pivot table "refresh" button is under the data menu.
Note: We highly recommend that you have the CRM Outlook Client installed and properly configured. While there are workarounds including leveraging OData and ODBC, we do not recommend going down this path.
Congratulations! You now have a refreshable pivot table of your target CRM data. You can manipulate it as you would any other Excel pivot table. Make sure to check out our blog for even more Dynamics CRM tutorials!
Happy CRM'ing!
