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’
Navigate to an entity record and click the ‘pop-out’. This will relaunch your record and provide the complete url.
Copy and paste into notepad. What is important are the highlighted parts
- Your org information
- The Object or Entity type code of your entity (use could also use the LogicalName=incident”)
- The GUID of the record – dynamically supplied by the id field in the query
Return to further edit your query. Select to create a ‘Custom Column’
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
You should now see a field with your url
Click ‘Close and Apply’
Back in the Power BI Desktop Designer, click on the data view.
Select your ‘URL’ column and change the Data Category to ‘Web URL’
Add the column in a table and it will be a clickable link that launches the actual record!
That’s awesome, but the long url is unattractive. We can fix that.
- Select the table
- Click on the editing paint roller
- Expand the values section
- Toggle the url icon selection to ‘On’
Happy Power BI’ing!