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)
Then, against the URL field in the report (or hidden parameter value in this case), we put this expression;
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.