Using multiple VB Mid functions on a parameter in SSRS

user_9004093 0
Viewed 121

Below is an expression I am using to pull out the forename from a user ID

i.e. "text\[forename] [Surname]"

at the moment this expression is suitable if " " is between the forename and surname. However, what i have noticed is that " " can be replaced by "." or "/" or "_" for example.

The trouble I am having is including multiple "Mid" functions to include all these options. I have attempted using "Or", "Switch" and a number of other operators with no effect.

I am not using a dataset, rather a parameter within the report i.e. when a user logs onto the report, their UserID is taken as the paramter.

Am I missing something obvious or have i missed the mark completely?

Any help would be greatly appreciated.

= "Hi " + Mid(Parameters!User.Value, InStr(Parameters!User.Value,"\")+1, (InStr(Parameters!User.Value, " ") - InStr(Parameters!User.Value, "\") - 1)) + "," &
chr(10) & chr(13) &
chr(10) & chr(13) &
"You have access to the Teams listed below. Only data from these teams will be shown in any analysis. Please click above to continue to the Dashboards"
reporting-services vb.net
1 Answers

Regex is definitely the way to go for something like this. Here's an example of how you could pull out the forename with regex:

Dim input As String = Parameters!User.Value
Dim pattern As String = "(?<=\\)[^ ./_]+"
Dim forename As String = Regex.Match(input, pattern).Value
Dim output As String = $"Hi {forename},

You have access to the Teams listed below. Only data from these teams will be shown in any analysis. Please click above to continue to the Dashboards"

VB.NET now allows multi-line string literals, so there's no need to use Chr(10) & Chr(13). Even if you don't use a multi-line literal, it would be better to use Environment.NewLine or vbCrLf depending on your needs. The $ before the string literal makes it an interpolated string, which means that you can insert variable values in {} brackets in the middle of the string. If you don't want to use that, you could just concatenate as you were doing before:

Dim output As String = "Hi " & forename & 
    Environment.NewLine & 
    Environment.NewLine &
    "You have access to the Teams listed below. Only data from these teams will be shown in any analysis. Please click above to continue to the Dashboards"

Regex is a powerful tool for searching, parsing, and transforming strings. If you're going to be doing a lot of work with data, it will be well worth your effort to learn it.

If you don't want to use regex, I would recommend using String.Split, since that method allows you can pass it any number of delimiters. For instance, you could do something like this:

Dim input As String = Parameters!User.Value
Dim parts() As String = input.Split("\"c, " "c, "."c, "/"c, "_"c)
Dim forename As String = If(parts.Length >= 2, parts(1), Nothing)
user_13596680
Related