powerobjects: Power BI URL Query in Dynamics 365 for Customer Engagement (CE)

[Blog bot]

Источник: https://www.powerobjects.com/2019/01...-bi-url-query/
==============


In a previous blogpost, we discussed how to drill into Dynamics 365 CE records from Power BI. Well, how about the other way around – drilling from Dynamics 365 CE to Power BI Service? Today, we’ll demonstrate how to display a Power BI report of a Dynamics 365 account in Power BI by clicking a link within a Dynamics 365 account form. Enjoy!

We are going to apply “URL Query” to achieve this feature by passing an account number from the Dynamics 365 account form to the URL.

The list below summarizes the steps:

• Step 1: Create a Power BI Report with Dynamics 365 Data Source
• Step 2: Publish the Report to Power BI Service
• Step 3: Create Two Fields in Dynamics 365 Account Form
• Step 4: Create a URL Query
• Step 5: Create a Workflow in Dynamics 365 to Populate Account Number in URL Query
• Step 6: Let’s See a Result!
  

Assumptions:

• Before starting, we assume that we have already created a Power BI report with Dynamics 365 data source. If you are interested in learning how to create Power BI reports, we have great training courses available. To learn more about Dynamics 365 in general, click here. If you are interested in learning more about Dynamics 365, the link takes you to the information page. Finally, for your 30-day free trial, go to the registration page.
• The data source in this Power BI report is a Dynamics 365 for Sales Online version 9.1 – 30-Day Free Trial Sample Data.

Let us get started!

Step 1: Create a Power BI Report with Dynamics 365 Data Source


We created a Power BI report to display a specific account information of Dynamics 365 for Sales. It includes Account Name, Account Number, All Opportunities associated to the account, Actual Value, Estimated Value, and Closed Date. The report is created in Power BI Desktop.

**The image below shows all opportunity data with no account selected




Step 2: Publish the Report to Power BI Service


By clicking on the Publish button in the screenshot above, we have published the Power BI report from Power BI Desktop to Power BI Service. Once it is successful and the following message appears, click Open ‘POC URL Query.pbix’ in Power BI to view the report.




Step 3: Create Two Fields in Dynamics 365 Account Form


We are going to use the Account Number field in the Account entity in Dynamics 365. We could achieve the same result by using the Account ID (GUID) field – however, one benefit of using Account Number is that it is a common field between Dynamics 365 CE and the Finance and Operations module. Therefore, we can use the Account Number field to associate between the two applications and create a data model in Power BI.

The Account Number field is available in the Account entity, although by default it is not displayed in the Account main form. Therefore, we need to add the field to the form. We will also create a custom field in the account form to store the “URL Query.”

Adding Account Number field:

1. Login to Dynamics 365 for Sales and open an account form (for purposes of the demonstration, we are going to use the Proseware, Inc. sample account).



2. Click on FORM as shown above. (Note that if you don’t see FORM as an option, contact your System Admin for permission.)

3. The Account Form Designer appears. Click the Account Number field and drag-and-drop it under the Account Name field (labeled as 1 in the screenshot below).



4. Click Save (2).

5. Click Publish (3).

6. Click Save and Close (4).

7. Refresh your browser to see the Account Number field in the form.




Adding URL Query field:

1. Click FORM > New Field.

2. In the properties window, let’s set the values as:

• Display Name: PBI Report
• Field Requirement: Optional
• Name: new_pbireport (note that the prefix will depend on your publisher prefix)
• Auditing: Enable
• Data Type: Single Line of Text
• Field Type: Simple
• Format: URL
• Maximum Length: 200
• IME Mode: Auto
  

3. Click Save and Close.

4. Refresh the web browser of the Account Form Designer (hit F5 key).




5. Select Custom Fields from the Filter dropdown (labeled 1 in the screenshot above).

6. Drag-and-drop PBI Report under the Website field (2.)

7. Click Save (3).

8. Click Publish (4).

9. Click Save and Close (5).

10. Refresh your browser and see the PBI Report field in the form.




Step 4: Create a URL Query


Next, we will extract and create a URL query string from Power BI Service. Let’s open Notepad to save the URL for later use.

1. Go to Power BI Service and open the report page published way back in Steps 1 and 2.

2. If we have more than one page in the report, it is important to select the report page on which we plan to display by default.

3. Copy the URL and paste it into Notepad.


In our case, this is what should be pasted into Notepad: https://app.powerbi.com/groups/me/re...8e442abc0c790a

4. Update the URL in Notepad by adding ?filter=accounts/accountnumber eq ” immediately following URL – with no space! Note that those are two single quotation marks at the end – not one double quotation mark. This is important, as you’ll see in Step 5! It should now look like this:

https://app.powerbi.com/groups/me/re...8e442abc0c790a?filter=accounts/accountnumber eq ”

Step 5: Create a Workflow in Dynamics 365 to Populate Account Number in URL Query


1. Go to Settings > Processes in Dynamics 365.

2. Click +NEW to create a workflow.

3. In the properties window, let’s set the values as:

• Process Name: Create URL Query for PBI Report
• Activate As: Process
• Entity: Account
• Category: Workflow
• Scope: Organization
• Make sure each of these is checked:
  • As an on-demand process
  • Automatically delete completed workflow jobs (to save disk space)
  • Record is created
  • Record fields change for Account Number field

4. For Add Step, click Update Record.

5. Select Account for entity.

6. Click Set Properties.

7. Copy and paste the URL Query (from Step 4) to the PBI Report field (see below).

8. Under Operator > Look for, select Account and Account Number. Click Add, as shown:


9. Before clicking OK, it is important to set the cursor between the single quotation marks of the URL Query and add the Account Number parameter between them. Account Number parameter = {Account Number(Account)}. The URL should now look like this:

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



10. Click Save and Close.

11. Click Activate.

12. Click Save and Close on the workflow window.

Step 6: Let’s See a Result!


First, we need to run the workflow we created in the previous step and populate the URL Query with account number for an account. Recall that we are using Proseware, Inc. for this demo.

1. In Dynamics 365 for Sales, open the account form of Proseware, Inc. Note that there is no URL Query created in the PBI Report field yet.



2. Click on the ellipses … next to PROCESS in the Command bar.

3. Click Run Workflow.

4. Select the workflow we created in Step 5 and click Add > OK.



5. Let a few seconds pass and refresh the browser of the Proseware, Inc. account form.




The URL Query link is populated!!! For other accounts, we can also run the same workflow from the All Accounts view by selecting all the accounts at the same time.

6. Let’s click on the link in the PBI Report field.

7. The Power BI Service window appears with the account number filtered for the Proseware, Inc.

• Account Name is Proseware, Inc.
• The Account number slicer shows only the Proseware, Inc. account number (BBA38GH7).
• In the Report level filters section, the accountnumber is set as BBA38GH7.
  

We’re done! This has been just one example of Power BI and Dynamics 365 integration by URL Query with no coding. It’s slick.

Happy Power BI’ing & D365’ing!

The post Power BI URL Query in Dynamics 365 for Customer Engagement (CE) appeared first on Microsoft Dynamics 365 | PowerObjects.



Источник: https://www.powerobjects.com/2019/01...-bi-url-query/