In this series of blogs I will provide a simple overview of how to connect Microsoft Excel to a Microsoft Dynamics CRM Online Organisation and how to then use some of the Power BI tools in Microsoft Excel, such as Power Query, Power View, PowerPivot and Power Map, to create nice looking, interactive and informative reports.
The following screenshots show examples of the types of reports and visualisations that can be created.
This first screenshot shows an example of a Power View Report in Microsoft Excel. This report displays several tables and charts which work together interactively to provide an analysis of Opportunities in Microsoft Dynamics CRM Online.
Interactive features of this report include the following:• Clicking a column heading on a table sorts the table by the selected column.
This next screenshot shows an example of a PowerPivot Report in Microsoft Excel. PowerPivot Reports may display the following table and chart combinations:
In a Power View Report, the table and the chart(s) are related to each other by a single filter control. The filter control, table and chart in the Power View Report at interactive.
This final screenshot shows an example of a Power Map Report in Microsoft Excel. This particular example shows three layers. Layer 1 shows Opportunities Estimated Revenue x Actual Revenue as a Bubbles, Layer 2 shows Revenue by Account as Columns and Layer 3 shows No of Employees as Heat Maps. Each layer is mapped to the City of the corresponding Account. The map is interactive and can be zoomed, rotated and time-lapsed.
In my next blog I will describe the requirements for Microsoft Excel the add-ins that are required and how to connect Microsoft Excel to a Microsoft Dynamics CRM Online Organisation.