As we come to an end of another year, the Christmas period provides an excellent opportunity to review what’s happened over the year and to plan effectively for what’s in store during the new year. My 2016 has been eventful, to say the least, presenting a good mixture of excitement, new challenges and learning experiences. 2017 looks to present a number of new opportunities for me to continue working with Dynamics CRM/Dynamics 365 for Enterprise (CRM/D365E), to continue developing the blog further with new content and for me to widen my knowledge more generally of Microsoft technologies in the cloud. Here’s my top 6 list of closing observations and thoughts for 2016:

You can’t learn everything about CRM/D365E, so don’t even bother trying

I have really enjoyed working with CRM/D365E over the past couple of years. As I’ve discussed previously on the blog, it gives those who have “dabbled” in various different Microsoft technologies an open and accessible canvas to work with a number of different technologies simultaneously. As a result, the sheer breadth of potential specialisations within CRM was staggering; with this increased tenfold following the introduction of D365E. There is no point, therefore, in needlessly stressing yourself out, attempting to become a CRM/D365E master. This is particularly true given that the rate of changes to the product mean that it is literally impossible to keep up. Instead of being frustrated, you should take joy and satisfaction at whenever you learn something nuanced or niche about the product and focus your study towards either a technical (i.e. developer) or functional (i.e. Sales, Service etc. areas of the product) specialist. There is plenty for you to get your teeth stuck into either way.

2016 has been the year for CRM/D365E

There have been a lot of developments this year with the product formerly known as Dynamics CRM. This blog has covered the vast majority of these changes over the past year, such as:

A lot to take in, I’m sure you’ll agree! And this seems unlikely to let up in 2017 either. We can look forward to the following next year:

  • The release of Dynamics 365 for Business, Microsoft’s successor product for CRM targeted at small to medium size businesses. There is currently scant information available regarding this product, so I am keen to find out more about this at the first available opportunity.
  • The (anticipated) release of brand new exams for Dynamics 365
  • An expected spring update to Dynamics 365 for Enterprise, which is likely to take the product to its next major version

Now is certainly the best time to be involved in working with CRM/D365E, with the enhanced opportunity for those working with the product to develop their skills further and get involved as part of various projects.

Don’t be afraid to make a big change

Opportunities will always present themselves – whether it’s learning about a new technology, getting involved as part of a project at work or even moving from your current role to an entirely different one. These should always be explored eagerly, even if you are daunted by the change that they may make to your current circumstances.

…but also don’t be afraid to admit if this was a mistake

Whenever we set out to make a major change – whether in our professional or personal life – we always have the best intentions on where we want to end up. This can often be borne out of a degree of frustration, which can be compounded further if this change does not deliver what we had hoped for. The important thing to remember with this is not to get angry but deal with the situation calmly, with control and take pressing action to get yourself in a place where you are happy. I am often reminded of one of Tony Robbin’s key messages – take massive action. Do whatever you feel is necessary to get you to the place where you want to be. And don’t be afraid to look backwards as part of this. Certainly, from a professional viewpoint, you should always be ensuring that you never burn you bridges with past colleagues/companies that you have worked with, judge any opportunity on its individual, unbiased merit and never be afraid to admit if you made the wrong decision. Instead, what you have done is had a significant learning experience that has helped to develop you further.

Your team is the most important thing about your job

A lot of studies point to the importance of a line manager in an employees happiness and engagement. Whilst this is undoubtedly important, I think this only tells half the story. What I have come to learn this year is the importance of having good and reliable team members surrounding you – including both colleagues who report to you and those who you report to. Having that almost instant rapport with other colleagues, in terms of how they approach daily tasks, sharing the same values as you and in covering your back, is the single most important thing about your role. If this is not present or not working within your current team, then you very much need to take massive action to get the team in the place where they need to be or start evaluating your options to extricate yourself away from the team. You should ask yourself the following questions when attempting  to determine whether your team is functional and effective or the complete opposite:

  • Do you colleagues communicate openly and honestly with you on a day to day basis?
  • Does your line manager give you the freedom and latitude to complete your job in the best way you see fit?
  • Is your line manager always approachable?
  • When things go wrong, how do your colleagues work towards a resolution? Do they instantly start pointing the finger or do they get actively involved in providing a resolution? What did your colleagues do (if anything) to prevent things from going wrong in the first place?
  • What is the team dynamic like? Is everyone in your team professional in how they conduct themselves?
  • Does everyone have a clear vision of what the team is working towards, and how this fits in with the wider business?

