Contextual Power BI Reports on Dynamics 365 forms

I have mentioned on a few occasions that Power BI does not allow you to iframe its components. This means you cannot just add an iframe of a Power BI report inside Dynamics 365 on a customer form, for example.

While Power BI does not allow this directly, the statement does come with an asterisk.

You can, but you will have to create an app in Azure, give it proper privileges and then develop some plugins in Dynamics 365 that handles the authentication. Not an easy task and probably out of reach for non-developers.

So I got really excited when I recently found a solution called “Power BI Viewer” that does exactly that. The solution has been made by Trond Aarskog, who has been generous enough to make it available on his website. http://crm-powerbi-viewer.heiigjen.com/

Trond has provided easy to follow steps on how to create the app in Azure and make the initial configurations in Dynamics 365.

Once those initial steps are completed, you can add Power BI reports and Power BI dashboards on Dynamics 365 dashboards. You could already do that, so that by itself isn’t amazing. The amazing part is that you can add custom functions to Trond’s code that allow you to add filters contextually of what you are viewing in Dynamics 365. For example, that means on your account form, you can now add a Power BI report and filter it dynamically to only include data for the given account and include images from Dynamics 365 too.

Power BI report in Dynamics 365 filtered for the current account.

BOOM!!!

Trond also has the details on his site on how to setup the filtering.

Since you can pass the ID of the record you are now able to do stuff like:

• Filter a report according to the given record
• Use the Power BI maps with data added
• Create a common table for both activities and notes and display it on the report
  • Use the data model to include records from any children or associated records
• Pass guid of the active user and filter a Power BI report accordingly (NOT possible in Power BI itself)
• Use custom network visualizations in Power BI to show relationships
• Power BI reports are added as web resources which means they can be added to system dashboards

In addition, Trond’s solution also lets you embed Power BI reports in Dynamics 365 On-Premise!

Couple of lessons I learned while playing with Trond’s solution:

Notes On Setup

(This section probably won’t make sense until you’ve looked through Trond’s guides) While I definitely encourage you to read through Trond’s guide for the setup first, here are some of my notes that might be helpful.

When adding the Application ID from Azure to the configuration file, the ID needs to be in quotation marks.

When filtering reports according to a record ID, note that Dynamics 365 passes the guid inside a pair of { }. Power BI exports the guid without {}. Make sure your web resource removes the {} from the guid before applying the filter.

Here’s the script I used to filter on the account ID. It is based off the sample available on the Power BI Viewer website.

Of course, the table and column name here will have to match what you have in your Power BI data model. And of course, in the properties for the web resource on the form, check the box for passing the record ID.

NOTE: Trond is planning to update this part of the solution once the new CCF SDK becomes available and make it much easier for everyone to use. YEAH!!

Entity Images

You can include the entity images in your Power BI reports and they will display when shown inside Dynamics 365. I.e. pictures of users, contacts, etc.

However, they will not display in the Power BI Desktop Client or when using the powerbi.com by itself. The area where the image is supposed to be just shows up blank.

You can see the Dynamics 365 images on powerbi.com, if you have a tab with Dynamics 365 open at the same time. So basically, when viewing the images from Dynamics 365 in Power BI, your browser needs to be authenticated against Dynamics 365 at the same time.

Report Page Size

When adding the Power BI Viewer to a form, you can specify the amount of columns and rows the webresource should use. Power BI will display the entire report page inside the area you defined. This means any white area on the report page surrounding the visualizations will be displayed as well. You can effectively change the report page size to match the size of the area you want to show the report in. That will help you size the charts better.

The view mode also affects how the report is displayed in Dynamics 365. Use Actual Size to force a specific measurement. This will add scrollbars on the report if the window isn’t large enough. Fit to Page and Fit to Width will change the size of the report with the window size. It seems to be best to use Fit to Page when saving the report, but it is helpful to use Actual Size while modifying it.

Set view mode to actual size to get a better idea of how it might look in Dynamics 365. Set to Fit to Page when saving.

Disable Navigation and Filtering When Displaying On Forms

Make sure you set the ShowFilterPane and ShowNavPane in the web resource properties to false. That will give the report more space on the form.

Setting the ShowNavPane and ShowFilterPane to false will give the report more space and not take up screen real estate as shown above.

Maps

While you can use the regular Power BI maps, the ESRI ArcGIS maps are not allowed when embedded in this manner. This intentional limitation also applies to the Power BI Embedded service in Azure.

The Catch

There’s gotta be catch right? The reports do load a little slower than the rest of the form, but nothing that is not manageable. Main catch is that you will need a Power BI Pro license (or Power BI Premium) in order to share the reports. You will also need to remember to share your Power BI reports with all the Dynamics 365 users.

Also, your reports will only be as up to date as Power BI. Depending on your refresh rate, that could be up to an hour, or longer if outside business hours. This is of course an area where the Data Export Service could be used successfully to get closer to live data.

Hope you all find Trond’s solution as helpful as I have. I definitely think the solution fulfills a very significant gap in how you can use Dynamics 365 and Power BI together. If you have listened to any of the CRM Audio Power BI episodes, you will occasionally have heard Scott Sewell and I talk about different workarounds to get this kind of contextual reporting. Those workarounds always included some kind of link or button that would take the user to the report, rather than just displaying it inside the form. This is now possible. I am definitely looking forward to working more with this solution and will of course share tips and tricks that I find useful, so don’t forget to sign up for my newsletter or follow me on Twitter.  Follow @CRMChartGuy