Custom MSCRM Reporting in Excel

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.

Exported Orders from MSCRM into Excel

Spreadsheet containing the list of exported MSCRM Orders in 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.

The Connections list that Excel is using to read data from MSCRM

The Connections list that Excel is using to read data 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
Changing the Excel Query's Command Text to bring back the Order Product records into the Excel Spreadsheet

Changing the Excel Query's Command Text to bring back the Order Product records into the Excel Spreadsheet

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:

Excel showing the results of the revised query to look at the Order Product entity

Excel showing the results of the revised query to look at the Order Product entity

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:

Excel showing the Order Detail records from MSCRM auto-filtered to only show a particular Product Type with Excel Formulae to show totaled Amounts and Quantity sold

Excel showing the Order Detail records from MSCRM auto-filtered to only show a particular Product Type with Excel Formulae to show totaled Amounts and Quantity sold

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.

Writing the SQL Query in SQL Server 2005's Management Studio

Writing the SQL Query in SQL Server 2005's Management Studio

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.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Custom MSCRM Reporting in Excel

  1. Pingback: Custom MSCRM Reporting in Excel, Micro trend software

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s