If the answer is no or nothing to most, if not all, of the above, then you should start to think carefully about how you can make a positive change to the working environment.

Looking ahead to 2017: The blog 

At the start of the year, I set myself a personal challenge: start a CRM focused blog and publish at least 1 post a week. So far, I think I’ve done a pretty good job meeting this challenge, although I obviously cannot comment on the quality of the content… 🙂 Whilst I am fully intending to continue posting in the New Year and the beyond, I am currently contemplating some changes to the blog. With the introduction of Dynamics 365, the blog name will soon become retro and outdated. So a new name will likely be required, and possibly a shift in focus towards a more general Microsoft technology focused blog. Watch this space for more info, but for those who have stuck around this year and are still reading, then thank you for your support! I hope I can continue to entertain you in some small fashion in 2017 as well.

Regardless of whether you are a new or existing visitor to the site, I wish you a very Merry Christmas and a Happy New Year!

Getting to grips with how to use Dynamics CRM/365 for Enterprise (D365E) is no easy feat. You can imagine just how difficult it is for an end user to get to grips with how the application works and functions; with more detailed knowledge around customisation and development being an entirely different ball game altogether. Compounding this problem further is the fact that the product has evolved at an increasingly more rapid pace over recent years, to the point where it is literally impossible to become a master of everything that you can do within CRM/D365E. Those venturing into the product for the first time may find their learning journey significantly simplified if they already have a good general knowledge about some of the underlying technology that powers CRM/D365E. This was certainly true in my case; I had a good background already in managing Office 365, writing SQL queries/reports and some experience with C#. This is all incredibly useful knowledge to have in your arsenal and is all directly applicable towards CRM/D365E in some way. For those who are getting to grips with the product for the first time, either without this previous experience or as part of an apprentice/graduate type role, your journey may not be as swift and issue-free. With this in mind, here’s my list of essential knowledge that you can add to your own “swiss army knife” of personal knowledge. Experience and good knowledge of these technologies will not only help you greatly in working with CRM/D365E, but present an excellent learning opportunity for Microsoft technologies more generally and something that you can add to your CV with pride:

SQL Server

What is it? : SQL Server is Microsoft’s proprietary database knowledge, based on the ANSI standard. SQL stands for Structured Query Language and is one the most widely used database programming languages on the planet.

Why Knowing It Is Useful: The underlying database technology that CRM/D365E uses is SQL Server, so having a general awareness of relational database systems work and how SQL Server differs from the standard goes a long way in understanding what is capable from a customisation/development viewpoint. For example, you can very quickly grasp which data types the application supports, as they will all ultimately be based on a supported SQL Server column data type. If you are running on-premise versions of CRM/D365E, then knowledge of SQL Server immediately moves from being a nice bonus to an essential requirement. This is because administrators will need to have good knowledge of how to manage their Dynamics CRM database instance, perform backups and also, potentially, write transact-SQL (T-SQL) queries against your database for reporting or diagnostic work.

Recommended Area of Study: Focusing your attention towards SQL Server Reporting Services (SSRS) report writing will benefit you the most. Through this, you can begin to establish good knowledge of how SQL Server databases work generally, and be in a position to write FetchXML for Online/On-Premise deployments of the application or Transact-SQL (T-SQL) based reports for On-Premise only. Having a good awareness of what is capable via a standard SQL query will also hold your good stead when working with FetchXML, as you can immediately make a number of assumptions about what is possible with FetchXML (for example, filtering results using an IN block containing multiple values and performing grouping/aggregate actions on datasets)

Office 365

What is it? : Office 365 is Microsofts primary – and perhaps most popular – cloud offering for businesses, individuals or home users. Through a wide array of different subscription offers, home and business users can “pick ‘n’ mix” the range of solutions they require – from Exchange-based email accounts to licenses for Microsoft Visio/Project, through to PowerBI.

Why Knowing It Is Useful: Although it is arguable that knowledge of Office 365 is not essential if you anticipate working with on-premises versions of the application, you may be doing yourself a disservice in the long term. Microsoft is increasingly incentivising organisations to move towards the equivalent cloud versions of their on-premise applications, meaning that as much knowledge as possible of how CRM/D365E Online works in the context of Office 365 is going to become increasingly more mandatory. If you are looking to secure a career change in the near future, and have not had much experience with Office 365, then this is definitely an area that you should focus on for future learning. From a day-to-day management point of view for the Online version of the product, some basic awareness of how to navigate around and use Office 365 is pretty much essential if you are going to succeed working with the product on a day-to-day basis.

