Simple Demonstration of Using Power BI URL Query in Dynamics 365 for Customer Engagement

Do you have any idea about how Dynamics 365 customer engagement apps work with Power BI for Microsoft 365 cloud service? In this blog, we will be demonstrating the way to display a Power BI report of Dynamics 365 account in the Power BI simply by clicking on this link within a Dynamics 365 account form.

So to begin with, apply URL query in order to get this feature simply by passing the account number from Dynamics 365 account form to URL.

Below, we have mentioned the steps that would follow:

• First, one would require to create this Power BI Report with the Dynamics 365 Data Source
• Next, the report will be published to Power BI Service
• Two fields need to be created now in the Dynamics 365 Account Form
• Then, a URL Query should be created
• Now it is time for a workflow to be created in Dynamics 365 in order to populate the account number in the URL Query.
• It’s time for the Result

Now, prior to beginning with the above-mentioned steps, make sure that:

A Power BI Report with the Dynamics 365 data source has already been created. In case you are keen to learn how Power BI reports can be created, there are amazing training courses available out there. Also to learn about the basics of Dynamics 365. In case you are keen to learn more regarding Dynamics 365, here is the link that will take you to the information page. Ultimately, to enrol for the 30-day free trial, head to this registration page.

Also, keep in mind that data source in the Power BI report is Dynamics 365 for the Sales Online version 9.1 (30-days free Trial Sample Data).

Once, you have ensured that the above mentioned two things have been taken care of, now begin the journey:

Step One: Creation of this Power BI Report with the Dynamics 365 Data Source

A power BI report has been created in order to display particular account information of the Dynamics 365 for Sales. It consists of Account Number, Account Name, All of the opportunities related to the account, Closed Data, Estimated Value, Actual Value. Meanwhile, the report is created in the Power BI Desktop.

Step Two: The report will be published to Power BI Service

With a click on Publish button on the page, a Power BI report will be published for this Power BI Desktop to the Power BI Service. Upon its success, this following message would be shown, so click Open ‘POC URL Query.pbix’ in Power BI in order to view the report.

Step Three: Two fields need to be created now in the Dynamics 365 Account Form

Next, it is time for using the Account Number field in Account entity in Dynamics 365. The same result can be attained with the use of the Account ID (GUID) field. But it would be beneficial to use Account Number because it’s a common field b/w Dynamics 365 CE & Finance & Operation module. Thus, the Account Number field can be used to create an association between two apps and for creating a data model in Power BI.

The Account Number field is available in Account entity. However by default, that not shown in Account main form. Thus, the field needs to be added to form. In the account form, a custom field can also be created in order to store ‘URL Query’.

Add the Account Number field:

1. Begin by logging in to the Dynamics 365 for Sales & open account form, for demonstrating, Proseware, Inc. sample account can be used.
2. Now click on the ‘FORM’. If you don’t see ‘FORM’ being displayed as an option, get in touch with your System Admin to seek permission.
3. Then Account Form Designer will appear. Click on the ‘Account Number’ field and then drag & drop it under Account Name field.
4. Click Save.
5. Then click Publish
6. Then, click Save and Close.
7. Refresh the browser so you can see Account Number field in the form.

Add the URL Query field:

1. Click on FORM>New Field.
2. In properties window, set values as this: Display Name: PBI Report, Field Requirement: Optional, Name: new_pbireport (keep in mind that prefix is going to depend on the publisher prefix), Auditing: Enable, Data Type: Single Line of Text, Field Type: Simple, Maximum Length: 200, Format URL, IME Mode: Auto.
3. Next, click Save and Close.
4. Refresh web browser of Account Form Designer (hit F5 key).
5. Choose Custom Field from Filter dropdown.
6. Drag & Drop PBI Report under Website field.
7. Click Save
8. Click Publish
9. Click Save & Close
10. Refresh browser & see PBI Report field in a form.

Step Four: Then, a URL Query should be created

Now it is time to extract & create URL query string from the Power BI Service. The Notepad needs to be opened in order to save URL for future use.

1. Going to the Power BI Service, it is time to open a report page that is published in Step One & Step Two.
2. In case you are having more than a single page in the report, it is integral to select a report page which is desired to be displayed ‘by default’.
3. Copy URL & paste it in the Notepad.
4. Update URL in Notepad adding ‘?filter=accounts/accountnumber eq ”’ soon after this following URL, without any space. Note: Keep in mind that at the end there is not one double quotation mark, but there are two single quotation marks. It is crucial, as it the step 5 you would see that it appears like this: https://app.powerbi.com/groups/me/reports/32068cc5-719a-46ba-8504-b78a62e8dc4a/ReportSection8b8acc8e442abc0c790a?filter=accounts/accountnumber eq ”

Step Five: Create a Workflow in Dynamics 365 in order to populate the account number in the URL Query.

1. In the Dynamics 365, head to Settings>Processes.
2. To create a workflow, click on +NEW.
3. In the properties window, values need to be set as:

Process Name: URL Query needs to be created for the PBI Report, Activate As: Process, Entity: Account, Category: Workflow, Scope: Organization.

Further ensure that each one of these is checked: As an on-demand process, Automatically delete the finished workflow jobs (in order to save the disk space), Record is created, Record fields change for the Account Number field.

4. Next, click on Update Record for the Add Step.
5. Select the ‘Account’ for an entity.
6. Click on Set Properties.
7. Copy & Paste URL Query from Step four, to PBI Report field.
8. Under the Operator>Look for, select the Account & Account Number. Click Add.
9. Before you click OK, set cursor between single quotation marks of URL Query & add Account number parameter between them.

Account Number parameter = {Account Number (Account)}. Now URL must appear like this:

https://app.powerbi.com/groups/me/reports/32068cc5-719a-46ba-8504-b78a62e8dc4a/ReportSection8b8acc8e442abc0c790a?filter=accounts/accountnumber eq ‘{Account Number(Account)}‘

10. Now click on Save & Close.
11. Click on Activate.
12. Click on Save & Close on workflow window.

Step Six: It’s time for the Result

Now it is time for running workflow created in the previous step & populate URL Query with the account number for the account.

1. In the Dynamics 365 for Sales, open account form of Proseware, Inc. Keep in mind, no query is yet created in PBI Report field.
2. Next, click on ellipses… that is next to the PROCESS in the Command bar.
3. Click on the Run Workflow.
4. Select workflow created in Step Five and then click on Add>OK.
5. Take a few breaths and now refresh your browser of the account form.

URL Query Link is populated. Now for the other account, the same workflow can be run from All Accounts view by choosing all accounts at the same time.

6. Next, click on a link in the PBI Report field.
7. Then Power BI Service window will appear with the account number.

So, this was the example of the Power BI & Dynamics integration by the URL Query without any coding. For further assistance, get in touch!