Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications

|

Drill into CRM records from Power BI

Post Author: Joe D365 |

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'

records from Power BI

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

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

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

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

records from Power BI

You should now see a field with your url

Click 'Close and Apply'

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'

records from Power BI

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

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'

records from Power BI

Happy Power BI'ing!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

PowerObjects Recommends