Click through to CRM records from SSRS custom report for Microsfot Dynamics CRM 4. Linking information on SQL Reporting Services custom CRM reports to Dynamics CRM 4 records and opening them up from within the report.

If you are writing a custom report – SQL Server Reporting Services (SSRS) report for Microsoft Dynamics CRM 4, you might want to add the functionality (sql expression) to click through to a record. This can be applied to some or all information shown on the report, so that if you click on the information text box, you open up the corresponding CRM record in a new window such as: click on sub-account in the sub-accounts list to open this sub-account CRM record, or the same for contact, opportunities and so on.

To do this, right click on the text box in where you will be putting the expression and the value to be clicked on, and click on Texbox properties. On there click on Actions on the left, select last option “Go to URL” and in the expression box type the following query expression

=IIF(IsNothing(Parameters!CRM_URL.Value),system.dbnull.value,Parameters!CRM_URL.Value & "?ID={"&Fields!Accountid.Value.ToString()&"}&OTC=1")
/*Replace "&Fields!Accountid" with the ID of the corresponding record */
/*you want to be open, so contactid, opportunityid or any other custom entity id field*/
/*Also replace OTC value above with the Entity Object type Code (OTC) value*/

You can get any entities OTC value from the “EntityView” view or for the full list of all system entities type codes, read this MSDN article: http://msdn.microsoft.com/en-us/library/bb887791.aspx. Obviously you will need to get the OTC value for your custom entities from the “EntityView” view from your Dynamics CRM database. You can use a query similar to the following:

select entitytypecode from entityview where entityname = 'account'

This sql expression can work with any entity either system (cusomisable) or custom entity.

2 Replies to “Click through to CRM records from SSRS custom report for Microsfot Dynamics CRM 4. Linking information on SQL Reporting Services custom CRM reports to Dynamics CRM 4 records and opening them up from within the report.”

  1. Good one…!
    But I have one query. I need to open record CRM record on click of this hyperlink with the same window properties as is in CRM record. Like toolbar=no, menubar=no, scrollbars=yes, etc….. How can we do that here?

Please comment or leave feedback