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
- Create an SSRS report on the KB Article entity. Populate the columns of the report with KB Article attributes.
- 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).
- Click on Action. From the Change action options, Select Go to Url as shown below.
- Click on the Function button. The Expression window will open as below.
- 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.
This script is for running the report from an iFrame.
- 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.