PowerPivot & Microsoft Dynamics CRM 2011 – Part 2

In a previous post I have described some general capabilities of PowerPivot in conjunction with Microsoft Dynamics CRM 2011.

Before I can demonstrate any of these capabilities, let’s see how to extract data from Microsoft Dynamics CRM 2011 into PowerPivot in 10 simple steps:

  1. Install Microsoft Office 2010
  2. Download and install PowerPivot for Excel 2010
  3. Open Microsoft Dynamics CRM 2011 Web client (On-premises only), go to Settings –> Customizations –> Developer Resources. Find the OrganizationData end point hyperlink and copy it

  4. Open new Excel sheet
  5. Click the newly added PowerPivot tab and then the PowerPivot button which will open the PowerPivot window



  6. In the PowerPivot window, click the ‘From Data Feeds’ button in the ‘Get External Sources’ group
  7. Paste the copied URL into the ‘Data Feed Url’ textbox. Give the connection a meaningful name. Click ‘Test Connection’ and then ‘Next’ if the test succeeds


  8. The next window displays all Microsoft Dynamics CRM 2011 business entities exposed by the OrganizationData end point. You can select required tables, set a friendly name for them and also set the required columns from each table by clicking the ‘Preview & Filter’ button. When done, click he ‘Finish’ button.
    For the my next examples, I have selected 3 tables: AccountSet, ContactSet and IncidentSet.


  9. Next, the PowerPivot actually extracts data and displays a report. Click the ‘Close’ button


  10. The PowerPivot window will display the extracted data tables, each in a separate tab


You are done! The data is now contained in the Excel file, and after you save it, it will remain there. If you click the ‘Refresh’ button in the ‘Get External Data’ group, the PowerPivot will refresh the data from Microsoft Dynamics CRM 2011 OrganizationData end point.

In the next post, I’ll demonstrate some useful PowerPivot tools and how they help solve various MSCRM2011 problems.



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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s