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.

Mystery Lock Screen

A few days ago, I noticed a strange new lock screen on my phone that I didn't remember putting on there. It was a simple black background with a blue clock, blue notification icon, and the text of my last notification, also in blue. If I swiped across it, my normal lock screen appeared (complete with lock pattern) and I had to unlock it again.

I spent a few days troubleshooting it, uninstalling all my additional launchers, before I figured out what the issue was. I had Dynamic Notifications enabled on my phone. This was the culprit, because once I unchecked it, the mysterious lock screen disappeared.

You can find Dynamic Notifications in the Samsung Galaxy S3 settings by going to Settings > Accessibility and unchecking that bad boy (or checking it, if you like it.)

#android #s3 #galaxys3 #lockscreen #troubleshooting

Stuck in Recovery

This weekend I was at my mother in law's and I flashed a new custom recovery image on my rooted Samsung Galaxy S3. When I was finished, it asked me if I wanted to reboot into recovery mode, which I did because I wanted to 1) make sure it worked, and 2) make a backup of my phone.

Much to my chagrin, the phone would not boot into recovery mode. It flashed the Samsung logo, along with the dialogue at the top saying it was booting into recovery, but then the screen went dark and the phone became unresponsive (I'm not sure if it was off or not).

I held the power button  and rebooted the phone, but it tried to go into recovery mode again, rather than boot normally. I pulled the battery and rebooted again, but it still wanted to go into a non-functioning recovery mode.

I tried to go into download mode (power + home + volume down), and this worked. So I pulled the battery and tried to boot normally, but again got stuck in recovery FUBAR.

I couldn't plug the phone into my computer and fix things with Odin, since I was at my mother in law's. And I couldn't research the problem, since my phone wasn't working.

What I found to work was to put the phone into download mode. It asked me if I wanted to continue (volume up) or cancel and reboot (volume down). When I rebooted via this menu, the phone booted normally and I was able to flash a different recovery image.

I figured I'd put this out to anyone else who has this problem. If you're stuck in a non-functioning recovery mode loop, try to go into download mode and reboot from there. It might just work.

#android #s3 #galaxys3 #samsung #recoverymode #troubleshooting

SSRS: Cascading Parameters not Refreshing, Part 1

I've been using Visual Studio for a while, but mostly with SQL scripts. I haven't had any problems with cascading parameters up until now.

Now that I'm using MDX and hitting cubes, things have changed and I've encountered a problem with cascading parameters not updating automatically. Sure, they populate perfectly when you first make a selection. But if you make a change to the parent after the fact, the child just doesn't update.

It turns out this is by design (thanks, Microsoft). SSRS thinks it's being helpful by not refreshing if it doesn't have to. It also doesn't want you to have to re-select a parameter if you've already selected it.

This is a pain in the butt, since MDX parameters aren't pretty. Who wants to scroll through a dropdown of thousands of dates to find the right one? To allow human users to interact with the report, you need to create a date parameter as a parent parameter, then use that parent parameter to populate a (hidden) child parameter with the correct dimensional hierarchy (I'll talk about that in another post). But, if you change the parent date, the child hierarchy doesn't update, and you're screwed.

I've seen some pretty involved ways to get around this, but I've found it doesn't need to be that complex. To fix this, you just need to "invalidate" the child parameter's available values.

If we take the date example from above, follow these steps:

1. Create a new parameter for the date/time portion. We'll call that one "StartDate".

2. Go into your cube (I use the wizard), find the dimension and hierarchy you need, and drag it to the Filters pane. We'll say we're using the createdon date for this.

3. You'll see that on the far left is a column that says "Parameters". Click that check box. SSRS will automatically create a new parameter for you based on this selection when you click ok.

4. Find the new parameter. It's usually named [Dimension][Hierarchy] and removes any punctuation or spaces you may have in your naming. If the parameter is part of a date range, it may also prefix the parameter with "To" or "From". Open this bad boy up.

5. Leave the datatype as text. If you want to change the "Allow multiple values", go ahead. Leave the "Allow blank value" and "Allow null value" unchecked. MDX has a different way of dealing with these.

6. On the Available Values menu, change the radio button "Specify values". Click the function (fx) button next to value and use something like the following:

="[I - Web Access - Createdon].[Hierarchy].&[" + CDate(Parameters!StartDate.Value).ToString("s") + "]"

The format here is the =[Dimension].[Hierarchy].&[Value]. You may need to use =[Dimension].[Hierarchy].&[{Value}] if you get a CONSTRAINED error (for guids and the like). However, a CONSTRAINED error may also reflect a typo in the hierarchy or a bad/nonexistent value.

*A nice shortcut here is if you go into the query designer, navigate to the hierarchy you want, right click it and select copy, it gives you this address. 

7. Go ahead and copy what you have here. Hit ok, then click the function button next to Label and copy this there as well. That way you can verify the value actually changes.

8. On the Default Values menu, change the radio button to "Specify values". Click Add, then the function button, and paste the same thing from the available values here. Hit ok.

9. Preview the report and verify that when you change the parent parameter, the child parameter changes.

10. Open up the automatically created parameter again and set it to "Hidden". Now the user only sees the "easy" parameter.

So why does this work? Well, aside from needing to convert a date format into a hierarchy address, the key is the available values. When you change the parent date, it invalidates the child's available values and forces the report to create a new set.

This is great. But need to select the values from a query? Do the same thing. Ensure that the selected values from the parent are the only available values for the child. If the parent changes, the child's available values are no longer valid and will be refreshed. I'll post a detailed example of this later.