Get Data: Error Connecting PowerBI to the CRM Online OData Service

PowerBI in Office 365 can be a really useful tool, however many users may have come up against an issue with using the MS Dynamics CRM Online OData Service to “Get Data”.

You can easily register to use Power BI for your Office 365 tenant and download PowerBI Desktop, however it doesn’t appear to be as easy to use the “Get Data” function or register CRM as a data source using the OData Service for CRM Online.

If you a looking for a walkthrough on the set up of PowerBI, then this blog post from Steve Ivie is a useful place to start; https://www.packtpub.com/books/content/getting-dynamics-crm-2015-data-power-bi

The Problem

If like me you attempt to connect to CRM using the OData Service URL found in CRM Online then you might have used the following steps;

  1. Download and Open Power BI, from the pop-up menu select Get Data
  2. Select Dynamics CRM Online for the list and then click Connect
    GetData
  3. You are then prompted for the OData Service URL in the following dialog
  4. Now you would expect to enter the URL for the OData Service from CRM Online right? The format should be something like this; https://<your org>.crm5.dynamics.com/XRMServices/2011/OrganizationData.svcPlease Note: the URL ‘crm5’ section will vary depending on your datacentre. To find the OData Service URL navigate to; Settings > Customizations > Developer Resources in CRM.
  5. Once you have entered the URL click OK, the organisations available data sets should begin to load
    Odataentered
  6. In the Navigator menu select a data set or data sets from the CRM list and then click Load, (in the example AccountSet and ContactSet have been selected)
    powerbinavigator
  7. This is where the frustration starts! The load process is triggered and I receive the following message; OLE DB or ODBC error: [DataSource NotFound] OData Request failed (404) Not Found.At this point you cannot apply any changes and you have not data to work with. Close the message box.
    powerbierror

The Work Around*

Now, I researched the problem and couldn’t find a resolution for CRM 2015 Online, however if you have Microsoft Dynamics CRM 2016 Online your PowerBi dreams are but a URL away for fulfillment – *note I didn’t call this a “solution”, I will take answers on a postcard (or comment) from anyone who managed to get the OData Service working.

  1. In CRM Online 2016 navigate to; Settings > Customizations > Developer Resources
  2. Grab the Instance Web API from the field;
    crmapis.jpg
  3. Repeat steps 1-3 from the previous section
  4. Instead of the OData URL, enter the Instance Web API from CRM Online and click OK
  5. Once you have connected to CRM select the entity tables you want to work with in the Navigator menu and click Load
    powerbiload
  6. The data sets should now load and the glorious world of CRM with PowerBI is yours to explore at last!

Hopefully this will save some people from trawling support threads, if there is a better way or a way you have managed to use the OData Service for CRM Online 2015 or 2016 please get in touch!

Happy CRM’ing for now. 🙂

 

Advertisements

Add Custom Controls in CRM Online

System Administrators can add Custom Controls in MS Dynamics CRM Online with Update 1 for 2016. There a 15 new controls available to use for the CRM Phone and Tablet App.

In my previous post I talked briefly about the ability to add Custom Controls for use with the MS Dynamics CRM Phone and Tablet app’. In this post I will quickly show you how to locate and use these controls.

Before we get started, if you are working in solutions, you may want to create a new one, if so navigate to; Settings > Solutions > and click “New” to add a solution in CRM. Then add the Lead entity as used in this example (or the entity you wish to work with).

Please Note: With CRM 2016 you will need to select the assets to include in your solution, you can choose to “include all assets” if you wish to.

To add a custom control for an option set in the Lead form, please use the following navigation steps;

  1. Navigate to the lead entity in your solution (see above).
  2. Open the main “Lead” form, or alternatively navigate to the field list associated to the entity.
  3. Double-click the field or list you want to add the controls to; in this example we have used the “Preferred” field (preferredcontactmethodcode).

    Leadform1

  4. Click the Controls tab, then Add Control.

    leadfiedlproperties.jpg

  5. Select the control you want to use, (in this example – option set) and then click Add.

    leadoscontrols.jpg

    Please Note: Different controls are available depending on the field or list type. For example, slider controls might only be available for numerical or money fields.

  6. Select the devices you want the control to appear on (i.e. phone, tablet or both), then click “OK”. Alternatively you can choose to “Cancel” the configuration changes.

    leadaddcontrols.jpg

    Please Note: Controls are not available for the phone header at this time. There may be additional steps to configure values based on the type of control.

  7. Save the form and click Publish.
  8. You should now be able to check your changes in the CRM Mobile App.  Sign in to the app on your device , there should be a notification when you connect to your organisation, (like the one below). Then click Download.

    synccontrols

