Microsoft Dynamic CRM allows user to export data to excel and perform further data analysis from there. If you have attempted to export more than 10000 records from Dynamic CRM 4, you will find excel will only show the first 10000 records. At first, you may think there is a limit with Excel. Then you will find out Excel 2003 has a limit of 65535 rows or Excel 2007 has a limit of 1048576 rows. The 10000 record limit is actually set by CRM4. Referring to the SQL database, you will find a column MaxRecordsForExportToExcel in the OrganisationBase table. The value for the column is 10000. Even if you can modify this value and solve the issue immediately, modifing data directly from SQL database is not supported by Microsoft CRM. Here is a supported work around and can be done at a standard CRM user level (assuming you have sufficent previllege over the data).
1) Select the view list you want to export. For example: Active Contacts.
2) Click Excel from the list toolbar and select Dynamic Worksheet option
3) Open the excel workbook and enable Data Connection if required
4) On the excel workbook, right click the data area, select Edit Query
5) If there is a pop-up windows about "The query cannot be edited by the Query Wizard", click ok. Now you will see the Microsoft Query window.
6) From the Microsoft Query toolbar, click View and select SQL. A new pop-up Windows showing the actual SQL statement appears.
7) Remove Top 10000 from the SQL statement and click OK to exit the SQL statement window
8) From the Microsoft Query window toolbar, click File and select Return Data to Microsoft Office Excel
9) Refresh your excel worksheet if auto-refresh is not enabled and you should see all data from the select CRM view now
Really Helpful... Thanks
ReplyDeleteThank's a lot , it works fine.
ReplyDeletePhilippe