Recommended Area of Study: Spin up a D365E trial, and you can very quickly start getting to grips with how the product sits within the Office 365 “ecosystem”. Practice licensing users, configuring security group level access to your D365E trial tenant and modify the details on Office 365 user accounts to see how these details are synced through into D365E. The Microsoft Virtual Academy also has a number of general courses related to Office 365 however, due to the frequent updates, it may not always be in-line with the current version. The official curriculum/certification paths for Office 365 may also suffer the same from this but are worthwhile in demonstrating your experience and ability to integrate D365E with the various related Office 365 services.

Active Directory

What is it? : For the rookie, intermediate and experienced IT admins, Active Directory needs no introduction. It is essentially Microsoft’s implementation of the Lightweight Directory Access Protocol (LDAP), having first being introduced in Windows Server 2000, providing a means of managing user, security and access rights for domain objects. There are now two distinct versions of Active Directory that are available – the more traditional, Windows server based, on-premise Active Directory and Azure Active Directory, which is utilised primarily by Office 365.

Why Knowing It Is Useful: User account records for both On-Premise and Online versions of CRM/D365E use Active Directory objects, with a number of important information synchronised between an Active Directory user and the equivalent User entity record. For example, as indicated in this MSDN article, the only way in which you can synchronise a user’s Job Title through to CRM/D365E is by updating the equivalent field on the Azure Active Directory. Active Directory objects are also the only way in which you can authenticate with the application via the Web Interface or other means, with no option to create a database user or other kind of authenticated user type.

Recommended Area of Study: It’s free to set up your own Azure Active Directory, so this is an excellent starting point for getting to grips with the technology. There’s also nothing preventing you from downloading a trial of Windows Server and installing the Active Directory server role on this machine. Once configured, you can then start to create users, update attributes, configure permissions and setup roles that contain collections of privileges. If you already have an Office 365 tenant with CRM/D365E Online, then you can use the Office 365 portal to manage your user accounts and test the synchronisation of attribute values from the Active Directory through to the application.


What is it? : A good way to remember what PowerShell is that it is essentially a blue command prompt window 🙂 . Traditionally only being relevant and important for those working extensively with Windows Server or Exchange, PowerShell is now increasingly important as part of administrating on-premise CRM/D365E, Office 365 and Azure, to name a few. Indeed, one of the major shock announcements this year was that PowerShell became open sourced and can be installed on Linux; representing the increasing demand and importance of Linux-based resources within the Microsoft cloud.

Why Knowing It is Useful: Similar to SQL Server, PowerShell is something that is instantly more applicable for on-premise CRM/D365E deployments. For example, the only way to modify the default number of Dashboard items is via executing the Get-CRMSetting cmdlet against your on-premise organisation. I would also, again, argue having a general awareness of PowerShell can help greatly when performing administration work against an Office 365 tenant that contains a CRM/D365E organisation, such as user provisioning or license assignment. If you are utilising the Azure Service Bus to integrate CRM/D365E for Azure-based applications, then PowerShell immediately becomes a desirable skill to have in your arsenal, allowing you to remotely administer, deploy or update Azure resources programmatically.

Recommended Area of Study: The fact that PowerShell is now open sourced means that there is a plethora of online tools and guides to refer to, and you can be assured that you can get it working on your platform of choice. The GitHub page for PowerShell is a great place to get started. Beyond that, you have a few options about how you can practice further. If you have spun up a D365E trial, then you can choose to hook up PowerShell to Office 365 to see what you can do from a remote management perspective (such as granting Send On Behalf permissions for a shared mailbox). Alternatively, you can run it from your local Windows machine, connect it up to a Windows Server instance or attempt to create new services in Azure and experiment that way.

The recent releases of Dynamics CRM have very much been missing something for developers who work with the product. Whilst a number of exciting new features have been added to the product – the Web API, Interactive Service Hub and Portals, to name a few – there very much feels to be a lack of attention towards surrounding support tools to give developers a head start in progressing their careers and facilitating more agile and efficient development of bespoke CRM solutions. Exams are one area that has been guilty of this, with no up to date exam for developers released for over 3 years. In today’s modern world, 3 years is a lifetime of change and can leave developers in a situation where they are not aware of the latest technologies and potentially developing solutions that will soon be deprecated or lead to additional development time and administrative headroom to manage.

