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

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

    image

    image

  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

    image

  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.

    image

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

    image

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

    image

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.

Advertisements

2 thoughts on “PowerPivot & Microsoft Dynamics CRM 2011 – Part 2

  1. Pingback: Microsoft Dynamics CRM Data Provider for Excel Power Query | YanivRDT

  2. Pingback: Microsoft Dynamics CRM Data Provider for Excel Power Query - Microsoft Dynamics CRM Community

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