Dynamic URLs in Microsoft CRM

So there's a lot of information out there about how to set up hyperlinks in SSRS reports and get them to point to CRM records. There's also a lot of information about making these links dynamic so they work in any CRM environment. But a lot of this information is incomplete, leaving you scratching your head when you use the examples provided online.

So the first step is actually setting up a parameter that will fetch the CRM URL for the environment you're currently in. To do that, create a new parameter and name it CRM_URL. The name is the important part here. Any parameter prefixed with "CRM_" is considered special when you upload the file to CRM.

The data type of the parameter is text and the parameter needs to accept NULL values. The prompt can be anything. I tried "bob" and it worked. And while most sites tell you to set the parameter as hidden, when you upload the report into CRM it should overwrite whatever you have in there to hidden. I didn't have any problems with visible or internal settings - CRM changed it to hidden when I uploaded it.

In the available values section, make sure it's set to none.

In the default values section, choose specify values. This is one of the parts that got me. If you don't specify a NULL value, the parameter won't return anything.

Finally, you should be able to leave everything in the advanced section as the defaults.


That should get you to a working, dynamic URL in CRM that automatically adapts to any environment you move your report to.

But also keep in mind that I couldn't get any of this to work in Visual Studio. Only once I uploaded the report to CRM did the parameter actually populate and the hyperlinks work.








This is all well and good, but there's a problem with this. If you try to use this in a subreport or drilldown report it won't work! Subreports won't work with CRM's special parameters. So what now?

Well, the answer is fairly simple. You need to set the parameter up in both the main and subreports, then pass the parameter to the subreport. That's kind of a pain in the butt, but it's the only way this works.

Once you do that, the dynamic hyperlinks should work in both the main and subreports.

If you're not sure how to setup those dynamic hyperlinks, see the post I made yesterday HERE.

Why won't my hyperlinks work in CRM reports?

This is something that comes up most often with SSRS reports written for Microsoft Dynamics CRM, but I'm sure it causes problems elsewhere.

The symptom is when you add a record hyperlink to a report, it doesn't register as a hyperlink. Clicking does nothing, and your cursor doesn't even change to the little hand icon on mouse over.

Generally this means there's something wrong with the hyperlink. You can output that hyperlink to a field and verify that it looks good to you. You can even copy and paste it into your browser to make sure it works.

Assuming that it's a correct hyperlink, it still may not work. And the reason for that is SSRS doesn't do a good job of converting GUIDs to stings. Even if you specify that the field is a string in the XML, it may not convert it properly. So you have 2 options: explicitly convert it to a string in your query, or convert it to a string in the expression editor.

When you use the expression editor, you have 2 options: toString or CStr.

Sometimes SSRS/CRM will inconveniently drop the toString from your hyperlink when you upload the report. I haven't pinned down why this happens, but know it does and you may need to put it back in and upload again. The formula should look something like this:

=IIF(IsNothing(Parameters!CRM_URL.Value), System.DBNull.Value,

Parameters!CRM_URL.Value & "?ID={" & Fields!contactid.Value.toString & "}&LogicalName=contact")

Alternately, you can use CStr in your formula. SSRS/CRM won't drop this function when you upload the report, but in a few cases it just doesn't work. Again, I don't know why it works in some situations but not others. You may have to fiddle with it. At any rate, the formula should look something like this:

=IIF(IsNothing(Parameters!CRM_URL.Value), System.DBNull.Value,
Parameters!CRM_URL.Value & "?ID={" & CStr(Fields!contactid.Value) & "}&LogicalName=contact")

Either way, this should solve the problem of valid hyperlinks not actually working in your field actions.