The ability to dynamically export data from MSCRM to Excel is a very powerful tool as this allows Users of the CRM Solution to build reports and analyse data without development or the more deep SQL Technical Skills required for custom MSCRM Reports via SQL Reporting Services.
However the ability to export from MSCRM to Excel can be limited by where we run the export from in MSCRM – such that an Advanced Find relating to the Account entity will return fields about the Account entity, a View relating to the Order entity will just return fields about the Order entity and so on. This can be problem for analysing related entities or reporting on entities that do not appear in the MSCRM Views or Advanced Find.
Usually most key entities will be accessible in the MSCRM Advanced Find and Views, however there is one big exception to this – the detail lines on a particular Opportunity/Quote/Order or Invoice. We cannot find say the Order Lines for the Order Detail entity via the Advanced Find, which can make exporting this to Excel difficult.
However if we want to perform analysis on Total Order Quantities or ‘Sweet Spot’ Analysis on Product Types – we would need to pull this list of ‘Order Detail’ records into Excel.
Fortunately this can be done by looking at how Excel is taking the data from MSCRM and editing this to take data from the Order Detail entity manually.
This can be done via the following steps:
1. Export a standard list of Orders from MSCRM as a Dynamic Worksheet into Excel.
2. Click the ‘Connections’ button in Excel to bring up the Data Connection that Excel is using to bring the Order Records from MSCRM.
From here, click the ‘Properties’ button to now view the specific details of this Connection.
3. This Properties window now shows the Technical Detail of how Excel is reading records back from CRM – the specific area which concerns us is the ‘Command Text’ area. This textbox contains the SQL that Excel is running against the MSCRM Database to retrieve the Order Records. The current SQL shown is the SQL Statement that MSCRM has built for us by exporting the Orders to Excel as a Dynamic Worksheet, however we can change this to a different SQL Statement to bring a different set of records.
In this example, we could instead bring back the list of Order Products as opposed to Orders by pasting the following SQL into the ‘Command Text’ area:
SELECT SOD.productidname AS [Order Line Product], SOD.extendedamount AS [Order Line Amount], SOD.uomidname AS [Order Line Unit of Sale], SOD.quantity AS [Order Line Qty], SO.name AS [Order Name], SO.pricelevelidname AS [Price List], SO.transactioncurrencyidname AS [Currency] FROM FilteredSalesOrderDetail SOD INNER JOIN FilteredSalesOrder SO ON SO.salesorderid = SOD.salesorderid
Clicking OK here will then affect the change in the Excel Query.
We can now jump back to the Excel Spreadsheet and click the ‘Refresh All’ button in Excel to refresh the contents of our Spreadsheet based on the revised query:
4. The key here is that we can now run standard Excel Functionality against our MSCRM data which is been refreshed whenever records change in MSCRM by use of the Refresh All button in Excel.
So we can use Excel Filter Command in the Data area of the Ribbon to give us quick drop-downs to analyse the data, and similarly add Charts, Reports or Pivot Tables into the Spreadsheet to further analyse the data:
This combined MSCRM Query and Excel functionality gives us a simple powerful reporting platform that can be used to analyse data in MSCRM without requiring Training and Experience to use Visual Studio and SQL Reporting Services.
This approach for changing the Command Text of the Excel Query does still require SQL Knowledge and a experience of the ‘Database’ names behind the CRM Fields and Entities – this can be fiddly, so I would always recommend writing these queries in the SQL Management Console beforehand to allow for testing before copying into Excel.
Essentially this is using the standard Excel functionality to access data from a SQL Database which can be taken much further to produce multiple queries and more detailed reporting, however this can also be used for a simple reporting connection to MSCRM based from the existing Export to Dynamic Workshop functionality.
This functionality will be improved further through the use of Office 2010’s Power Pivot functionality working alongside MSCRM.