With this in mind, it is pleasing to hear that an updated version of the Visual Studio Developer Toolkit will be released for Dynamics 365 and that a beta version of this tool is now available to be downloaded. For those who have not used the earlier version of this tool for Dynamics 2013 and earlier, it provides developers the mechanism to create Visual Studio project templates that can store all of your related development assets, facilitate the deployment of these assets to your CRM instance from within Visual Studio and allow you to generate early-bound class files of your CRM entities and the requisite template class files for your Plugins; enabling you to focus more on developing your business logic. I have some experience using the tool myself in a sparing manner and have found it somewhat cumbersome to use. For example, I have had issues where I have had to sign into a development CRM system every time the project loads and I have not found it straightforward to use alongside existing CRM solutions. The tool is also only compatible with Visual Studio 2012 and earlier, which means developers running the latest version of Visual Studio will miss out on being able to use the toolkit. Nevertheless, the ability to generate the required code for your plugins in a quick manner is a major boon and, with the new release, a major opportunity is present in order to improve the tool and resolve some of its glaring issues. I’ve been taking a look at the Beta release of the Dynamics 365 Toolkit, and here are some of the new features which I am most excited about and feel will be a big help to developers in the months ahead:

Templates for Mobile App Development

For most standard deployments, the out of the box Dynamics 365 for Tablet/Mobile app will be sufficient for your users. You could even look at developing a simple app using PowerApps. For more advanced scenarios, the SDK would be your next port of call in order to develop a mobile app that connects up with CRM/D365E. This is a popular approach to take when developing a mobile app with a specific function, as it enables you to leverage the full functionality of CRM/D365’s processes as part of your app, whilst ensuring that the data model conforms to a familiar, SQL Server-based model. The analogy I have used before with other developers is that developing for Dynamics CRM is like developing for SQL Server on steroids 🙂

Previously, there were some code examples included as part of the SDK in order to help developers “get started” with developing a Windows Store, iOS and/or Android app that connects to CRM. Now, the Dynamics 365 Toolkit includes a number of Project templates that help with developing a mobile, store and/or universal app:


The only caveat with these templates is that they are designed solely for Windows-based devices; for a truly cross-platform application, then you would need to look at utilising a tool like Xamarin in order to meet your requirements.

Configuring Paths to SDK/Plugin Registration Tool

This is a minor new feature, but something that is important and, arguably, essential if you are frequently developing new CRM/D365E developer solutions. Within the settings page for the toolkit, you can now specify the location for your SDK DLL’s and Plugin Registration tool; which will then be used across all of the projects you create in Visual Studio moving forward:


This is a huge time-saving step and removes a rather tedious task that is often involved when it comes to setting up your Visual Studio projects.

Configure Microsoft Dynamics 365 Solution Wizard

When you now create a new Dynamics 365 solution template in Visual Studio, you are greeted with a simple and clear wizard that helps facilitates the following scenarios:

  • Specification of a persistent connection to CRM/D365E, that can then be re-used across different projects within Visual Studio and provides a familiar UI experience with the existing tools within the SDK (such as the Plugin Registration Tool):




  • The ability to select or create a brand new solution from within Visual Studio (previously, you only had the option of selecting an existing solution):


  • Granular level control of which templates to include in your project – including Plugins, Custom Workflow Assemblies or Web Resources/Assets:



With all these changes put together, the process of setting up new projects that are targeting a single CRM environment is greatly simplified and you can ensure that your project contains just the assets that you need.

Why the Developers Toolkit is important

One of the major hurdles that developers generally face is having to deal with all of the stuff that is non-development related – setting up environments, installing development tools and resolving environment configuration issues, to name but a few. Whilst all of this is useful experience and gives developers a flavour for how a potential application deployment will look, it can often get in the way of a developer’s primary responsibility; to just code. This is one of the reasons why DevOps is becoming an increasingly more important area of focus for businesses who employ developers, as having an effective DevOps strategy, plan and resource will ensure greater productivity from your existing resource and help to foster an environment where projects are being run the “right” way. The Developers Toolkit is an important tool in your DevOps arsenal, as it works towards meeting all of the above objectives and begins to foster an approach where set standards are followed across multiple projects. It also helps take out the administrative effort often involved with, for example, setting up a Plugin class file manually within Visual Studio. Although the Dynamics 365 Developers Toolkit is still in beta, and not ready for Production use, I would very much hope to see a full release in the near future. Tools of this nature (such as XRMToolBox and the Ribbon Workbench) help to encourage greater efficiency, which is often essential for many IT projects these days.

