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.
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.
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.
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.
This is where the filter is set on the report.
For ease of reading, the example here is broken down on multiple lines. In your code, leave it as one line as in the code example earlier.
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.
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.
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.
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.
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.
Do you know how to enable/disable filter pane and Page selector in the BPI report?
When you show a filtered report the filter pane is automatically disabled. On an unfiltered report it is enabled. Page navigation cannot be disabled. Hopefully those settings will be added in the future.
Have you tried to implement this in system dashboards?
You should be able to remove the pane using URL filtering, by adding the &filterPaneEnabled=False attribute after the report URL, for Online PowerBI reports anyway.
Let me ask you a question about how to deal with visibility permissions based on Dynamics CRM when PowerBI data source is a Azure SQL Server feeding by Data Export.
Hi Javier. Can you elaborate on your question. Generally your visibility permission considerations would be exactly the same whether you are using the Azure SQL/Export Service or connect to Dynamics 365 directly.
Excellent article!! Thx for your valuable contributions.
Regarding your future item “Target a specific report page”, I’ve been able to target specific pages using the page suffix in the report URL. Today date, I’ve only used the page suffix on the “Publish to Web” URL. I’ve not tested them on the embedded reports but suspect the report URL part is the same.
Hi DrCRM. I’ve been able to embed a specific report page before, but using other methods. It is within this “built-in” way of embedding it, that I don’t see an option to go to a specific page. That’s why I have it on the wish list.
Yes, this will work, but it requires you to iframe the Power BI report rather than using the built in form component.
Great blog, but it doesn’t seem to be working for me.
I might me missing out on some thing.
However I did followed all the steps, I was wondering if a special type of report is needed.
I created a sample report with an Accounts table and an Opportunity table.
On adding the report to the form there are no values in the table.
Any help or suggestion is much appreciated .
Using the XrmToolBox plugin mentioned we also have the ability to modify system dashboards.
Just build a system dashboard with an empty section and replace it with your own modified element – or add the element to an existing dashboard.
Only one minor adjustment has to be made to get it working: DELETE the attribute IsUserDefined=”0″ in the element – and then publish the modified dashboard XML.
Now I just have to find a clever way to filter the system dashboard using the GUID of the current user viewing the dashboard WITHOUT resorting to using Row-Level Security in my Power BI dataset.
hello. Is there an option for a dynamic group and report ids?
I don’t see a way of doing that in this context. You would likely need to use a different embed method and then dynamically populate the ids. However, Power BI does allow for personalized KPIs etc. so the data shown can be somewhat dynamic.