In this blog, we highlight a lesson learned when we used a Dynamics 365 Customer Engagement online data source connector, called Instance Web API, for Power BI reports.
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:
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
This script is for running the report on the reports menu.
This script is for running the report from an iFrame.
There you go! Now you have a really cool XRM solution based on Microsoft Dynamics CRM that includes Advanced Querying.