For those businesses or individuals who are currently considering Dynamics CRM, one of the decisions that you will ultimately need to make is regarding whether you intend to use CRM Online or CRM On-Premise. For those whose first reaction to the previous statement is “Say what?”, heres a brief breakdown of the two different options:

  • CRM Online: An instance of CRM that is accessible via Office 365.
  • CRM On-Premise: An installation of CRM on your own server(s).

The word on the street these days is all around cloud computing and services, and that all organisations should have most, if not all, of their infrastructure within a hybrid public/private cloud configuration. However, it could be that you are required to host your CRM within a specific location due to regulatory or contractual requirements for your business. Or that you already have existing costs allocated towards server infrastructure that must be used as part of your CRM project.  The list of potential reasons are endless, which is why CRM On-Premise exists in the first place and is still an essential requirement for many organisations.

So here’s a breakdown of some of the factors to consider, and my recommendation on the best approach to go for:

If you are already using Exchange Online with other Office 365 services, then CRM Online is the way to go

One of the potential headaches when it comes to configuring CRM for first use is around e-mail synchronisation, something which I have hinted at in a previous blog post. If you already use Exchange Online, then the setup steps involved are greatly reduced, as CRM will automatically detect your Exchange Online profile and settings if it is on the same tenant as your CRM Online instance. If not, then you’re going to have to look at other options such as the E-mail Router in order to link up your On-Premise Exchange Server, SMTP or POP3 e-mail system. These can be fiddly to setup and maintain, as you will require a dedicated machine that hosts the E-mail Router software and you may potentially have to liaise with other third party e-mail system providers in order to troubleshoot any issues.

Sometimes it’s nice having the latest new product without paying extra for it

For On-Premise CRM, you would need to factor any future upgrade plans as part of your initial cost-investment into the system. Given the increased frequency of CRM releases, this could start adding up to big bucks after the first year or two. As a On-Premise customer as well, you will also miss out on any major updates in between versions, such as Update 1 last year for CRM Online. This was quite a fundamental and significant update, in my opinion, that helped to make CRM even easier to use. With CRM Online, you are always guaranteed to get the latest updates and thereby take advantage of some of the latest and best features available within CRM. The trade off with this is that you must upgrade to the latest version of CRM eventually. You’ll be offered a date and time for the upgrade and can delay it, but you can’t stay in the past forever! This could present issues if, for example, you have written bespoke customisations that are no longer supported or deprecated. Be sure you have read and fully understand how updates work in CRM Online before making your decision.

Evaluate your internal resources first

It could be, for example, that your organisation is moving from an internal application system that uses SQL Server as the backend database system and that you have several team members who you have invested heavily on T-SQL administration/development training. The great thing about On-Premise CRM is that this skillset will not be lost, as you will need to maintain and manage your organisation(s) databases. And, if you’re really nice, you can also let them write beautifully bespoke SSRS reports directly against your databases and let them do all sorts of other fun data integration pieces using SQL Server.

The flip side of this should be obvious, but if you and your organisation don’t know your SELECT’s from your WHERE’S, then CRM Online could be the best choice as you don’t have to worry about managing and maintaining a SQL database, as Microsoft handles all of this for you. You can instead focus yours and your team’s attention and learning potentially more relevant things relating to CRM (Online) directly

Do you trust Microsoft?

Its a serious question nowadays. In the world of cloud computing, can you say with 100% certainty that the organisation(s) where you are hosting or storing some of your business’ key data and applications will a) ensure your data is kept securely and b) able to offer satisfactory guarantees in relation to service availability? Whilst (touching wood) Office 365/CRM Online outages have been few and far between, the risk is still ever omnipotent. You will therefore need to evaluate what the maximum amount of outage time is acceptable for your business and put in place procedures to ensure that your business can keep working (for example, nightly backups of your CRM Data so that you can still access your Data via a spreadsheet/database export). The benefit of having an On-Premise CRM system is that you will more than likely have control over your server machines, as well as all the data that is stored on them, and ensure your infrastructure is built to satisfy any concerns around outages or system failures.

Nice words for your Finance Team: Operational Expenditure is better than Capital Expenditure! 

Deciding to go with CRM Online could significantly simplify your organisations visibility over your month-by-month costs. If done correctly, you could even make the bold claim that you have successfully eliminated all capital expenditure (i.e. upfront software costs) costs relating to CRM systems within your business. Based on experience, most finance departments are happier knowing they have to pay X amount over a 12 month period, as opposed to being hit by large and unwieldy costs in a sporadic and uncertain way. So if you want to be make BFF’s within your finance/account team, then CRM Online is the fastest and best way to achieve this.

