Drill into CRM records from Power BI

work flows 300x225 Drill into CRM records from Power BI

Power BI is becoming more and more the de facto reporting tool for Dynamics 365, which we are very excited about. SQL Reporting Services remains relevant and offers features that many users will continue to leverage and enjoy regardless of the reporting tool. One of these is the ability to drill into a CRM record from a CRM SSRS report. This can be very convenient if you need to modify the record quickly and readily. We’ve been doing this for years with a custom expression in the drill action of a text box.

This can also be accomplished in Power BI in a very similar way. And it works for both Online and On-Premises.
Note: 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.
It is also worth noting that an oft-requested feature to enable drill-thru to a report within Power BI has been demoed as a coming soon feature. Although awesome, that is different functionality.

Let’s go through the steps to allow drilling into a CRM record from our Power BI table.

Create a query that includes the ‘Id’ field of the entity. In my example, I am reporting on Cases, so along with other fields I have selected ‘incidentid’

062717 2021 Drillthroug1 Drill into CRM records from Power BI

Navigate to an entity record and click the ‘pop-out’. This will relaunch your record and provide the complete url.

062717 2021 Drillthroug2 Drill into CRM records from Power BI

Copy and paste into notepad. What is important are the highlighted parts

  1. Your org information
  2. The Object or Entity type code of your entity (use could also use the LogicalName=incident”)
  3. The GUID of the record – dynamically supplied by the id field in the query

062717 2021 Drillthroug3 Drill into CRM records from Power BI

Return to further edit your query. Select to create a ‘Custom Column’

062717 2021 Drillthroug4 Drill into CRM records from Power BI

Build out your concatenation partly by pasting from your url you pasted into notepad and partly using available columns. Use the ampersand as the connecting operator. Be sure to put double-quotes around text (“xxx”)
I named the column: URL

062717 2021 Drillthroug5 Drill into CRM records from Power BI

You should now see a field with your url

Click ‘Close and Apply’

062717 2021 Drillthroug6 Drill into CRM records from Power BI

Back in the Power BI Desktop Designer, click on the data view.
Select your ‘URL’ column and change the Data Category to ‘Web URL’

062717 2021 Drillthroug7 Drill into CRM records from Power BI

Add the column in a table and it will be a clickable link that launches the actual record!

062717 2021 Drillthroug8 Drill into CRM records from Power BI

That’s awesome, but the long url is unattractive. We can fix that.

  1. Select the table
  2. Click on the editing paint roller
  3. Expand the values section
  4. Toggle the url icon selection to ‘On’

062717 2021 Drillthroug9 Drill into CRM records from Power BI

Happy Power BI’ing!

Let’s block ads! (Why?)

PowerObjects- Bringing Focus to Dynamics CRM