I have had an opportunity recently to start getting to grips with the wonderful world of PowerBI. For those who have walked the tightrope between Excel and SQL Server Reporting Service (SSRS) Reports, PowerBI appears to be the tool with these individuals in mind. It enables you to leverage existing Excel knowledge (through PowerQuery or Excel-based formulas/calculations), whilst also offering a number of easy to setup Visualisations, that are not too dissimilar to the charts, maps and other objects that can be setup on a .rdl report. What’s also good to know, from a Dynamics CRM/365 for Enterprise (D365E) point of view, is that you can very quickly hook up your CRM data to a PowerBI dashboard/report. In addition to this, integrating with other data sources – such as SQL, JSON or flat file sources – or even with completely different application systems is possible (even with SalesForce – shock, horror!). In the days of increasing need for tight integration between a dazzling array of different application and database systems, PowerBI gives you a broad framework to achieve any reporting goal you may have in mind. Having said that, it is still rough around the edges and in need of some further development before it, arguably, becomes the de facto tool to use. For example, I have found some of the formatting and design options available to be rather basic and light-years behind what is possible with SSRS or even Excel. There are also some features missing that are somewhat baffling, such as the ability to send out dashboards/reports via email on a predefined schedule. I would hope that we see increasing attention towards PowerBI in the months and years ahead in order to bring the very best of features from these more traditional applications but exposed in a non-daunting and wholly accessible way.

As referenced above, getting set up with your Online CRM/D365E data is incredibly straightforward via the inbuilt Dynamics 365 connector “wizard” – simply login into your online organisation, specify the entity data that you want to work with and PowerBi will push this data into a table for you, enabling you to start building your report in seconds. The connector “wizard” is suited to most typical data retrieval scenarios, providing a GUI interface to visualise the entity data within your CRM/D365E instance and the ability to put together related entities and return them as part of your query. Before you start using it, however, I would highlight the following:

  • The OData feed is rather indiscriminate in its retrieval – all records from an entity will be returned. Some pre-filtering will occur based on CRM’s/D365E’s security model (e.g. if the account you log in as has Business Unit level Read privilege on the Lead entity, only records in the accounts Business Unit will be returned), but typically it will be System Administrators who set up a PowerBI Dashboard; therefore meaning you have to deal with a lot of records being returned into PowerBI. Given that the two different PowerBI plans have limitations in regards to record retrieval, this could cause problems if you are working with large CRM datasets.
  • Tied in with the above, because you have no way via the “wizard” interface to specify record retrievals, you cannot take advantage of filtering your data based on certain attributes or even take advantage of some of the native functionality within CRM to aggregate your data. Whilst PowerBI is certainly more than capable of doing this, relatively n00bish users may find this an unwelcome barrier that hinders adoption.
  • Lookup and Option Set attributes are returned as a special data type of Record – with the underlying properties of the related record (GUID, display name etc.) stored within this sub-record. Having the data stored in this manner causes additional administrative effort on the PowerBI side, as you will need to figure out how to access the underlying properties of this PowerBi data type.

Fortunately, if you are inclined towards building very specific and focused queries that you can execute against your Online CRM/D365E, there is a way – and the best thing is, we get to use something that has been recently introduced into CRM as well 🙂

The Web API to the rescue!

The Web API was introduced in Dynamics CRM 2016, which implements version 4 of the Open Data (OData) Protocol, and will eventually replace the traditional means that developers would use to access CRM via web services (namely, the Organization service and the old OData service). CRM Developers will need to start becoming increasingly familiar with the Web API in the years ahead, and no doubt a lot of work will need to be done updating existing coding to look at the new Web API.

Because the Web API is a web service, PowerBi can connect to it via the Web connector. By querying the Web API, you have access to all of the messages that are exposed to get data from CRM – Retrieve, Retrieve Multiple and Predefined Query – with multiple options available to use in terms of how you return, filter and aggregate your data. Results will be returned in JSON format, so there will be some additional work that needs to be done to get the data into an accessible format. This post will now take a look at what you need to do in order to return data based on a FetchXML query, as well as (hopefully!) providing a useful code snippet that you can adapt to your environment.