Legacy Systems or that annoying finance system that’s 15 years old, but runs 20% of our business work and cannot be replaced

Without traditional database access that On-Premise provides, it may prove difficult integrating your CRM system with any legacy system, particularly if it’s a non-SQL database. That’s not to say that it’s not possible to find a solution using CRM Online, but you may have to expand significantly more resources setting up staging environments with your CRM Online/Legacy System, or look at writing customised code that performs the tasks that you need in order to “glue” both of the systems together.

Make sure your technical team understand the limitations of CRM Online clearly, and that their feedback is factored in as part of the decision making process

I’ve already mentioned the most obvious limitation for CRM Online in the form of not having access to the CRM SQL Database. But there are other limitations too. For example, you are unable to directly query all of the information contained within the audit data entity.  There is a very good (although outdated) article on TechNet which gives a flavour of some of the limitations within CRM Online. It is very important that as part of any scoping exercise that your technical team is fully aware of the limitations of CRM Online, so that any potential difficulties around integration or data access can be mitigated from the outset.

Conclusions – or Wot I Think

If you are a small to medium business who are already using Office 365 or planning to move across in the near future, then CRM Online is the obvious and best choice to ensure the most streamlined user experience and ease of management and setup. If, however, you are a much larger organisation or are required to operate under specific compliance or regulatory requirements in respect to your business applications/data, then these are the types of scenarios where On-Premise CRM is pretty much an absolute requirement.

One of the great things about developing bespoke solutions for CRM is the ability to make changes to the sitemap navigation. For the uninitiated, the Sitemap is this area within CRM:

Sitemap

The areas and individual buttons can be modified to suit most requirements for organizations, to include links to custom entities, external applications or to an internal HTML/Silverlight web resource. As a result, CRM can be made to look highly bespoke and unique, as if it is a completely different CRM system altogether from the default setup.

We recently had a requirement to create a sitemap area button that would open a specific record. The record in question is one that will be updated frequently, so colleagues within the business require quick and easy access to it from the Sitemap area. We already know that this possible for opening specific entity views, as we have used this a number of times previously (for example, change the default view that opens when you click the ‘Accounts’ button to “Accounts I Follow”). MSDN provides a great outline of how you go about doing this:

To display a list of entity records within the application for a SubArea set the Entity attribute value. This displays the default view for that entity and provides the correct title and icon.

However, if you want to have a SubArea element that uses a specific initial default view, use the following Url pattern.

Url=”/_root/homepage.aspx?etn=<entity logical name >&amp;viewid=%7b<GUID value of view id>%7d”

Source: https://msdn.microsoft.com/en-gb/library/gg328483.aspx#BKMK_DisplayViewInApplicationUsingSiteMap

The question at this stage then is can we adapt the above method in order to open an entity record instead? Let’s give at a go, using our trusty XRMToolbox Sitemap Editor tool. The steps below assume that you already know how to use this tool and to make amends to the sitemap area.

On the above article page, we are told that in order to open an entity record via a URL, we need to provide the following query parameters:

  • etn: The logical name of the entity
  • pagetype: In this instance, should be set to “entityrecord”
  • id: The GUID for the CRM record to open. The best way to obtain this is to export the record to Excel and unhide all the columns; the GUID is then the value in the A column; you will need to change this to Upper Case via an Excel =UPPER function:

ExcelGUID

Then, in order to ensure that the GUID is accepted correctly in the URL, we need to surround it with curly braces. As these character types are not accepted as part of a URL string, we need to provide the following substitute character strings:

{ = %7B

} = %7D

e.g. {E16EE6D6-56B4-E511-80E2-2C59E541BD38} -> %7BE16EE6D6-56B4-E511-80E2-2C59E541BD38%7D

So let the trial and error begin! The most simple way of getting this to work would be to change the SubArea URL value to the full CRM instance URL. So, for example, our CRM Online URL would look something like this:

https://mycrminstance.crm.dynamics.com/main.aspx?etn=test_mycustomentity&pagetype=entityrecord&id=%7BE16EE6D6-56B4-E511-80E2-2C59E541BD38%7D

But if we have separate development/production environments, then this is impractical as the link will not work when moving our solution between environments. Our preferred setup therefore is to look at using a relative URL path that works across different environments.

What happens if we try adapting the URL example for views instead? So, in which case, our URL would be:

