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.
<section id="{d411658c-7450-e1e3-bc80-07021a04bcc2}" locklevel="0" showlabel="true" IsUserDefined="0" name="tab_4_section_1" labelwidth="115" columns="1" layout="varwidth" showbar="false"> <labels> <label description="Filtered Power BI embedding demo" languagecode="1033" /> </labels> <rows> <row> <cell id="{7d18b61c-c588-136c-aee7-03e5e74a09a1}" showlabel="true" rowspan="20" colspan="1" auto="false"> <labels> <label description="Accounts (Parent Account)" languagecode="1033" /> </labels> <control id="filteredreport" classid="{8C54228C-1B25-4909-A12A-F2B968BB0D62}"> <parameters> <PowerBIGroupId>00000000-0000-0000-0000-000000000000</PowerBIGroupId> <PowerBIReportId>aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee</PowerBIReportId> <TileUrl>https://app.powerbi.com/reportEmbed?reportId=aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee</TileUrl> <PowerBIFilter>{"Filter": "[{\"$schema\":\"basic\",\"target\":{\"table\":\"Accounts\",\"column\":\"accountid\"},\"operator\":\"In\",\"values\":[$a],\"filterType\":1}]", "Alias": {"$a": "accountid"}}</PowerBIFilter> </parameters> </control> </cell> </row> <row /> </rows> </section>
Replace the following parameters with details from your specific Power BI report. Highlighted with blue text above.
- PowerBIGroupId
- PowerBIReportId
- TileUrl
- PowerBIFilter
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/.
TileUrl
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
crm chart guy 
Hello,
Do you know how to enable/disable filter pane and Page selector in the BPI report?
//Hampus
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.
Thanks!
Have you tried to implement this in system dashboards?
Great post btw!
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.
The page selector can’t be hidden yet as far as I know, not without JavaScript at least.
Rasmus
Hi Rasmus
Have you been able to do this? I get an error if I attempt to include &filterPaneEnabled=False in the TileUrl.
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.
The page selector can’t be hidden yet as far as I know, not without JavaScript at least.
Rasmus
Aw, forgot that the secure embedding only accepts URL parameters that start with &$, so that would make it &$filterPaneEnabled=False
I haven’t been able to make that work in this context. If you get the syntax to work, please let us know 🙂
I did notice that that filter pane is automatically off for reports that are filtered. And on for unfiltered reports. This is a good default setting, but it would be nice to have the control.
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.
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.
The page selector can’t be hidden yet as far as I know, not without JavaScript at least.
Rasmus
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.
Cheers
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.
I used the following function to filter the report on accountid, excuse the outdated namespace.. This was done on a new trial located in the US a couple of weeks ago, and other than this all I did was add a standard iframe directly to the form called “IFRAME_report”
function setSource() {
var pbiframe = Xrm.Page.ui.controls.get(“IFRAME_report”);
var guid = Xrm.Page.data.entity.getId();
pbiframe.setSrc(“https://app.powerbi.com/reportEmbed?reportId=ac70e832-ad38-4795-a944-80c9011d217f&autoAuth=true&ctid=3e145ac8-fe55-4bca-9391-538c9afc52cd&$filter=account/accountid eq ‘”+guid+”‘”);
}
Yes, this will work, but it requires you to iframe the Power BI report rather than using the built in form component.
Hi,
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 .
Thanks!
Below is the form xml I am passing
00000000-0000-0000-0000-000000000000
d5d9cc16-79b7-4a7f-9da3-333e826f5bd6
https://app.powerbi.com/reportEmbed?reportId=d5d9cc16-79b7-4a7f-9da3-333e826f5bd6
{“Filter”: “[{\”$schema\”:\”basic\”,\”target\”:{\”table\”:\”accounts\”,\”column\”:\”accountid\”},\”operator\”:\”In\”,\”values\”:[$a],\”filterType\”:1}]”, “Alias”: {“$a”: “accountid”}}
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.
[…] Ulrik’s blog about embedding Power BI visuals the right way: https://crmchartguy.com/2019/03/24/embed-filtered-power-bi-reports-on-dynamics-365-forms-again-the-o… […]
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.
Hello, When I’m embedding a report and publishing it via Form XML Manager – it doesn’t display any data with the Model Driven App. I have to go into the App Designer –> Form Designer, edit the Form and without doing any changes just Save and Publish it. After which, the report is being defaulted to a Dashboard and only one Tile is showing up, but not the whole report. Filtering appears to be working fine for the Report, but unfortunately only one tile shows up, but not the whole report. Am I missing something or did I do anything wrong?
Well done! Less integration and a link between applications to show relevant data such as ERP information.
Hi Ulrik
Awesome job!
I am able to display the PBI-report on the form, however, I have run in to troubles with the contextual filtering. Seems like it does not take the cr617_RECQRequirementID into account. Am I doing something wrong code? Or is it because need to add a filter in the PBI-report of some sort?
Details:
Power BI table name: RequirementEntity
Column name: Requirement ID
Name of the attribute that needs to be filtered on: cr617_RECQRequirementID
Code:
{“Filter”: “[{\”$schema\”:\”basic\”,\”target\”:{\”table\”:\”Requirement Entity\”,\”column\”:\”Requirement ID\”},\”operator\”:\”In\”,\”values\”:[$a],\”filterType\”:1}]”, “Alias”: {“$a”: “cr617_RECQRequirementID”}}
The space in “Requirement ID” is supposed to be supported, but I’d recommend testing on a report without spaces in the column and table names just to rule it out.
Using this option – do the Dynamics 365 users need a Power BI license in order to view the embedded Power BI report on a D365 form?
Yes, a Power BI Pro license is needed for sharing the reports.
Hi Ulrik, it’s a great tool and I’m looking forward to using it. When I update the form, I don’t receive any error message, but the power bi icon just blinks on and off in the section . Any idea why it’s not working, or what I could look at to get it to fix?
Hi,
I am getting errror message when trying to publish – … Schema validation error..The element “parameters” has invalid child element “PowerBIReportId”. List of possible elements expected: PowerBIGroupId, PowerBIDashboardId, TileId, TileUrl, Type, EnableinMobile.
What is wrong?
Thank you
Mirek
I was able to embed a filter my report following this procedure. However, I am having issue when the filter is blank (accountnumber is null in my account). The report is displayed unfiltered instead of showing a blank report.
Is this a know issue ?
Fred
I don’t know if Microsoft has it listed as a known issues, but I see the same behavior. So it is definitely a pitfall if you are not using accountid or something similar that is always populated.
How I can enable for Mobile using xml?
It used to work automatically. What kind of error, if anything are you seeing?
Hello,
Have you any idea what the syntax would be for multiple filters (multiple tables)?
Below an example when I would create the filters in Power BI and get added to the filterpane.
https://app.powerbi.com/…..?filter=Sales/CustomerNo eq ‘filtervalue’ and Receivables/CustomerNo eq ‘filtervalue1’
So I want to filter the same alias value but on two/three different tables. I tried adding two parameters “PowerBIFilter”, pasting the whole JSON text in one “PowerBIFilter” but this didn’t do anything.
My backup could be that I create a filter table with unique values in my Power BI datamodel and link this one to all the tables but I would rather not do that.
If you have any more insight on how to construct this filtering I would be pleased to hear.
Kind regards,
Loran
Hello Ulrik, how do you in CRM modify the height? For me, it always stays the same even though I have modified the report to be a fixed height in pixels but still, the report has a lot of white space below and the report takes the entire page.
Hi, great article!
Was very useful for a requirement on a project.
However, the embedded report is not responsive on the mobile app, is there a way to fix this? The report elements are very small and you can’t zoom in/out. The report is responsive on the web browser and it would be great if the same worked for the mobile app.
Thanks!
Hej Ulrik!
Embedding the report works fine, I just created a super-simple one with estimated vs actual revenue for testing purposes.
But – the Power BI report is showing unfiltered information, i.e. I get all estimated and actual revenue for all accounts, not the selected one. My filter in the Power BI report uses accountid, but it doesn’t seem to work 😦
//Niklas
I am having the same issue. Did you manage to resolve this issue?
Yes, replace & with &
It changes the & into the text comand for & (so xlm doesnt get confused)
Hope it helps.
Thanks for this article.
I was able to successfully embed a power bi report in an iframe in a web resource on an account form in our dynamics 365 sales system. It works great in a browser, but when I use the Dynamics 365 mobile app the iframe prompts for me to sign it, which wants to open in a browser rather than in the dynamics 365 app itself so even if I sign in in the browser the app has no idea.
I could use some pointers on getting this to work for users in the mobile app. The report is data that is not inside of dynamics 365, but we filter based on data from the CRM system.
Thank you
-Jeff
That worked! Thanks for sharing. Question: how would you add another filter? For instance
account (PBI) = accountID (CRM)
AND
Company (PBI) = Responsibility Center (CRM)?
Thanks for the article. It works great when filtering one column. However, I can’t seem to figure out how to filter two columns simultaneously (eg AccountID and Responsibility Center). The filter Wiki page (https://github.com/Microsoft/PowerBI-JavaScript/wiki/Filters#contructingfilters) only refers to complex filters within the same column.
Any ideas on how to approach this? I tried the following with both fields from the Accounts entity but it didn’t work:
{“Filter”: “[{\”$schema\”:\”basic\”,\”target\”:{\”table\”:\”Sales\”,\”column\”:\”responsibilitycenter\”},\”operator\”:\”In\”,\”values\”:[$a],\”filterType\”:1}]”, “Alias”: {“$a”: “responsibilitycenter”}}
,{“Filter1″:”[{\”$schema\”:\”basic\”,\”target\”:{\”table\”:\”Sales\”,\”column\”:\”accountid\”},\”operator\”:\”In\”,\”values\”:[$b],\”filterType\”:1}]”, “Alias”: {“$b”: “accountid”}}
Thanks,
Joris
Hi Joris,
I’m using the same syntax with success to combine 2 filters.
However, the result is an AND-combination between the filters – and I want an OR-combination.
Do you have any idea how to combine the filters with OR so the result is the UNION and not the COMMON result?
Ulrik, thanks for a great article!! 🙂
/Tomas
Great blog post Ulrik, i have one question regarding the contextual filtering. Is it possible to filter on related entity fields?
For example we have an entity call Account Reference that is linked to the Account entity. My report is embedded on the account form but i want to filter on the id field in the Account Reference entity.
Thank you for this very helpful blog, but I was wondering if anyone had a solution for the following problem: I am using the CRM accountnumber on an Account form as a filter for data in a BI Report and because this accountnumber is all numeric it seams to be converted somehow to integer and no match found in the BI data. The accountnumber in standard CRM is a string and so is the column in the BI Source. For testing purpose we tried to create a new column in BI Data Source and put one character (K) in front of the Accountnumber and then everything works fine. I can not change the type of the BI Report source to integer because some of the Accountnumbers start with zero and creating a new field in CRM with “K” + accountnumber for 400 K records is not a good option.
This is the Filter in the XML for the form.
{“Filter”:”[{“$schema”:”basic”,”target”:{“table”:”Customer”,”column”:”CustomerAccount”},”operator”:”In”,”values”:[$a],”filterType”:1}]”,”Alias”:{“$a”:”accountnumber”}}
Is it possible to change this part “values”:[$a] so that it is a combination of the constant “K” + $a (accountnumber alias) ?
Thanks,
Reynir K.
[…] You can also take this further by embedding Power BI reports are that filtered based on the current record with contextual filtering, we will take a look at this in another blog post but for now here are some links to help get you started on that if you wanted to: https://docs.microsoft.com/en-us/powerapps/maker/model-driven-apps/embed-powerbi-report-in-system-form  & https://crmchartguy.com/2019/03/24/embed-filtered-power-bi-reports-on-dynamics-365-forms-again-the-o… […]
Hi, Really great blog post.
I’m having slight trouble when adding the title URL. The adding goes blue after the &group id.
https://app.powerbi.com/reportEmbed?reportId=00xx0000-000x-00×0-x000-0xxxx0000x00&groupId=000xxx00-0xxxx-0000-0x0x-x00xx00x00xx
I can see that your example aboive doesn’t have the group id in the URL but it doesnt work if I remove it.
Is this a change to embedding or am I missing something obvious
Thanks
Paul
Superb post but I’m getting the same problem as Paul. The embed Tile url for a report in a workspace requires the groupid as well to find the report but the form xml edit will not accept the groupid so a bit stuck there.
Any help would be much appreciated
Thanks
Darren
Couple questions as I too am having difficulty filtering on account name. You refer to making sure you are using the same filter in your Power BI report. Please expand on this a little.
Excellent article!
I do have some questions though.
The Power BI report that I need to embed is shared with me. It’s not in my personal workspace. How can I get the ‘PowerBIGroupId’ parameter to work for a shared report?
The TileURL format has changed to https://app.powerbi.com/view?r=xxxxxxxxxx. Can this be used?