Integrate Power BI in D365: Open Power BI Reports with Record Filtering

by

In this blog post, we will explore how to integrate Dynamics CRM with Power BI by creating a web resource that opens a Power BI report and applies record filtering based on the current CRM record. Prerequisite for this is a already in PowerBI Service hosted Report.

The Script

<!DOCTYPE html>
<html>
<head>

</head>
<body>
<script>
window.onload = function() {
  var cur_recordid = parent.Xrm.Page.data.entity.getId();
  var result = cur_recordid.replace("{", "").replace("}", ""); // Remove curly braces from the record ID
  var pbi_string = "https://app.powerbi.com/groups/me/reports/YOUR_GUID/ReportSection?filter=msdyncrm_marketingformsubmission/msdyncrm_marketingformid eq '" + result + "'";
  
  var btn = document.createElement("button");
  btn.innerHTML = "View in PowerBI";
  btn.addEventListener("click", function() {
    window.open(pbi_string);
  });
  
  document.body.appendChild(btn);
};
</script>
</body>
</html>

The HTML code is creating a button that, when clicked, opens a Power BI report and applies a pre-filter based on the current record in Dynamics CRM. The script retrieves the current record’s ID from the CRM form, constructs a Power BI URL with the filter parameter, and opens that URL in a new window when the button is clicked. It was initially deployed on the Marketingform Entity where it reads the users current record and prefilters a PowerBI Report on click of the button.

Step-By-Step

Step 1: Create the HTML Web Resource Create an HTML file (e.g., “open_powerbi_report.html”) that contains the necessary script to achieve the desired functionality. This file will be uploaded as a web resource in Dynamics CRM.

Step 2: Retrieve the Current Record ID In the JavaScript code within the HTML file, use the parent.Xrm.Page.data.entity.getId() method to fetch the ID of the current CRM record. This ID will be used to construct the filter criteria for the Power BI report.

Step 3: Construct the Power BI URL with Filtering Based on the Power BI report you want to open, create a URL that includes the report’s unique identifier and the desired filter criteria. Use the retrieved CRM record ID to dynamically generate the filter parameter in the URL.

Step 4: Create the Button and Event Listener Using JavaScript DOM manipulation, create a button element and set its properties such as innerHTML and event listener. When the button is clicked, it will open a new window or tab and load the Power BI report URL constructed in the previous step.

Step 5: Upload the HTML Web Resource In the Dynamics CRM environment, navigate to the Web Resource section and upload the HTML file created in Step 2. Make sure to publish the changes to make the web resource available for use.

Step 6: Add the Web Resource to CRM Forms Edit the CRM form where you want to display the button for opening the Power BI report. Add the web resource to the desired section of the form and configure its properties, such as positioning and visibility.

Step 7: Test and Refine Save the changes made to the CRM form and navigate to a record to test the functionality. Clicking the button should open the Power BI report in a new window/tab with the applied record filter.

Conclusion

Integrating Dynamics CRM with Power BI provides users with a unified platform for managing customer relationships and visualizing data insights. By creating a web resource in CRM that opens Power BI reports with pre-filtering based on the current record, users can seamlessly transition between the two systems, gaining real-time insights specific to the record they are working on.

Remember to customize the code provided in this blog post to match your specific Dynamics CRM and Power BI environment, ensuring the correct URLs, GUIDs, and entity names are used.

Leave a comment