Navigate to a lead record and you should see the field displayed using the new custom control selected as per the example from the mobile app’ below.

leadcontrolsmob

Mobile Custom Controls in CRM Online

In CRM 2016 Online Update 1 there have been a number of enhancements to the Dynamics CRM App, some of the most notable for their impact on user experience are Custom Controls.

So what is the idea? Well that’s simple enough;

  • Custom controls can expedite the entry of data using the mobile interface
  • Further aligns the user experience with the use of touch devices
  • Provide immediate visual references for CRM data
  • Reduce the need for custom development

What does that mean? Basically the enhancements help to make the native CRM Tablet and Phone app’ more appealing to the user. Along with offline synchronisation, the additional functions can further help your organisation to ‘configure once and deploy anywhere’ with Dynamics CRM for Phones and Tablets.

There are 15 custom controls available in this release, though currently the controls are for the Mobile/Tablet client only but are surfaced as regular Xrm.Page.ui control objects and are configurable in the compatible form fields of an entity.

Different controls are available depending on the field or list type. For example, slider controls might only be available for numerical or money fields, and the calendar control is only available for lists. Controls are configured via the form editor.

crmapp

Custom Controls as viewed in the Opportunity form

  • This current set of custom controls includes;

Linear Slider
Liner Gauge
Number Input
Radial Knob
Arc Knob
Flip Switch
Option Set
Star Rating
Website Preview

There is additionally a control for video embedding, and a nifty calendar control that you can add to give users a view of their activities in a calendar format instead of the more traditional CRM list.

So let’s have a quick look for a high level at some of what is available:

 

Option Controls

Option Set: Used for visual representations of relatively short option sets. Clearly view selected and other options without entering the field’s edit mode. To change, simply tap on the new value. Slider

Flip Switch: Displays as a traditional two-option field.
flipswitch

Numerical Input

Linear Slider: Used for numeric entry, with the exception that the numeric entry from a mobile keyboard is not possible here.

Slider

Linear Gauge: Drag values across a preset range or tap ahead to jump to a preconfigured step. Users can also enter data using their mobile keyboard when using this control.

gauge.jpg

Radial Knob: This is an excellent control for showing numeric values within a circular presentation. Data entry is completed by turning the knob to dial in a value. The range is fully configurable. An excellent addition to a touch interface.

radial.jpg

Arc Knob: The arc knob is another great way to track progress visually in the application, this could be applied to the probability of an opportunity or a percentage.

arc.jpg

Star Rating: Used to replace the entry of a whole number on a scale of 0 to 5. Simple yet effective way to view a rating. Could be easily applied to lead/opportunity rating and feedback reviews etc.

rating.jpg

Calendar Control

This control can be used on dashboard, entity list view, or as a sub-grid within another form to enable users to view their appointments or tasks in a familiar format. It could be used to replace the traditional activity list. It is only available for tablet and phone users and can be enabled in the Activities area.

Control Compatibility

At the time of writing the custom controls available are compatible with the following field types:

customcontrolstable

Client Side Reference for Custom Controls

You access controls using the following collections:

  • Xrm.Page.ui.controls
  • Xrm.Page.ui Section.controls
  • Xrm.Page.data.entity Attribute.controls

When a form displays a business process flow control in the header, additional controls will be added for each attribute that is displayed in the business process flow. These controls have a unique name similar to the following example: header_process_<attribute name>. For more information on client side references refer to the following MSDN post; https://msdn.microsoft.com/en-us/library/gg334266.aspx

If you don’t currently have the latest version of CRM Online you can get access to all 15 custom controls using a trial instance.

Remember; these Custom Controls are not supported in the CRM web application.

For a full list of enhancements in CRM 2016 Update 1, you can refer to the following TechNet post: https://technet.microsoft.com/en-us/library/dn531078.aspx

Happy CRM’ing. 🙂