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:

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:

2. 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);
    }
  }
}

3. 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.