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

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:
Dynamics-CRM-Ben-Ward-Managed-Solution
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:

Continued Reading

Why Are Realtors Using Surface Devices?

[vc_row][vc_column][vc_column_text] With the competitiveness of the real estate industry, agents […]

LEARN MORE

Managed Solution had a great time at Biocom's You're the Expert, a live show using #comedy to make academic research more accessible & exciting!

"Biocom's You're the Expert Comedy Show was awesome! I love […]

LEARN MORE

Contact us Today!

Chat with an expert about your business’s technology needs.