Leverage the power of Power BI with Dynamics CRM data

Yesterday I attended a presentation from felow MVP Sophie Marchand about Power BI. I had previous exposure to Power BI when it became generally available for free as part of the Office 365 suite. The content of the presentation was around multiple areas in Power BI, so I thought I’d revisit some of them and put some thought into how we could leverage the power of the Business Intelligence platform to manipulate and play with Dynamics CRM data.

Connecting Dynamics CRM Online to Power BI (web)

This area is well documented. When you create or log on to you Power BI account, you can get Dynamics CRM Online data by going to the “Get Data” section, selecting “Services” and finding the Microsoft Dynamics CRM content pack.

image

Once you click on Connect, you will need to provide your Dynamics CRM Online OData Service URL (make sure you don’t leave a trailing slash in the end).

image

Once this process is completed, Power BI will connect to your Dynamics CRM Online Organization, get a set of data and build a Sales Manager Dashboard. That dashboard is pre-configured as part of the native integration between CRM Online and Power BI. Keep in mind that you can also connect to CRM Online from Power BI for Desktop. The way the data is structured is a bit different and you don’t get the Sales Manager Dashboard by default. That being said, it bring the data in using the OData Service and gives you the ability to create your own visualizations and reports. Let’s take it a step further and see what options you have if you need to manipulate data from custom entities and create visuals, dashboards that are useful for your business.

Custom Dynamics CRM data sources for Power BI

If you want to leverage Power BI, which you should in many ways, there are other ways to connect it to your CRM data. Here are some of the options that you have.

Extract Dynamics CRM in Excel Spreadsheets and Import the files to Power BI

Power BI (web)

image

Power BI for Desktop

image

For CRM On-Premise organizations, using Power BI for Desktop, you can simply connect to your CRM Database start creating for dimensions, cubes and reports from there. If you need more information on how to get started with Power BI for Desktop, make sure you check out Microsoft’s article here.

image

Worth noting, you can export your Power BI for Desktop content and upload it to the Power BI web platform. This gives you the ability to view the dashboards online, question it differently and also share it with people in your organization. In the Dynamics CRM and Microsoft ecosystem, it has never been easier to build reports and intelligence around your operational data. Enjoy!

Advertisements

SSRS: How to open CRM Forms from Custom Reports

Just thought I would share how to add a URL in your SSRS reports to open a related CRM record. I spend a little bit of time figuring this out so here it is. I will skip the CRM/SSRS reporting 101 so if you are not familiar with building reports for CRM, you may want to familiarize yourself with basic concepts before you dive into this.

  1. Add a parameter to your report and make sure it prompts for the URL:

    This will return an URL that looks like this: http://YourOrganizationURL/CRMReports/viewer/drillopen.aspx?

  2. Now you can add the URL as an action when some of your elements are clicked. In my example, I have a few columns that when clicking on the value, will open a case, its owner or its customer.

    To do that, I select the column in the table that contains the data and head to the “Action” table; Select “Go to URL” as the action and then click on the function button to setup the URL.

  3. It’s time to use the URL parameter that was defined at step 1 that looks like this http://YourOrganizationURL/CRMReports/viewer/drillopen.aspx. To create a valid URL from here, we have 2 options that I know of… We can
  • Play around with VB to manipulate the String value and remove the part after the Organization URL highlighted in yellow above; from there we build the record URL as per Microsoft Dynamics CRM URL Patterns. It shouldn’t be complicated but I have not tried it.
  • Use a CRM\SSRS report built-in functionality that allows to simple pass the ID and logical name of the record we want to open to the URL received in parameter. It is able redirect to the appropriate form magically.

Obviously, I went with the 2nd option. You expression will look like this: Parameters!CRM_URL.Value & “?ID={” & Fields!incidentid.Value & “}&LogicalName=incident”)

Parameters!CRM_URL.Value : This is the parameter created in step 1
Fields!incidentid.Value: This is the ID of the record you want to navigate to
LogicalName=incident : This is the logical name of the record you want to valigate to


And just as a proof (although this really doesn’t prove anything!) J


And that’s it! It’s magical. Key takeaway here is the fact that if you ask CRM for the URL as a parameter to your report, you get a URL and you then have the flexibility to play around with it and create links to any page you need. The other takeaway is the functionality built in the report to open a record’s form by simple receiving the ID and logical name as parameters. Hope this saves some time for someone one day.