Reducing the length of a returned values in SSRS where a certain amount of seperators exist

A customer recently asked if they could remove the end part of several URLs, e.g. after the 5th slash e.g. https://spfactoryuk.wordpress.com has 2 slashes, or adding onto that URL /subsite would have 3 slashes, /subsite/evenlowersite would then have 4 etc.

They wanted to remove the 5th one onwards in a list of returned URLs to help with a parameter they had (effectively they didn’t want to select the bottom site where applicable).

So to do this in SSRS, add the following code to the report Properties > Code section;

Public Function ReduceURL(Combined As String, Position As Integer) As String
if (Split(Combined, “/”).Length >= Position) Then
Return Split(Combined, “/”).GetValue(Position-1)
Else
Return “”
End If
End Function

Then, against the URL field in the report (or hidden parameter value in this case), we put this expression;

=Code.ReduceURL(Fields!MemberValue.Value,5)

This could be adapted to reduce the length where other separators are concerned too – e.g. commas instead of slashes could be put into the code.

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