Power BI embed to Dynamics 365 form with filtering

There have been several post about this topic before, but this one is the only way I got it to work. Rasmus wrote about this a few weeks ago, and I am using most of his code. Ida was also early out mentioning this possibility, but I just couldn’t get it all to work before now. Big thank you to Jeroen Vos for BI assistance:)

Bear in mind that I had to start from scratch here to make sense of it all. You can fast forward quite a bit if you already have a data model. I believe Microsoft will release a OOTB function sometime this year, but until then we can demo embedded BI in CRM forms like this.

Create a report

Start by opening Power BI dashboard, and creating a new project with “Get Data”

If you don’t know where to find the developer settings, you can copy the url from above, and just replace it with your org and crm* that you have in your country.

For this demo I am only choosing Account and Opportunity.

After loading, add the funnel graph.

Add the field salesstage and estimtedvalue. Both are from Oppty table.

Your report should resemble something like this. Now we have to format quite a bit for this to actually look presentable in Dynamics. Because this solution is highly custom at the moment, we need to make it fit the Dynamics IFrame for it to look decent.

The most important format of them all is the page format to the right. You need to make this one small for the Dynamics Tab to be able to open it in full screen. I made it half of the standard. Eventually you should end up like something below:

Numbers on the right are a picklist, but for the demo I didn’t care to rename them.

Publish to powerbi.com

Remember to copy the URL here

Dynamics 365 form configuration

This process requires a webresource with javascript and a tab with IFrame in Dynamics. Because of the new UI this layout is actually quite nice!

URL can be any website since we are overwriting it anyway. The name of the iframe is used in the javascript. Don’t restrict any cross-frame scripting.
function SetBIFrame(executionContext){
    var formContext = executionContext.getFormContext();
    var pbiFrame = formContext.getControl("IFRAME_BI");
    var GUID = formContext.data.entity.getId().replace(/[{}]/g, "");
    //replace the Power BI url with the url from the Embed step
    var PBIurl = "https://app.powerbi.com/reportEmbed?reportId=d86fdc2b-316a-420e-b177-9e477c879031&autoAuth=true&ctid=83121429-cfda-4a1b-89c9-969eb42dc605";
    //accounts = account table in BI, and accountid=guid for account
    pbiFrame.setSrc(PBIurl + "&filter=accounts/accountid eq '" +GUID+ "'");
}

Only thing you need to change is the URL from the powerbi.com secure embed. Since I am using account and opportunity out of the box, the table names for both of them are “accounts” and “opportunities”.

In the filter above I am filtering the results in the Account table with the account ID that i will find within CRM. The result is to show a graph for only that single account.

Remember to Pass Execution Context here. I don’t really now why, but it wont work without. Normally when I script I don’t have to use this, but if someone would tell me what it does I am happy to learn:)

Publish all settings and open Dyanmics 365 to the account (that you know has some opportunities).

As you can see Alpine Ski House is showing a sales funnel of the opportunities related and nothing more. This is a filtered report in Dynamics based on the account in a BI tab on top. Navigation here in dynamics is pretty smooth.

2 thoughts on “Power BI embed to Dynamics 365 form with filtering

  1. Thomas, good stuff! The reason you need to pass the execution context as the first variable is because the execution context is needed in the function to yield the form context, which is necessary to get the entity record guid. The execution context tells your function what’s going on outside of the function itself. Back in the old days you could get this information by escaping to window.Xrm or window.parent.Xrm, but sadly no more.

    Like

    1. The error reported was when trying to get the iFrame to a variable (step before guid). Before I just used Xrm.Page.get…

      I guess there are lots of scripts I have to change.

      Thank you so much for info:) Any blogs you would recommend to learn more about the change for copy paste coders?

      Like

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s