An Overview on how to Create Advanced Microsoft Dynamics CRM Online Reports using Microsoft Excel, Power Query and OData endpoint

By Ben Ward, Applications Analyst, MCTS, MCP, MS

Due to the cloud based nature of Microsoft Dynamics CRM Online, end users are not granted access to the backend SQL database (for a good reason which I will not go into now. Trust me, it’s long and pretty boring). This restriction to the SQL database makes pulling extensive reports a little more difficult than first thought. However, the good folks at Microsoft have provided an OData endpoint to be used with other products to allow extensive reports to be created. An OData endpoint is basically a URL for users to reference to access their data without permissions to manipulate the data with the SQL database. This ensures the data remains untouched inside the SQL database but users can download and access the data for other uses such as reporting.

Here is the high-level overview of how OData endpoints can be used:

The following example can be used to access your Microsoft Dynamics CRM Online OData endpoint:
[Your Organization Root URL]/XRMServices/2011/OrganizationData.svc

Power Query is highly recommended to access Microsoft Dynamics CRM Online OData endpoints. More information on
Power Query can be found here:
https://support.office.com/en-us/article/Introduction-to-Microsoft-Power-Query-for-Excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605

Once the OData endpoint has been referenced in Power Query, select the entities where the desire data resides and start editing the tables to fit your business needs. If you are familiar with SQL databases, Power Query editor will be very easy to use.
Note: Microsoft Visual Studio can also be used to access OData endpoints and create tables, which will be covered in another post.

About the author:

Ben has worked at Managed Solution for over two years and is currently working on CRM customization and administration, Microsoft SharePoint integration and customization as well as Business Intelligence analytics including SQL reporting. Ben is a Microsoft Certified Technology Specialist, Microsoft Certified Professional, Microsoft Specialist and has six Dynamic CRM certifications.

Other Blog Posts by Ben Ward:

• An Overview on how to Create Advanced Microsoft Dynamics CRM Online Reports using Microsoft Excel, Power Query and OData endpoint

• How to add a Lead to Dynamics CRM

• What is a CRM? Part 2

• What is a CRM? Part 1

• Power BI to gather market insights