/_root/homepage.aspx?etn=test_mycustomentity&pagetype=entityrecord&id=%7BE16EE6D6-56B4-E511-80E2-2C59E541BD38%7D

SitemapURLError

That’s a no then! The next step then is to take a closer look at the example URL’s provided and making some guesswork in regards to how relative URL’s function. If we assume then that our full URL is:

https://mycrminstance.crm.dynamics.com/main.aspx?etn=test_mycustomentity&pagetype=entityrecord&id=%7BE16EE6D6-56B4-E511-80E2-2C59E541BD38%7D

Then our relative URL would be:

/main.aspx?etn=test_mycustomentity&pagetype=entityrecord&id=%7BE16EE6D6-56B4-E511-80E2-2C59E541BD38%7D

And guess what? It works! One comment to make on this though is that the record opens in a brand new window within Internet Explorer & Google Chrome, so I would therefore presume that this is the case across all browsers. There are some additional query string parameters that can be specified in the URL to make this look more like a quick-edit “pop out” window:

  • cmdbar: Setting this to “false” will hide the ribbon on the form
  • navbar: Setting this to “off” will hide the sitemap navigation bar

Our URL string and record window would therefore look this:

/main.aspx?etn=test_mycustomentity&pagetype=entityrecord&id=%7BE16EE6D6-56B4-E511-80E2-2C59E541BD38%7D&cmdbar=false&navbar=off

URLExampleWindow

The user can then make their changes to records, save and then close the window. Suffice it to say, it is good to know that it is possible to do this within CRM and that the trial and error steps involved were fairly minimal.

The Scenario: You are running CRM Online in conjunction with some legacy database/application systems. These systems are setup with a SQL Server Reporting Services instance that is looking to either an SQL Server, OLE DB etc. database.

The Problem: You need to make data from your legacy systems visible within your CRM. The information needs to be displayed on the Entity Form and show specific information from the legacy database that relates to the CRM record.

Admittedly, the above is perhaps somewhat unlikely situation to find yourself in, but one which I recently had to try and address. I suppose the most straightforward resolution to the above is to just say “Get rid of the legacy system!”. Unfortunately, the suggestion didn’t go down to well when I voiced it myself…

So at this point the next best answer looked to be try and utilise what we have within the existing infrastructure: an all singing, all-dancing SSRS and SQL Server database instance.

What if we were to try uploading an .rdl file that includes a FetchXML and our SQL/OLE DB database data source into CRM? Whenever you try to perform this, you will get this error message:

ReportUploadError_NoFetchXML

 

Rats! So there is no way in which we can include a non-fetch XML Data Source to our separate SSRS report instance. So is there anything else within CRM that can be utilised to help in this situation? Let’s first take a quick look at the following nifty little feature within CRM, courtesy of our good friend MSDN:

You can use an IFRAME to display the contents from another website in a form, for example, in an ASP.NET page. Displaying an entity form within an IFrame embedded in another entity form is not supported.

Use the getValue method on the attributes that contain the data that you want to pass to the other website, and compose a string of the query string arguments the other page will be able to use. Then use a Field OnChange event, IFRAME OnReadyStateComplete event, or Tab TabStateChange event and the setSrc method to append your parameters to the src property of the IFRAME or web resource.

You may want to change the target of the IFRAME based on such considerations as the data in the form or whether the user is working offline. You can set the target of the IFRAME dynamically.

Source: https://msdn.microsoft.com/en-gb/library/gg328034.aspx

Having worked extensively with SSRS in the past, I am also aware that you can use an SSRS URL string in order to specify properties about how the report is rendered, its size and – most crucially – what the value of required parameters should be. The friend that keeps on giving has a great article that goes through everything that you can do with an SSRS report URL and also how to use Parameters as part of your URL. So in theory therefore, we can place an IFRAME on our form and then use JScript to access form-level field values and modify the IFRAME URL accordingly.

Here are the steps involved:

  1. Go into Form Editor and add a new IFRAME to the form, specifying the following settings:

Name: The Logical name of the control, this will be required as part of the JScript code used later, so make a note of it.

URL: As this is a mandatory field, you can specify any value here as it will change when the form is loaded by the user. This is not practical as we don’t want this to be displayed if, for example, the field that we are passing to the URL has no value in it. Our JScript code will sort this out in a few moments

Label: This can be anything, and defaults to whatever is entered into the Name field

Restrict cross-frame scripting, where supported: Untick this option

Ensure that ‘Visible by default’ is ticked

Your settings should look something like this:

IFRAMESettings

  1. Create or modify an existing JScript Library for the form, adding in the following function (after modifying the values accordingly):
