Power BI with Dynamics 365 for Sales: Custom URL Linking and Advanced Drill-Back to CRM Records

Technology is evolving and transforming all functions of the organizational ecosystem, and it is imperative businesses execute a plan to implement systems, platforms, and analytics that are resilient and scalable. Microsoft Dynamics 365 (“D365”) Apps, including embedded intelligence and machine learning, are only a few solutions that are having a dramatic and positive impact on business’s ability to thrive in this accelerated environment. Marrying D365 apps with PowerApps, Power BI, and Flow (otherwise known as the Microsoft Power Platform), organizations can be optimistic they will stay relevant well into the future. D365 and the Microsoft Power Platform will shape the analytical landscape and advance our strategic business and operational decisions. One of the common client asks that can be achieved with the Power Platform is the ability use Power BI with Dynamics 365 for Sales to drill back to specific  forms, views, or dashboards (note, these concepts can also be applied to CRM) using a URL.

The Analytics Scenario

Power BI is becoming the de facto reporting tool for Dynamics 365, and I have had the opportunity to be the key architect and developer on several enterprise deployments of D365 and Power Platform solutions. In the past, I programmed SQL Server Reporting Services (SSRS) to handle the more complicated features. One of these being the ability to drill into D365 or CRM records from an SSRS report. This can be very opportune for end users to easily modify or quickly take action on a record. Previously, I had accomplished this with a custom expression in the drill action of a text box.

Now, it is a quite common that I am asked whether users can construct a URL to access a specific D365 for Sales (CRM) form, view or dashboard from within Power BI reports. Learning outcomes are:

1.    Uncover the concepts around accessing D365 for Sales elements using a URL

2.    Extracting data entities from D365 for Sales

3.    Developing a dynamic drill-back URL utilizing the transformation engine (Query Editor) that can enable business users to navigate from BI analysis to D365 for Sales

In this first article, I am going to describe the elements of the URL for linking to entity forms. I will follow this with another article covering linking to views and dashboards, and using PowerApps to perform everything read here and more. 

Linking Power BI with Dynamics 365

Loading Entity Forms

• For On-premises it would be something like https://your_server/your_org
  • If your data is On-premises and you publish to the Power BI service, your deployment must be IFD (Internet Facing Deployment) in order to launch the record.
• For Online it would be something like https://<<your_org>>.crm.dynamics.com

To navigate to a view for accounts with an id of %7b61273827-328E-E011-95AE-00155D9CFA03%7d use the following:

<<Your Connection String>>/main.aspx?etn=account&pagetype=entitylist&viewid=%7b61273827-328E-E011-95AE-00155D9CFA03%7d&viewtype=1039&navbar=off&cmdbar=false

My solution uses the syntax “/main.aspx?etn=account&….”. However, when you copy a URL in D365 you may also see etc used in place of etn. So “/main.aspx?etc=1&….”. Etc is the numeric code representing an entity. Whilst this may typically work for system entities, I have encountered many issues with custom entities, as the numeric code can be different in different organizations. Therefore, it is best practice to always reference entities by name using “etn”.

Extracting the URL Needed to Drill Back to D365 for Sales

We are going to begin in D365 for Sales to extract the URL elements needed to complete our drill-back and link your report in Power BI with Dynamics 365.

• Navigate to a D365 for Sales activity (any activity will work). We are going to use the URL from this page to create the link.

• Grab the id from the URL. Notice that the ID for the view is the one at the end. Do not copy the solution ID or entity ID.
• Create a query that includes the ‘Id’ field of the entity. In my example, I am reporting on Activities, so along with other fields I have selected ‘activityid’.

This can also be achieved by popping out the activity details from within D365 for Sales activities detail.

• Copy and paste the follow URL into notepad++:

https://<<your_org>>.crm.dynamics.com/main.aspx?pagetype=entityrecord&etn=<<activitytypeid>> &id=%7b<<activityid>>%7d

The highlighted parts will need to be substituted:

1. << your_org >> = Organizations base URL information
2. << activitytypecode >> = ETN – The Entity type code of your
3. << activityid >> = The GUID of the record – dynamically supplied by the id field in the query << activityid >>

Copy this into notepad and save it as we will be using it in the next section.

Setting up Your Power BI Report and Connecting to the D365 for Sales Environment

With the D365 for Sales URL in a notepad, navigate to Power BI and use the Query Editor to consume the OData entities available in D365.

• From a new Power BI file, click on Get Data > OData Feed.
• Copy and paste the organizations D365 URL.
• Add the OData suffix required to query the D365 for Sales database (api/data/v8.0). To find the full web API key, we can navigate to the D365 homepage and drop down and follow Settings > Customization > Developer Resources and copy the Instance Web API key.
• When prompted, sign in using and Organizational e-mail (D365 is based on OAuth and requires an organizational login).
• After successful sign-in, click ‘Connect’ to start loading the list of data entities accessible from the D365 OData feed (this will take a moment).
• Connect to OData and search for the Activities (activitypointers) data entity.

• Select Edit to bring the data entity into the Query Editor.

Creating the Drillback Field Using Column From Examples

From the Query Editor, we are going to add the drill-back column by using Power BI’s Column From Examples.

• With the URL we extracted at the beginning of this article to notepad, replace the etn= and id= with the first rows activityid and activitytypecode in the first row of the data set (see below).

https://<<your_org>>.crm.dynamics.com/main.aspx?pagetype=entityrecord&etn=<<activitytypeid>> &id=%7b<<activityid>>%7d

• While in the query editor, select Add Column > Column From Examples > From Selection.
• Paste the modified URL into the column from examples and press enter.
• Note something amazing happens when using Column From Examples. Pasting the URL and pressing enter should automatically identify the fields to concatenate, and the function will fill down without having to write a line of code. I have highlighted in a red box the formula that it creates.
• Another way to accomplish this is to click Add Column > Custom Column and type the paste the formula below.

“https://<<your_org>>.crm.dynamics.com/main.aspx?pagetype=entityrecord&etn=”& [activitytypecode] & “&id=%7b”& [activity]& “%7d”

• Rename the query Activities by double clicking the name, and Close & Apply the query to add it to the model.

Modelling and Visualizing the Data

Close & Apply and Declare the Data Category

After extracting and transforming data utilizing the powerful Query Editor, the final steps will be to load the Activities data to the model and begin developing the business logic.

• Once it has successfully loaded the Activities data, navigate to the Data tab, Modeling > Data Catgory > Web URL.
• Navigate to the report tab and begin curating the report using the DrillBackURL and Description. Use the statecode and statuscode to determine high priority items. I have placed translations beside. Here are a few resources for developers interested in learning more about custom entities and global option sets.

Bonus: Additional activities which I will perform are to create dimensions within my query editor and merge them with the Activities table.

Save and Publish Your Power BI Report

Looking incredible! Now we can save the report, and (optionally) publish to the organization for other users to view. Congratulations – an action oriented Activities report linking Power BI with Dynamics 365 for Sales with dynamic drill back to specific records is now available for proactive decision making.

Learn more about Power BI and all its features here.