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.

Maxjerin has added a new code suggestion. Maxjerin’s code hides a tab based on the onChange event of a field on the form. If the primary contact “name” field is maxjerin, the tab is displayed. Otherwise the tab is hidden.

I have updated the post to include Maxjerin’s code – Thanks Maxjerin:

Here is the code that I used to HIDE/UNHIDE a tab when I wanted to show it for only one particular user and hide for others. The code includes some alert messages just for debugging purpose which you might find useful to understand the code. You may remove them once done.

This section goes into the onChange section of the Primary Contact lookup field