Writing and deploying Power BI reports for Dynamics 365/PowerApps

Writing Power BI reports

Power BI is a cloud service from Microsoft that provides integration options for Dynamics 365 CE. Using Power BI, we can develop rich reports and share them with users. Here, we are going to develop a sample report using Power BI; so, first, we need to set up a Power BI trial at https://powerbi.microsoft.com. You can use your same Office 365 credentials to set up this trial. We will develop our report using Power BI Desktop, which you can download at https://powerbi.microsoft.com.

Let's perform the following steps to develop our sample report:

1. Download Power BI Desktop and complete the installation steps.
2. Open Power BI Desktop and click on Get data:

3. Connect to Dynamics 365 CE by taking the following steps:

4. As soon as we click on Connect, a new dialog will appear, in which we can provide a Web API URL. We can copy it from the Settings | Customization | Developer Resources section:

It may ask you to connect to your Dynamics 365 CE organization if you are using it for the first time. Use your Dynamics 365 CE credentials.

5. Now we are connected to our Dynamics 365 CE organization, we need to select which entities we want to use in our report. Let's select the accounts, him_makes, him_models, and him_vehiclesentities. Power BI Desktop will connect to Dynamics 365 CE and we should be able to see all the selected entities in the Report Designer, as follows:

6. Let's expand the him_vehicles entity and select him_vehiclename, him_vehiclenumber, and _him_customer_value from the list. We can change the label of the fields by taking the following steps:

7. You will notice that the customer name is not displayed; instead, it will display the GUID of the customer record. To get the customer name, we need to add a new column and use the LookupValuemethod, so let's do that.
8. Click on the New Column button and change the label to Customer Name:

9. We will use the following formula to get the display name of the customer based on their GUID:

Customer Name= LOOKUPVALUE(accounts[name],accounts[accountid],him_vehicles[_him_customer_value])

Here, the first parameter represents which column we want to get from the related entity, the second parameter is based on which column we want to get data, and the last parameter represents the GUID field of the current entity, which holds the customer ID. Now, we can uncheck the him_customer_value field and check our new Customer Name field, and we should be able to see our new field in our report, like so:

We can use other visualization controls based on our requirements.

You can find more information on creating Power BI reports at https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-report-add-visualizations-i.

10. We can use the Format option to format different sections of the report:

Now, our sample report is ready, so let's upload it to Dynamics 365 CE.

Deploying a Power BI report to Dynamics 365 CE

We can place a Power BI dashboard or Power BI tiles in Dynamics 365 CE dashboards. We are going to create a Power BI dashboard and we will deploy this dashboard in Dynamics 365 CE. First, we need to publish our report to Power BI online; then we can create a dashboard to use it in Dynamics 365 CE. Let's take the following steps to use our sample report on Dynamics 365 CE:

1. Click on Publish to publish our report to Power BI online and follow the numbers given in the following screenshot:

2. Open Power BI in the browser and verify our report:

3. Click on the report to open it and create a new dashboard using the Pin Live Page option:

Select the Go to Dashboard option from the pop-up dialog; it will open a Power BI dashboard, which we can use in Dynamics 365 CE.

4. To deploy our Power BI dashboard to Dynamics 365 CE, first, we need to enable a setting for Power BI. So, navigate to Settings | Administration, click on System Settings, and enable the option as shown here: 

5. Navigate to the Dashboards area and click on the New option. You should be able to see the Power BI Dashboard option, as follows:

6. It will show a dialog with Power BI dashboards. Select our dashboard and click on Save, like so:

After that, we should be able to see our Power BI dashboard as follows:

This is how we can develop Power BI reports and dashboards and deploy them to Dynamics 365 CE.