Dynamics CRM: Export to Excel for further analysis

One of my most popular blog post has been about dashboards.  I thought I would look into this in more detail and look at different ways you can use the data held within CRM.

This time however, instead of using the dashboards in CRM (which I do love), I am going to do the reporting in Excel.

First of all I am going to show you how to get the data out of CRM (whether this is online or on-premise).  Within every screen there is an “Export to Excel” option which is highlighted below in the Opportunities screen.  Selecting this gives you 3 options.

You can either take a snapshot of the data right now or get dynamic data (i.e. a live connection from Excel to the CRM database).  For reports that are repeated monthly, dynamic is your best bet as you when the Excel report is opened the data is live data from the CRM database; this saves you having to build the same report each time you need it.

You can choose the columns you want to bring to Excel.  Choose as many as you need depending on the reports you want to drill in to.  This is a standard CRM site so the fields are Out of the box.  I have chosen to bring the following fields:

• Owner (i.e. Sales Person)
• Est. revenue
• Est. close date
• Topic of Opportunity
• Potential Customer
• Probability

This now saves an XML on your local computer.  Open this file and you will be told that you have to enable the content.

Click Enable and go to the Data tab and either Refresh from CRM or Refresh All.  CRM Client for Outlook has to be installed for this to work.

You now have your CRM opportunities in Excel for analysis.  This is where real Excel pros can have some fun.  I am going to leave this data where it is as it is connected to the CRM database.  I am going to create a new sheet in this workbook and call it Dashboard.  Here I am going to build a pivot table and use charts and “slicers”.

I am not going to go through how to build a pivot table as many of you will have done this multiple times.  However to explain my dashboard, I have made the row hierarchy Owner > Potential Customer > Topic and values the Estimated Revenue.

I now have a pivot table to work from.  Using Excel 2010 features I can now create “slicers” to drill down and view the data I want.  I created 4 slicers, Probability, Est Close Date, Sales Person and Est Revenue.

I have also created a simple chart to visually show the data in the pivot table.

I can drill down on the data and use the slicers to filter.  Below I have just picked Richard Dunlop as the Sales person and drilled down to view all the Potential customers and the Topics of the opportunities.  As you can see the chart changes to represent the data in the pivot table.

Below I have shown all opportunities that are 80%-95% likely to close.  As you can see Glenn’s 3 opportunities are a lot better than the small opportunity Richard has.

If I use the slicers to show the opportunities that are estimated to close this month (June) I can see that only Richard has opportunities and 2 of which are high value opportunities; I now know to focus on these to hit my targets.

This is another simple way to run reports and analyse the data from your CRM.  This data can be shared within the organisation as well as on SharePoint.  More sophisticated dashboards can be built with multiple data sources to provide key information for business decisions.

I hope this shows you just how simple it is to build reports using Microsoft Dynamics CRM 2011/2013 and Excel 2010/2013.  If you have any questions just leave a comment below.

 

by Richard Dunlop