Before starting, ensure that you have installed the CRM Rest Builder Managed Solution within your CRM development environment. This tool allows you to quickly generate code snippets in JScript that perform web service calls into CRM/D365E and is a massive help in a number of different ways. A big shout out and thanks to Jason Lattimer for the work he has done on this.

  1. To begin, you need a FetchXML query that returns the data you need. This can be written manually or generated automatically via Advanced Find. In this example, we are going to use the following snippet that queries the Case entity, that will return 7 sample data records from CRM:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="incident">
    <attribute name="title" />
    <attribute name="ticketnumber" />
    <attribute name="createdon" />
    <attribute name="incidentid" />
    <attribute name="caseorigincode" />
    <attribute name="customerid" />
    <order attribute="ticketnumber" descending="false" />
    <filter type="and">
      <condition attribute="prioritycode" operator="eq" value="1" />
  1. Next, we need to generate the URL that will be used to query the Web API endpoint. There are two challenges here – the first being that the FetchXML query needs to be included in the URL and that it needs to be encoded so that it is a valid URL. The start of the URL is fairly straightforward to put together – it’s just your CRM organisation URL, in the following format:

https://<Organisation Name>.<Region Code>

So if your organisation name is crmchap and your CRM tenant is based in the EMEA region, then the URL would be as follows:

The rest of the URL can be obtained from the CRM Rest Builder. Open the Managed Solution, navigating to the Configuration page. It will look something like this:


Update the page so that the Action is set to Predefined Query. Ensure that the Primary Entity is set to Incident (always something you have to remember when working with the Case entity 🙂 ) and then copy and paste the FetchXML query into the box below. The window should look similar to the below once ready:

2Press Create Request to put together the code snippet. On Line 2, you will see the following piece of code:"GET", Xrm.Page.context.getClientUrl() + "/api/data/v8.0/incidents?fetchXml=%3Cfetch%20version%3D%221.0%22%20output-format%3D%22xml-platform%22%20mapping%3D%22logical%22%20distinct%3D%22false%22%3E%3Centity%20name%3D%22incident%22%3E%3Cattribute%20name%3D%22title%22%20%2F%3E%3Cattribute%20name%3D%22ticketnumber%22%20%2F%3E%3Cattribute%20name%3D%22createdon%22%20%2F%3E%3Cattribute%20name%3D%22incidentid%22%20%2F%3E%3Cattribute%20name%3D%22caseorigincode%22%20%2F%3E%3Cattribute%20name%3D%22description%22%20%2F%3E%3Cattribute%20name%3D%22customerid%22%20%2F%3E%3Corder%20attribute%3D%22ticketnumber%22%20descending%3D%22false%22%20%2F%3E%3Cfilter%20type%3D%22and%22%3E%3Ccondition%20attribute%3D%22prioritycode%22%20operator%3D%22eq%22%20value%3D%221%22%20%2F%3E%3C%2Ffilter%3E%3C%2Fentity%3E%3C%2Ffetch%3E"

The bit we are interested in is the string value after the Xrm.Page.context.getClientUrl() function, which will need to be appended to our CRM URL. So based on the above, our URL to use with PowerBI would be as follows:

A bit of a mouthful I agree!

  1. Now that we have the URL, we can connect up to CRM within PowerBI. Create or Open a new PBIX file and select Get Data -> Web:


  1. On the From Web window, copy and paste the URL we’ve built and press OK. You will be prompted to log into your CRM organisation; select Organisational account and log in as you would normally via the Office 365 login page. Once logged in, the data will be retrieved and the Query Editor will open, displaying something similar to the below:4
  2. Some additional work is required in order to get our data into a standard, tabular format. In addition, the data at the moment is returning the underlying Option Set and Lookup values from the Incident entity, as opposed to the Display Name; not good from a reporting point of view:

5We, therefore, need to modify the underlying PowerQuery in order to achieve the following:

