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.