Sometimes the out-of-the-box lookup field in CRM 2011 does not have the advanced query capability that a user needs to find a record. The query may go beyond a basic filter, or it may even include data from an external application. In this case you may need to create a custom SSRS report to find the necessary record(s) to present to the user.

Consider these complex requirements:

• From the CRM Lead form, the user must assign a Lead to the best Partner based on geographical location, how many leads they had successfully qualified, and the number of leads already assigned to the Partner.
• From the CRM Case form, the user must associate a Case to an Opportunity for the Customer, under which at least 3 orders have been placed for XYZ product.
• From the CRM Case or Opportunity, the user must find the Contact with the most Activities for an Account.

Using SQL Reporting Services, it's possible to run these types of advanced queries, present the results to the user, and allow them to select the option and bring it into the CRM form. This same method can be used to bring the value of a field, such as a text description, from another CRM record or external database into the CRM form. In either case, the user may run the report from the Reports menu on the record, or the report may be displayed in an iFrame on the record.

How to Update a CRM Record from SSRS Report

To demonstrate how this is done, we will use the example of a Representative who is working on a Case record. An SSRS report containing relevant knowledge base (KB) articles to the Case is displayed in an iFrame on the Case. In order to successfully close the Case, the user must provide a description on the resolution, and to do this, they would like to copy the Description from the KB Article on the report. In this scenario, we simply modify the report with JavaScript to allow the user to click on the KB Article description and pop it into Case Description. Here are the steps:

1. Create an SSRS report on the KB Article entity. Populate the columns of the report with KB Article attributes.
2. Right-click the desired column and select Text Box Properties. (Note: This is the column the user will click on to copy the Description to the CRM record).

1. Click on Action. From the Change action options, Select Go to Url as shown below.

1. Click on the Function button. The Expression window will open as below.
2. Copy the code below to set Case's "new_test" field to "description" field retrieved in SSRS report and click OK.

   This script is for running the report on the reports menu.
   ="javascript:window.top.opener.top.opener.Xrm.Page.getAttribute('new_test').setValue('"& Fields!description.Value.ToString() & "'); window.top.opener.top.opener.Xrm.Page.data.entity.save(); function CloseWindow(){window.open('','_self','');window.close();} CloseWindow();"

   This script is for running the report from an iFrame.

   ="javascript:window.top.opener.parent.parent.Xrm.Page.getAttribute('new_test').setValue('"& Fields!description.Value.ToString() & "'); window.top.opener.top.parent.parent.Xrm.Page.data.entity.save(); function CloseWindow(){window.open('','_self','');window.close();} CloseWindow();"

1. Save the report and upload to CRM to display on the appropriate views and forms.

There you go! Now you have a really cool XRM solution based on Microsoft Dynamics CRM that includes Advanced Querying.

Happy CRM'ing!