Integrating Power BI into your Microsoft Dynamics 365 forms revolutionizes user interaction, allowing for real-time data visualization and analytics directly within your CRM environment. In this comprehensive step-by-step guide, we will explore how to seamlessly embed Power BI reports onto your account, contact, and opportunity forms, enhancing the way your sales and customer service teams access critical information. By following this guide, you will learn to create, customize, and filter Power BI reports that cater to your business needs, ensuring a user-friendly experience that maximizes data utility. This integration not only optimizes operational efficiency but also empowers users with actionable insights right at their fingertips.
Let’s discuss how you can integrate Power BI into Dynamics 365 by adding specific tiles to your account form, contact form, opportunity form, and more. This integration is revolutionary as it brings real-time data directly to your users in Microsoft Dynamics. Welcome to the step-by-step guide on how to enable this feature in your environment today!
Why add a Power BI Report to your Dynamics form?
Forms, in Dynamics, are where your sales teams, customer service agents, or field service agents are going to spend most of their time. These forms are the primary way for users to view and interact with data within dynamics. Fortunately, you can now add Power BI Reports directly into forms in dynamics.
This is going to bring the power of Power BI’s data analytics and visualizations directly into dynamics for you’re users to see and get a better grasp of the data.
General Process Overview
- Create a Power BI Report
- Format the Report to be Dynamics friendly
- Add the desired Table’s Form and Power BI Report to a solution together
- Add the Power BI Report form component to the Table’s Form
- Filter the Power BI Report to show the data you need
#1 Create a Power BI Report
The first step in creating a Power BI report is to connect to your data source. Power BI supports a wide range of data sources, including Excel spreadsheets, SQL Server databases, and cloud-based data sources. You can connect to your data source by selecting the Get Data button on the Home tab of the Power BI Desktop ribbon.
Once you have connected to your data source, you can start creating your report. Power BI provides a drag-and-drop interface that allows you to create visualizations by dragging fields onto a canvas. You can choose from a wide range of visualizations, including charts, tables, and maps. Because the desire is for these reports to be on a Dynamics Form, I would not recommend adding more than 1 to 3 visuals and sticking to the Card Visualizations.
After you have created your report, you can customize it to meet your specific needs. Power BI provides a wide range of formatting options that allow you to change the appearance of your visualizations. More on this in the following section.
Once you have finished creating and customizing your report, you can publish it to the Power BI service. The Power BI service allows you to share your report with others and collaborate on your data. Publishing your report is also required in order for Microsoft Dynamics to find it.
#2 Format the Report to be Dynamics friendly
You can format the components in your report however you please. There truly are limitless possibilities for designing how you want this report to look. Just remember that if you are going to be implementing this report onto a Dynamics Form, you want this to be fairly simple. There is not much screen space for the report on the form and you will want the information to be the focus. This will allow you to make the information as large as possible.
Pro Tip: Set the Report’s Canvas Settings to a custom pixel size of 300px by 150px.
This is important because it is going to correct the size of your report so you will not need to later, after adding it to the form. 150px is about the width of one column on a three column form. Consequently, if you do not do this, then the components on your report will be incredible small in Dynamics.
Once you have finished creating and customizing your report, you can publish it to the Power BI service.
#3 Add the desired Table’s Form and Power BI Report to a solution together
Now that your report is published, then you can navigate back to the Power Apps Maker Portal. What we’re going to be looking to do is adding the report and the data set to our solution.
The first component that needs to be in the solution is your desired table and the table’s form. This is the form that the report will be living on. This is important because the report and the form need to be in the same solution in order to “talk to each other”. Subsequently, this way you ensure that there are no dependency issues when the solution is exported.
After your form is in the solution, you will need to add your Power BI Report and the Power BI Dataset. You can do this by selecting Add Existing. Then under Analytics, you will see both Power BI Dataset & Power BI Report.
In the Add existing BI Report page, change the tab to be the From Power BI tab. Then choose the workspace you published your Power BI Report to, then select your report and select Add. You should now have a solution with at least the Table Form, the Power BI dataset, and the Power BI Form. If you have this, you are good to move onto the next step, getting the report on the form!
#4 Add the Power BI Report form component to the Table’s Form
Open the form editor in the Power Apps Maker Portal. I would first recommend creating a new section for your report to reside. The Report will act as any other form component, so it can be placed in an existing section if necessary. To add a new section to your form, select Components in the ribbon, and then select the 1-column section component. You can then drag and drop this where ever you please.
Following the set up of your new section or defining where you want the report to be on your form, select Components in the ribbon. Then open the Power BI dropdown to reveal the Power BI Report component. Drag and drop this to the desired location on the form.
Be sure to select the correct report in the Report dropdown. If there is only one available, it should default to the correct one. Be sure to configure any of the following component settings. Once you have configured all of the settings, select Done and you will see your Report displayed.
Please note: You may find that the report will not accurately display your data when you are first add it to the form (reads as “blank”. That is ok and is expected behavior. Once you save and publish and view the form in CRM, it will be fixed. If the issue still persists, than there may be an issue with a data connection.
Be sure to Save and publish your form before you leave so that the changes will be put into effect and you can begin utilizing the power of Power BI in Dynamics.
Although the report is now on your form, you are not quite done with the configuration of the report. You may notice that the Power BI Report is going to look at all of the information in the dataset it’s built on. If you want the report to only showcase the data related to the record you are on, you’re going to have to add a small amount of filtering to the report component.
#5 Filter the Power BI Report to show the data you need
Without the correct JSON filter, the report is going to show all of the information in the dataset. Typically, if you are wanting to view information on the Account form, you want the report to show data that only applies for that account.
Fortunately, you can accomplish this with a small amount of JSON filtering. In my scenario, I needed to show some information on the account form about the account’s won opportunities. I wanted to capture the amount of revenue the account had in won opportunities, as well as the latest closed as won date.
The JSON Filtering for my scenario looked like this:
{"Filter":"[{\"$schema\":\"basic\",\"target\":{\"table\":\"opportunity\",\"column\":\"customerid\"},\"operator\":\"eq\",\"values\":[\"$a\"],\"filterType\":1}]","Alias": {"$a": "accountid"}}The generic version of the JSON Filtering (which I recommend copying and updating) looked like this:
{"Filter":"[{\"$schema\":\"basic\",\"target\":{\"table\":\"CHILD-TABLE-SCHEMA\",\"column\":\"CHILD-LOOKUP\"},\"operator\":\"eq\",\"values\":[\"$a\"],\"filterType\":1}]","Alias": {"$a": "PARENT-GUID-SCHEMA"}}Here is a breakdown of the bold components in the JSON:
- Child-Table-Schema = the logical name of the table that the data is measuring, not the table that the form is on
- Child-Lookup = the schema name of the lookup field on the child entity that looks to the parent table
- Parent-GUID-Schema = the schema of the ID field on the parent table (the table that the form is on)
In Conclusion
The Power BI Report component is a powerful feature in Microsoft Power Apps that allows you to add rich reporting and analytics to your main forms. By using this component, you can empower your users to accomplish more and make informed decisions. The Power BI Report component is easy to use, highly customizable, and integrates seamlessly with other apps. If you’re looking to take your Microsoft Power Apps forms to the next level, consider using the Power BI Report component!
Lastly, I want to give some major kudos to a colleague and good friend of mine, Adam Piercy. Adam is a phenomenal Solution Architect for implementing Microsoft Dynamics for enterprise organizations. He truly is an expert in the space. He was the first person to showcase this functionality to me and helped me learn how to implement this feature into Power Apps. Without him, this post would not have been possible.
Happy Developing!