I realize this is my fourth blog on this particular topic, and I am sure it won’t be the last. The occasion: Microsoft has released a new built-in, easier way of embedding Power BI reports on Dynamics 365 and filter them according to record context.
The approach is not as user friendly as I had hoped as it does require editing the form XML. To be fair, this approach is the easiest one yet and this is the first iteration of this feature, so given the importance of this area, I’d expect it to evolve over time.
Normally, editing the form XML would require you to export and import the solution, but luckily we have a tool in the XrmToolBox that makes that part easy.
Note: Power BI must already be enabled in the Dynamics 365 system settings for this feature to work.
To embed the Power BI report on the form, you must add a section with a control reference to the form XML which identifies your Power BI workspace, the reports, and the filter context. And that’s about it, so let’s get started on the step-by-step.
Create a section on your form
First, let’s insert the section on the Dynamics 365 form where we want to add the Power BI report. In this case, I am adding the section to an account form. For the sake of the example, I am going to assume we already have a Power BI report we want to use.
Open the XrmToolBox and find the FormXml Manager plugin.
If this tool is not already available, install it from the plugin store. Chance would have it that this tool has been made available by my friend Mohamed Rasheed, who I am collaborating with on the Power Query Builder and the Advanced Chart Editor.
In the tool, load the entities, select the account entity, and open the form where the new section was added. In this case, the form I edited earlier was called “Embed Power BI Account Form”.
Click on the Edit FormXml button and locate the section where you added the placeholder for the Power BI report. I use the label description to identify the section I added.
Replace the whole section with this code.
Replace the following parameters with details from your specific Power BI report. Highlighted with blue text above.
Power BI Group Id
This is the workspace ID from Power BI. If your report is in your “My workspace”, leave this at all zeros. When in a workspace, the ID is the part following “/groups/” in the url. In “My Workspace”, it will say “/me/” instead of “/groups/”.
Blurred part is the group id.
Power BI Report Id
Navigate to the report and grab the ID after the “/reports/.
On the report, go to File and Embed and copy the URL. You will only need a part of the URL. The term “TileUrl” is a little misleading in this context, as it is not necessarily a dashboard tile that is being referred to, but the report directly.
The part of the URL you need is the one that comes before “&autoAuth=True. Do not include that part or anything after it.
There may be an easier way to get only the part of the URL we need, but this is fastest approach I have found.
Power BI Filter
This is where the filter is set on the report.
Table and column need to exactly match the names in Power BI.
The filter alias is the schemaname from Dynamics 365 that you filter on.
In the example above, Power BI has a table called “Accounts”, and a column called “accountid”. The filter alias is the “accountid” field in Dynamics 365 from the account record that we are on.
Update and publish
Once all the parameters have been updated, hit update and publish in the FormXML Manager.
Go back to your account form in Dynamics 365 and refresh the browser and you’ll be able to see your new filtered, context specific Power BI report on the Account form.
Keep in mind that there will always be a little trial and error on getting the report to fit properly. I recommend working with the page pixel size as well as the Fit to Page, Fit to View settings to get it right.
A couple of notes
I really like that the filter does not have to be necessarily tied to the record id. It can be any value from the record. That means we can add visuals on a case or opportunity, that are filtered by the customer instead. This gives us some more flexibility.
Here are some setting options that are currently not included, but that I’d love to see in a future iteration:
- Target a specific report page, instead of going to the default page
- Remove Page Navigation at the bottom of the report
- Include/exclude filter pane
- Include/exclude buttons that
- Open the report in Power BI
- Enlarge the report similarly to how a Power BI report is expanded from a tile on an embedded dashboard
- Refresh report
This feature only works in UCI unified interface. The Power BI visual does not render in the classic interface using this method. If you are on the classic interface and need to show Power BI visuals, you will have to use on of the methods previously discussed.
If you afterwards go to the Form editor in Dynamics 365 and open the section we added manually, then we actually get an interface with selections for the Power BI workspace, dashboard, and tile we want to show. However, by using the selections here, we can only select Power BI tiles, not reports. That means, no interactivity directly. However, some of the buttons from my wish-list are available when showing a tile. I think this may be a preview of what is to come. When I made some changes here, I lost the filter context which is generally always needed on the form level though.
Last, but not least… all the usual disclaimers around using Power BI in Dynamics 365 still apply. The Power BI report needs to be shared with Dynamics 365 users. Otherwise they cannot see it. You will also need Power BI Pro, or alternately Power BI Premium. Security is determined by the Power BI report, so it does not respect your Dynamics 365 security role.
Here’s the original blog post from Microsoft introducing the feature. The code I used earlier is a modification of the code from this post. The code here, at the time of writing, was missing a few brackets. I also changed the Power BI filter options to parameters that I more commonly use in a scenario like this.
Here are the previous post on filtered Power Bi reports on Dynamics 365 forms mentioned earlier.
- Embed Power BI Visuals in Dynamics 365 – with Secure Embed
- Contextual Power BI Reports on Dynamics 365 forms
- Record Specific Power BI Reports in Dynamics 365 (only one click away)
The post called “Contextual Power BI Reports on Dynamics 365 forms” has the most details on what you can do with a Power BI report on a form vs. using built-in charts.
Enjoy. As always, please sign up for my newsletter or follow me on Twitter Follow @CRMChartGuy