Embed filtered Power BI reports on Dynamics 365 forms – again – the official way

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.

Embed Power BI System Settings

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.

Add section to from
Added a section called “POWER BI EMBED”

Open the XrmToolBox and find the FormXml Manager plugin.

FormXml Manager XrmToolBox

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”.

FormXml edit 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.

FormXml section highlighted

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/”.

Power BI group id

Blurred part is the group id.

Power BI Report Id

Navigate to the report and grab the ID after the “/reports/.

Report ID from url

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.

secure embed in Power BI button

Secure Embed Code from Power BI

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.

Power BI Filter formxml
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.

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.

Power BI on form1

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.

Power BI on Form2

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.

https://docs.microsoft.com/en-us/powerapps/maker/model-driven-apps/embed-powerbi-report-in-system-form

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.

Enjoy. As always, please sign up for my newsletter or follow me on Twitter 

29 thoughts on “Embed filtered Power BI reports on Dynamics 365 forms – again – the official way

  1. 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.

    • 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.

  2. 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.

  3. 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

  4. 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.

  5. 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+”‘”);

    }

  6. 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!

  7. 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.

    • 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.

  8. 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?

  9. 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”}}

  10. 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?

  11. 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?

  12. 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

Leave a Reply to Michael Winther Cancel 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