Creating a Custom Interactive Grid on a Microsoft Dynamics CRM Entity Form using SSRS

There are many resources documented in how to create a report to appear in a CRM form such as:

• http://andreaswijayablog.blogspot.com/2011/08/crm-2011-embed-report-in-iframe-and.html
• http://www.develop1.net/public/post/Embedding-CRM-reports-in-IFrames-using-the-CRM2011-Report-Viewer.aspx
• https://community.dynamics.com/crm/b/crmcustomereffective/archive/2010/08/23/embedding-crm-pre-filtered-reports-in-crm-iframes.aspx

However, the purpose of this blog is to describe a step by step process that will allow you to create a custom grid between Microsoft Dynamics CRM entities that are not directly related (or directly) with the assumption that you have SQL query knowledge, SSRS, and Dynamics CRM experience.

Steps to make the Grid open up Links (when the hyperlink is clicked):

1. Create a Report that takes the Entity GUID as a Parameter
   • i.e. Account GUID
2. Create a Query that links Direct or Indirect Entity
   • i.e.  Accounts are related to some custom entity called HappyDetails. HappyDetails is related to HappyCode.  Your custom grid wants an interactive grid to show associated HappyCode to the Account.  The query would be something like:
     
     “Select Distinct HappyCodeName,HappyCodeID From HappyCode HC Inner Join HappyDetails HD on hc.Id = hd.aID Where hd.AccountID = @AccountGUID”
     
3. Create your report and then in the HappyCodeName Field, do the following (this creates the hyperlink):
   • Right Click and Select Textbox Properties
   • Select Action
   • Select the “Go-to-URL”
   • Select the fx button to add the expressionTo get the URL of the HappyCode Entity, open a HappyCode record and press F11 copy the URL to notepad.It might look something like:
     “http://ServerCRM/CRMOrg/main.aspx?etc=10016&extraqs=%3fetc%3d10016%26id%3d%257bEA783CF8-3204-E211-9FC4-005056BB588A%257d%26pagemode%3diframe%26preloadcache%3d1366653674643&pagetype=entityrecord”Replace the bold text above with the GUID of HappyCodeID
     “http://ServerCRM/CRMOrg/main.aspx?etc=10016&extraqs=%3fetc%3d10016%26id%3d%257b” & Fields!HappyCodeID.Value.ToString() & “%257d%26pagemode%3diframe%26preloadcache%3d1366653674643&pagetype=entityrecord”
4. TEST: Run your report and pass a GUID.  Then click on Hyperlink.

Steps to Add the Custom Grid to CRM Entity Form:

1. Upload Your Report
   • Publish your Report for External Use 
2. Adding the Iframe to the Form with Navigation Link
   • Customize the form by inserting a “Tab (one column)”.
   • Provide the details that will appear in the left hand side of the form. 
   • Add an IFRAME to the “Tab” sectioni.    Give the Iframe a name (note the name, it is used in a the script)
     ii.    Set the URL as “about:bank”
     iii.    Give the Iframe a Label
     iv.    Restrict cross-frame scripting is checked
     v.    Visible by Default is checked
   • Format the IFRAME with two columns, Number of rows equal to 10, and make sure Display border is checked. 
3. Add a new Web Resource with your naming convention
   • Script(jScript) Web Resource 
4. Edit the web resource
   • Replace the bold text with the information of your environment and the name of the function//Function that gets the iframe control and sets the url of the report
     function someNameGiven()
     {
     //Get the Form Entity ID
     var Entity = Xrm.Page.data.entity.getId();
     //Set URL
     var URL = “http://Name_of_SQLSERVER/ReportServer?%2fNameofFolder%2fReportName&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false&rc:Parameters=false&id=” + EntityID + “&rc:LinkTarget=_blank”;
     Xrm.Page.getControl(“IFRAME_SomeName”).setSrc(URL);
     }
5. On the form, add the web resource you created on the form’s on-Load event call the Function someNameGiven  
6. Edit the IFRAME Report Security
   1. Add “Domain Users” to the Security with the following:
      i.    Browser
      ii.    Browser for Microsoft CRM

If you are looking to extend your existing Microsoft Dynamics CRM solution, RSM can help.  Our consultants average 15+ years experiences and can help you configure a system to work more efficiently.  For more information, contact our professionals at crm@mcgladrey.com or by phone at 855.437.7202.

By: Brian Connelly – New Jersey Microsoft Dynamics CRM Partner

Meg Moss