function onLoad_LoadSSRSReport() {

    //First get the page type (Create, Update etc.)

    var pageType = Xrm.Page.ui.getFormType();
    
    //Then, only proceed if the Form Type DOES NOT equal create, can be changed depending on requirements. Full list of form types can be found here:
    
    //https://msdn.microsoft.com/en-us/library/gg327828.aspx#BKMK_getFormType

    if (pageType != "1") {

        //Get the value that you want to parameterise, in this case we are on the Account entity and need the Account Name

        var accountName = Xrm.Page.getAttribute("name").getValue();

        //In order to "accept" the parameter into the URL, spaces need to be replaced with "+" icons

        accountName = accountName.replace(/ /g, "+");

        //Now, get the the name of the IFRAME we want to update

        var iFrame = Xrm.Page.ui.controls.get("IFRAME_myssrsreport");

        //Then, specify the Report Server URL and Report Name.

        var reportURL = "https://myssrsserver/ReportServer?/My+Reports/My+Parameterised+Report&MyParameter=";

        //Now combine the report url and parameter together into a full URL string

        var paramaterizedReportURL = reportURL + accountName;

        //Finally, if there is no value in the Account Name field, hide the IFRAME; otherwise, update the URL of the IFRAME accordingly.

        if (accountName == null) {
            iFrame.setVisible(false);
        }
        else {

            iFrame.setSrc(paramaterizedReportURL);
        }
    }
}
  1. Add the function to the OnLoad event handler on the form. Now, when the form loads, it will update the IFRAME with the new URL with our required parameter.

And there we go, we now have our separate SSRS instance report working within CRM! A few things to point out though:

  • If the report parameter supplied does not load any matching records, then SSRS will display a standard message to this effect. You would need to modify the report settings in order to display a custom message here, if desired.
  • It is recommended that you have https:// binding setup on your report instance and supply this to as part of the setSrc method. http:// binding works, but you may need to change settings on your Web Browser in order to support mixed mode content. Full instructions on how to set this up can be found here.
  • This may be stating the obvious here, but if your SSRS instance is not internet-facing, then you will get an error message in your IFRAME if you are not working from the same network as your SSRS instance. Fortunately, SSRS can be configued for an Internet deployment.
  • The steps outlined in 1) can also be used to specify a non-parameterised SSRS report within an IFRAME dashboard too. I would recommend using the following SSRS system parameters as part of the URL though:
    • rs:ClearSession=true
    • rc:Toolbar=false

e.g.

https://myssrsserver/ReportServer/Pages/ReportViewer.aspx?%2fMy+Reports%2fMy+Non+Parameterised+Report&rs:ClearSession=true&rc:Toolbar=false

One of the most challenging things about any system migration is ensuring that information from other business systems can be made available, and it is good to know that CRM has supported approaches that can help to bridge the gap.

Working with Dynamics CRM can present some interesting challenges. What you tend to find is that you can pretty much say “Yes!” when it comes to doing most things you would expect from a CRM/database system, but there is a learning curve involved in figuring out the best approach to take. Often, as well, you may  over-complicate matters and overlook a much easier solution to achieve what you need.

Take, for example, modifying the FetchXML queries in a Public View that you have created programmatically. Let’s say you’ve created your own view within CRM using the following C# code snippet (adapted from the SDK sample):

SavedQuery sq = new SavedQuery
     {
        Name = "My New View",
        Description = "My view created in C# for the Account entity",
        ReturnedTypeCode = "account",
        FetchXml = fetchXml,
        LayoutXml = layoutXml,
        QueryType = 0
    };

_customViewId = _serviceProxy.Create(sq);
Console.WriteLine("A new view with the name {0} was created.", sq.Name);

A few things to point out first with the above:

  • In order for this code to work, you would need to declare System.String values for fetchXml and layoutXml, as well as first connecting to CRM using the OrganizationServiceProxy (_serviceProxy).
  • As well as specifying the FetchXML query you would like to use, you also have to specify a LayoutXML as a parameter in order to. Although Microsoft do have dedicated articles on MSDN that goes over the schema for this, there is a potential learning curve involved here for those who are unfamiliar with working with XML.
  • ReturnedTypeCode is your entity logical name, which will need changing depending on the entity you are attempting to query
  • Be sure to add in the appropriate namespace references, otherwise this code will not work.

The code example above is all very well and good if you are just wanting to create a brand new view. But what happens if you need to change it in the future? We can modify the base properties of a view (Name, Description etc.) as well as the column layout via the CRM GUI, but when we attempt to modify the filter criteria (i.e. the FetchXML query), we will notice that the option is not available to use:

