Related Entity Data in Excel Spreadsheet


The grids in Microsoft CRM 3.0 don’t have related entity information.  You can’t for example look at a list of opportunities and see the account rating or account territory of the opportunity’s potential customer on the same row. 


 


With the export to dynamic Excel feature in 3.0, you can actually edit the spreadsheet query in Excel and bring in information from related entities.


 


Here is an example.  Say you want to analyze your open opportunities by account territory.  Navigate to Sales > Opportunities > Open Opportunities in Microsoft CRM 3.0 and click on export to Excel button on the grid toolbar.  That will bring up the following dialog, choose Dynamic Worksheet.


 



Open the Excel spreadsheet (choose “Enable Automatic Refresh”). Now right click on the data and choose Edit Query:





 


Click OK to this dialog and then OK again to the dialog afterwards.


 




 


That should bring you to the Microsoft Query editor.  Now click on the SQL button on the toolbar to bring up the SQL statement editor.


 




Notice that you can modify the SQL statement to bring in more data into the spreadsheet.  So for example, to bring in the Account Territory information of the Potential Customer, edit the SQL to be the following:


 


SELECT         opportunity.name as ‘name’,


opportunity.estimatedvalue as ‘estimatedvalue’, opportunity.estimatedclosedate as ‘estimatedclosedate’,


opportunity.customeridname as ‘customeridname’,


opportunity.opportunityid as ‘opportunityid’,


account.territoryidname as ‘territory’


FROM             FilteredOpportunity as opportunity JOIN FilteredAccount as account


ON opportunity.accountid = account.accountid


WHERE          (opportunity.statecode = 0)


ORDER BY   opportunity.name asc


 


Click OK and OK again to the dialog that pops up.  You should now see the added account territory column (territoryidname) in the query editor:



 



Now click on the Return Data button which will return you to your spreadsheet.  Note that you will also get the opportunityid column appearing which isn’t there before.  Feel free to delete this column and label the territoryidname column in the spreadsheet.  The result should look something like this:


 




 


Now you’re ready to analyze your opportunities by the territory of the potential customer.  For example, the following chart created from the data in the spreadsheet shows how much estimated revenue is in each of the sales territories:


 




 


(Here I created a pivot chart on top of the data in the spreadsheet list).


Wan Li Zhu

Comments (7)

  1. frank@workopia.com says:

    This is an awesome how to article!  Very practical for end users to know.

    Frank

    http://www.workopia.com

  2. On the first of May this blog posted its first real entry. This blog was designed to be a place where…

  3. David says:

    Can I use the "get external data" function within msft Excel to get data from msft CRM?

  4. Charles Eliot says:

    Yes, you can use the Excel "Get External Data" function to pull data directly out of Dynamics CRM. You’ll need to build an ODBC DSN. The procedure is described in one of the first CRM Team Blog postings, "Why Filtered Views Are Cool" (http://blogs.msdn.com/crm/archive/2006/05/01/587823.aspx)

  5. SusieN says:

    This is a really helpful article.  Is it possible to get picklist text rather than values in this way please?

  6. JB says:

    Great how-to, but it does not work with Excel 2007.