Using Regular Expressions in SSRS to get the Server Name Portion of a URL

SQL Server Reporting Services (SSRS) has an in-built function called Globals!ReportServerUrl that allows you to return the URL of the SSRS report on the server. If you are looking at customizing part of your SSRS report depending on the Server that it is hosted on, then this is a useful function.

However, the problem is that the Globals!ReportServerUrl function returns the entire URL. If, for example, you want to point to a different URL depending on the server, then you will likely want to get just the server name portion of the full URL. There are several ways of doing this including: a simple InStr check, using in-built VB string parsing functions, or using a Regular Expression.

In most cases, I’d say that using a Regular Expression is the most fail-safe and powerful way of parsing string values.

The Regular Expression that we’ll use is as follows:

^(http)(s{0,1})(\:\/\/)([^/]+)

This expression will extract the server name from the URL of the server hosting the SSRS report. So, for example, in a URL such as:

http : // testServer/Reports/blah

What this Regular Expression Does

The above regular expression will get the testServer portion of the URL. The expression will also handle https:// but if you expect the url to include a www. portion, then you will need to add your own code to adjust the expression slightly.

The expression will also get the server name from the URL even if it is not followed by further path information (for example: http : //testServer).

SSRS support for Regular Expressions is comprehensive, and in this example, we make use of the Replace function to extract the portion of the URL that we are interested in by placing the various parts of the regular expression in groups using parentheses.

VB Alternatives

You could alternately look into using VB-specific functions such as Mid( Here is an example of using VB to extract the Server Name which I have seen in several different help sites on the Web:

Mid(Globals!ReportServerUrl,8,InStr(9,Globals!ReportServerUrl,”/”) – 8)

I would personally suggest using the regular expression, rather than the above VB, since it contains less hardcoding for the http(s):// portion of the URL. And in general, using a Regular Expression will be less prone to breaking.

If you want the simplest possible solution, then you could do an InStr check for the server name. This would also be a reasonable solution if you know that the name of the server will remain fairly static, and that the server name will not be repeated elsewhere in the URL. Here is an example of how you would write a check like this:

=iif(InStr(Globals!ReportServerUrl,"testServer")>0,"Server is as expected", "Not the Expected Server")

Using a Regular Expression to Get the Server Name in SSRS

But let’s say that you want to use the Regular Expression that I explained above in your SSRS report to extract the Server URL. Once you know your Regular Expression syntax, it is fairly easy to plug it into your report using the SSRS Regex.Replace function (System.Text.RegularExpressions.Regex.Replace). Here is the Syntax that you could use to do this:

=System.Text.RegularExpressions.Regex.Replace(Globals!ReportServerUrl, "^(http)(s{0,1})(\:\/\/)([^/]+)", "$4")

As you can see, the regular expression uses groups and extracts the server-portion using the $N syntax. So in the example above, the $4 is used in the Regex.Replace function to get the fourth group, which is the Server Name.

Using IIF Logic Along with your Regular Expression

Ok, so let’s say that you want to do something a bit fancier in your code now that you are able to extract the Server Name from a URL. In the example line below I show you how to encapsulate the Server Name extraction inside of an SSRS iif statement. This could be useful if you wanted to create a link to a specific site depending on what the Server Name hosting the SSRS reports is.

=iif((System.Text.RegularExpressions.Regex.Replace(Globals!ReportServerUrl, "^(http)(s{0,1})(\:\/\/)([^/]+)(.*)", "$4"))="testServer","goToServer1","goToServer2")

Directing to Different URLs with Fields from your Report as Parameters

So taking the code above, if you wanted to direct to actual URLs that contain variable parameters that you can get from your SSRS report, here is an example of what this code might look like:

=iif((System.Text.RegularExpressions.Regex.Replace(Globals!ReportServerUrl, "^(http)(s{0,1})(\:\/\/)([^/]+)(.*)", "$4"))="testServer","http://testServerURL/goHere.aspx?id=" & Fields!TestId.Value & "&Name=" & Fields!FName.Value & "&info=url1”,"http://OtherServerURL/APageName.aspx?id=" & Fields!Id.Value & "&Name=" & Fields!FName.Value & "&info=url2")

Other Resources

If you found this article useful, then feel free to also check out some other posts that I have written about SSRS:

  1. SSRS Basics: Set up Visual Studio to Write a New SSRS Report
  2. SSRS Adding User Selectable Parameter Options

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s