View_NoFilterCriteriaButton

The next logical step would therefore be to look at creating some C# code that would take the existing view and modify the fetchXML query property. Unfortunately, Microsoft have not provided code examples on how this can be done, although it is in theory possible via the many methods at your disposal through the SDK.

Rather then spend days and potentially weeks writing a bespoke piece of code to do the job, it was then that I realised that I was being a little dense (as tends to happen) and that the Solution was sitting right in front of me. See what I did there?

Whilst the general rule of thumb is “DON’T DO IT!!” when it comes to modifying an exported solution file, it is possible to do and pretty much anything within a solution file can be changed or modified to suit a particular requirement. And, as luck would have it, modifying Public Views (either ones created by yourself or system ones) is a supported task that you can perform on the solution file:

Definitions of views for entities are included in the customizations.xml file and may be manually edited. The view editor in the application is the most commonly used tool for this purpose. Editing customizations.xml is an alternative method

Source: https://msdn.microsoft.com/en-gb/library/gg328486.aspx

So, in order to modify a custom Public Views FetchXML query, all you would need to do is:

  1. Create a temporary, unmanaged solution file containing the entity with the custom Public View you want to change.
  2. Export as an unmanaged solution, unzip and open the customizations.xml file either in Notepad, Visual Studio or the XML editor program of your choice
  3. Use Ctrl + F to locate the savedquery node of the view you wish to change. It should look like this:
<savedquery>
    <IsCustomizable>1</IsCustomizable>
    <CanBeDeleted>1</CanBeDeleted>
    <isquickfindquery>0</isquickfindquery>
    <isprivate>0</isprivate>
    <isdefault>0</isdefault>
    <returnedtypecode>1</returnedtypecode>
    <savedqueryid>{8e736028-47c7-e511-8107-3863bb345ac8}</savedqueryid>
    <layoutxml>
        <grid name="resultset" object="1" jump="firstname" select="1" preview="1" icon="1">
            <row name="result" id="accountid">
                <cell name="name" width="150" />
                <cell name="statecode" width="150" />
                <cell name="statuscode" width="150" />
                <cell name="ownerid" width="150" />
                <cell name="createdon" width="150" />
            </row>
        </grid>
    </layoutxml>
    <querytype>0</querytype>
    <fetchxml>
        <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>
            <entity name='account'>
                <attribute name='createdon' />
                <attribute name='statuscode' />
                <attribute name='ownerid' />
                <attribute name='name' />
                <attribute name='statecode' />
                <attribute name='accountid' />
                <order attribute='name' descending='false' />
                <link-entity name='email' from='regardingobjectid' to='accountid' alias='ab' link-type='outer'>
                    <attribute name='regardingobjectid' />
                </link-entity>
                <link-entity name='lead' from='parentaccountid' to='accountid' alias='al' link-type='outer'>
                    <link-entity name='email' from='regardingobjectid' to='leadid' alias='lp' link-type='outer'>
                        <attribute name='regardingobjectid' />
                    </link-entity>
                </link-entity>
            <filter type='and'>
                <condition entityname='ab' attribute='regardingobjectid' operator='null' />
                <condition entityname='lp' attribute='regardingobjectid' operator='null' />
                <filter type='or'>
                    <condition entityname='ab' attribute='createdon' operator='olderthan-x-weeks' value='1' />
                    <condition entityname='ab' attribute='createdon' operator='null' />
                </filter>
            </filter>
            </entity>
        </fetch>
    </fetchxml>
    <IntroducedVersion>1.0</IntroducedVersion>
    <LocalizedNames>
        <LocalizedName description="My New View" languagecode="1033" />
    </LocalizedNames>
    <Descriptions>
        <Description description="My view created in C# for the Account entity" languagecode="1033" />
    </Descriptions></savedquery>
  1. Modify the FetchXML query within the <fetchxml> node to your updated query
  2. (Optional) If your FetchXML query is simply making changes to the filter criteria, you can skip this step. Otherwise, if you have new fields that you would like to be displayed as part of the changes, you will also need to modify the <layoutxml> node so that it contains your new fields.
  3. Save the changes back into the solution file and then import the solution back into CRM.
  4. Test your view by opening it within the application and confirm everything looks OK.

I’m sure you’ll agree that this is definitely a much easier and simple way to make changes to your view. Just be careful when working within the solution file that you don’t accidentally delete/overwrite something!