Right click on Query1 and select Advanced Editor to open the underlying PowerQuery text. Delete everything here and then copy and paste the following into the window:

    //Get the CRM data, including the Formatted Values as part of the returned data
    Source = Json.Document(Web.Contents("", [Headers=[#"Prefer"="odata.include-annotations=""OData.Community.Display.V1.FormattedValue"""]])),
    //Get the underlying list of records returned
    values = Source[value],
    //Create a new table with one column, populated with the values list of records
    #"Table from List" = Table.FromList(values, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    //Query will error if no results, therefore use an if statement to build an empty table with the correct column headers
    Expand = if List.IsEmpty(values) then #table({"title", "ticketnumber", "createdon", "incidentid", "caseorigincode", "_customerid_value@OData.Community.Display.V1.FormattedValue"}, {"title", "ticketnumber", "createdon", "incidentid", "caseorigincode", "_customerid_value@OData.Community.Display.V1.FormattedValue"}) else Table.ExpandRecordColumn(#"Table from List", "Column1", {"title", "ticketnumber", "createdon", "incidentid", "caseorigincode", "_customerid_value@OData.Community.Display.V1.FormattedValue"}, {"title", "ticketnumber", "createdon", "incidentid", "caseorigincode", "_customerid_value@OData.Community.Display.V1.FormattedValue"}),
    //For some reason, if there are no results, then the empty table contains error records - so need to specifically remove them
    #"Removed Errors" = Table.RemoveRowsWithErrors(Expand),
    //Finally, rename the _customerid_value field
    #"Renamed Columns" = Table.RenameColumns(#"Removed Errors",{{"_customerid_value@OData.Community.Display.V1.FormattedValue", "Customer Name"}})

   #"Renamed Columns"

The comments should hopefully explain what the code is doing, but to summarise: the PowerQuery is parsing the JSON data into a tabular format, using the returned data to build column headers, and then renaming the _customerid_value field to match our requirements. There is also a logic statement in there to check if we actually have any data; and if not, then build an empty table (since JSON does not return anything that we can use if 0 records are returned).

With our updated PowerQuery, our result set should look similar to the below:


Nice, presentable, filtered and exactly what we need to start building our PowerBI report! 🙂

Conclusions or Wot I Think

Whilst I’ll be the first to admit the above example is rather code-heavy and would require significant tinkering to suit specific scenarios, I would argue that the approach demonstrated adheres most closely to some of the common rules when it comes to querying data for reports:

  • Only return the columns you need
  • Filter your data to reduce the number of results returned
  • Ensure that your report-side columns are giving presentable database column values

This approach is the only one adheres most closely to the above and, therefore, I feel that the extra effort is warranted; particularly when it means that those who are more familiar with CRM can stick to the tools they know best. As the default Dynamics 365 connector uses the Organization Data service, I would imagine that eventually this will be updated to use the new Web API instead. I hope that when this happens, we can begin to achieve all of the above via the PowerBI interface, as opposed to resorting to code.

Your business has decided it wants to implement Dynamics CRM/Dynamics 365 for Enterprise (D365E). That’s great news! Your next crucial decision will be what approach you take in implementing the system, in particular, who you involve as part of the planning, designing and building phases. Businesses that lack individuals with dedicated, technical know-how will often need to engage the services of a Microsoft Partner or consultant to assist with some or all of this. On the flip-side, if you have a capable and technically competent team that is able to get up and running with how to use the system quickly, then there is a case to be made for deploying CRM/D365E as an internally led project.

In this week’s blog post, I will evaluate the 3 potential routes open to you if you are looking to implement CRM/D365E within your business, looking at it purely from a small to medium size business’ perspective. For enterprise-size deployments, then it really does make practical sense to go down the Partner route, as this will ensure you can leverage the maximum level of expertise, as well as take advantage of resources/price breaks that can be leveraged via this route.

Microsoft Partner

It’s hard to argue some of the clear and immediate benefits of involving a Microsoft Partner as part of rolling out CRM/D365E. With access to resources like PartnerSource/the Dynamics Learning Portal and, depending on the partner’s relationship with Microsoft, there is a lot of expertise that a Partner can bring to the table. Most Partners live and breathe the product, with a lot of passion backing this up, so it is a great way to get individuals involved in your system deployment who know the system inside and out. No need to worry as well if your CRM/D365E “expert” is ill/away or if your consultant is unavailable, as you can pick up the phone and speak to anyone there and guarantee that there will be someone there to help. This is definitely very attractive if your business cannot afford the overhead of a dedicated  resource and gives you peace of mind that your system will always have someone there who can make the changes you need.

The downside? None of this comes cheap, unfortunately. Depending on the size and nature of your deployment, you could be looking at anything in the region of £20,000 – £120,000 or upwards for a deployment or migration away from an existing system onto CRM/D365E. Most partners will have specific change control procedures in place to manage any alterations to the original scope of work. If you’re a small business or in a market where your internal processes are subject to change at a drop of a hat, then that CRM/D365E deployment that your originally thought would cost £50,000 has now almost doubled as a result. It can also be very difficult to find a partner that aligns with your vision and objectives for the deployment. You want to make sure that your new system is a great success and that everyone in the business is shouting off the rooftops about it, but is this something that your chosen partner ultimately cares about? Or is their focus more geared towards, for example, hitting their required revenue goal and/or deployed seats in order to maintain their CRM Partner Competency? You have no doubt done your due diligence on which CRM system you want for your business; this should also be applied to any Microsoft Partners who are in the frame for helping to deliver your CRM/D365E project. You should focus on their track record, customer testimonials and, ultimately, your gut feeling when meeting them for the first time.

Independent CRM Consultant/Developer

You can potentially get a consultant on board that suits the level of expertise that you are looking for: if, for example, you anticipate using mostly out of the box elements of the system, then it may make practical financial sense to get on board a fairly junior level consultant; on the flip-side, if you anticipate needing to integrate with other systems within your business, then a consultant with development experience may be a better choice. You may also be able to secure more “face-to-face” time with an independent consultant or ensure that a set number of day(s) are spent within your business as part of the project, something which you may not always be able to guarantee going down the partner route.

Having just one consultant in charge of your entire deployment can prove to be incredibly risky for a business. For example, if the Consultant falls ills or is otherwise incapacitated, your entire project could be held up for long periods, costing the business money in the process. You may also find yourself in a situation where a Consultant is deliberately attempting to stretch out the time and length of the deployment, for selfish reasons. Businesses will also need to think carefully about their plans once a consultant/developer has finished the project. Who will be responsible for managing the CRM/D365E system? Who will carry out end-user training? These and other questions are ones you would need to have solid answers for if you’re are going down the independent consultant/developer route.

Internal Project

Speaking from experience, if you already work heavily with a number of “Microsoft stack” technologies within your business (SQL Server, C# etc.), then you should have very little difficulty in getting to grasp with CRM/D365E; which makes the internal project route a potentially attractive option. If you do get stuck at all, then CRM/D365E training courses are plentiful, and you could find yourself paying as little as 10-20% of what a fixed cost deployment from a Microsoft Partner comes back at, for what would be training for 2-3 individuals on the Microsoft exam curriculum (including the developer track). This assumes, of course, that your business has individuals within it who already have experience that you can potentially use as part of a systems rollout. These individuals may already, in addition, be well placed to understanding what the business needs and may already have a lot of passion around delivering solutions and service that meets and exceeds the expectation of colleagues. Who is better, therefore, to potentially spearhead and implement CRM/D365E within your business? There is also the risk that you potentially involve people within your project who may not fully understand or get how your business operates. Let’s face it, at the end of the day, you know your business better than anyone. You can avoid a situation where you are having to do additional work internally just to create process maps, documents etc. just so that someone else coming into your business cold can understand just what is that you do. And if there is any misunderstanding or confusion with this, then your project could get set back considerably and waste additional resource as a result.

Going down the internal route does present some major challenges and difficulties, which need to be mitigated from the outset in order to ensure the project is delivered on budget and on time. Getting a project manager involved from the outset (assuming your business has such a person available) is one way in which you can prevent against this, but be prepared for lots of frustrating moments. Getting to grips with learning CRM/D365E, even for those who have a good knowledge of the underlying technology, can be a major curve and you will need to be prepared to fail more than you succeed at first. You also need to carefully review and ensure that you have a fallback in case things go catastrophically wrong – if you go down the Online route, then this is provided for to an extent via Microsoft Support; but if you are adopting an on-premise version of CRM/D365E, then such support will be non-existent unless you involve a Microsoft partner.


Each business is different, so you can never make a number of arbitrary assumptions in respect to business size, turnover etc. when advising on the best route to go when rolling out a system like CRM/D365E. So, to summarise, my thoughts on this are:

  • If your business does not have any existing, internal IT technical expertise and a very fixed/focused line of business, then the Partner route is definitely the way to go
  • Ditto above, but if your business is subject to fluctuations, then a blended approach of getting a consultant on board for a fixed period and end-user training in how to use and customise CRM/D365E means that you get that immediate technical expertise, but ensure that you are not reliant long-term on unnecessary overheads
  • If you a small to medium business with already a lot of technical expertise, a tight budget, flexible deadlines and a business model that is subject to sudden changes, then the self-taught/internal project route is ideal and guaranteed to ensure there is sufficient passion and drive within your organisation to make your deployment a success.
  • Ditto above, but if you have a deadline and your business is highly specialised, then getting a consultant and/or Partner involved can really speed things along. Just ensure that you have a fixed budget in place and beware of scope creep or massive changes in requirements.

I hope that my analysis has been fair and balanced, and proves useful if you are currently contemplating how to go about your CRM/D365E deployment. It would be great to hear other people’s views and thoughts on the best approach in the comments below.