Previously on the CEI Blog, we have covered using the CRM_URL hidden parameter to dynamically build hyperlinks in SSRS reports. For those of you not familiar with CRM_URL, a little background:
CRM_URL
One of the things that makes the combination of Microsoft CRM and SQL Server Reporting Services great is drill-through. A report, like a sales pipeline report, becomes much more usable if you can click on the opportunity name and pull up the CRM record for that opportunity. An easy novice report-writing mistake, when building reports with hyperlinks to CRM records, is to hard code the hyperlink URL using the URL of your CRM server. This approach will work; however, the hyperlinks will only work in that one environment, and only for users who access CRM that way. For example, if you want to take the report and upload it to another environment, the hyperlink expressions will need to be re-written, and if you have users who access CRM in other ways, such as offline, the report hyperlinks will not work.
That’s why Microsoft included the CRM_URL hidden parameter. This allows you to build a hyperlink to CRM that dynamically changes based on the environment and how the user is accessing it. If a user accesses a CRM report offline, if you use the CRM_URL parameter in your hyperlink, the links will point to offline environment while offline. Bigger benefit is that the report can also be moved between environments without having to reconstruct the jump-to hyperlink expressions, which is essential to proper change management.
So instead of your hyperlink expression looking like this:
=”http://crmserver/orgname/sfa/conts/edit.aspx?id={“ & Fields!contactid.Value.ToString() & "}"
It should look like this
Parameters!CRM_URL.Value & "?OTC=2&ID={"& Fields!contactid.Value.ToString() &"}"
For more information on how to set up the CRM_URL hidden parameter in your SSRS report, see the dynamic drill-through section of the Dynamics CRM SDK.
Dealing with Custom Entities
In the above example, we’re using the CRM_URL parameter to drill-through to a CRM Contact record. The “OTC=” part of the expression defines what entity the hyperlink should link to, using the Entity Type Code. Each entity in CRM has an Object Type Code. For system entities, these codes are always the same. See a list of system entity type codes in Dynamics CRM. So if you are drilling through to a standard system entity, the above example will work fine. The Contact Entity is always OTC 2.
But what about custom entities? Custom entities also have an entity type code; however, the type code for the same custom entity may be different between two environments. The reason is that the entity type code is set sequentially based on the order the entities in an environment are created. If you are drilling through to a custom entity, the example above will still work for you if you specify the OTC of the custom entity in the environment to which you are publishing the report; however, when you move that report from your test environment to your production environment, there’s a good chance that the hyperlinks may have to be re-written.
Fortunately, there is an easier approach than the example specified in the SDK. By replacing “OTC=” with “LogicalName=,” we can define the drill-through entity using the entity name rather than the entity type code. For example, say we have a custom entity named “Truck,” we can drill through to it using the following expression:
=Parameters!CRM_URL.Value & "?ID="& Fields!new_truckid.Value.ToString & "&LogicalName=new_truck"
And we can now move the report between environments without having to re-write the report hyperlinks.
Comments