Dynamics CRM does a really good job of hosting and tracking a company’s critical business data, and the PowerBI technology stack does a great job of modeling and displaying business data to end users. While Dynamics CRM does a decent job of providing dashboards to surface this business data, I thought “Wouldn’t it be great if CRM users had native access to PowerView reports while using Dynamics CRM”? Well, it turns out that with a little configuration you can provide these types of reports right in Dynamics CRM! Let me explain the process I took to do this.
First off, you need to use the PowerBI stack to extract data from a source (Dynamics CRM, for example), manipulate it in a PowerPivot (or SQL Server Analysis Services) data model and create a PowerView report. I did this for the Top 100 Global Companies and created a PowerMap report hosted in a SharePoint Online instance, as shown in Figure 1. Please refer to this post for further details on creating PowerView reports from LOB data sources.
Figure 1: PowerMap Report in SharePoint Online
Once this is setup, you can then create a system dashboard in Dynamics CRM to host the PowerView report (Figure 2). Please note that this won’t work with Personal Dashboards, due to the fact that you need to configure the IFrame without restricting cross-domain scripting.
Figure 2: Creating a System Dashboard in Dynamics CRM
You will use an IFrame on the dashboard to reference the PowerView report in SharePoint, so you can open the report in a separate window and copy the URL into the IFrame properties, under the URL field. You need to make sure the Restrict Cross-Domain Scripting checkbox is cleared, and should also verify that the URL you copy has the &action=embedview in the querystring (so it renders properly within the page).
Figure 3: Creating an IFrame in Dynamics CRM
Once you configure this and publish to Dynamics CRM, you will then have a fully-functioning dashboard that displays PowerView reports! One final note to mention: authentication is an issue here, as the CRM user should also have a login to the SharePoint site that the PowerView report is hosted in, otherwise you will get a nasty error when displaying the dashboard (Figure 5).
Figure 4: PowerMap Report in Dynamics CRM Dashboard
Figure 5: Authentication Error with PowerView Reports in Dynamics CRM
I hope this blog helps others to quickly get these awesome reports working in Dynamics CRM. Enjoy!