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