1

I've been trying to get this to behave with no luck.

The database base field only has FULL NAME field, which includes Middle initials on some cases and no middle initials in some other cases.

I've been trying to display LastName, FirstName by using this ssrs expression below: While this works in names that doesn't have middle initials or name on the field.... for those names that includes middle initials/name, we run into issues with displaying.

=Right(Fields!agent_name.Value,Len(Fields!agent_name.Value)-Instr(Fields!agent_name.Value," ")) & ", " & (Left(Fields!agent_name.Value,Instr(Fields!agent_name.Value," ")))

But it includes the Middle Initials to display first.. As an example:

If the fullname field is John S Doe, above expression displays as:

S Doe, John

What I need to display is:

Doe, John

How do I set my expression to get rid of the Middle initial/name to display?

I've done a great amount of research and tried many diff expressions but no luck.. thanks in advance.

user3571153
  • 129
  • 1
  • 9

2 Answers2

2

While slightly inefficient, this is a clean way to accomplish the result:

=Split(Fields!agent_name.Value," ")(Split(Fields!agent_name.Value," ").Length-1) + ", " + Split(Fields!agent_name.Value," ")(0)

Edit: The above indeed does not work with suffixes. The expression below will remove a middle initial if its the second name and has a length of 1, which you could use in a calculated field. But it would fail for first name "Ann Marie" and the first/last swap would need to be handled separately.

=IIF(Split(Fields!agent_name.Value," ")(1).Length=1,Replace(Fields!agent_name.Value, " " + Split(Fields!agent_name.Value," ")(1) + " "," "),Fields!agent_name.Value)

The short of it is that you really would be best served handling this in code. But even so, if your fullname field is space-inclusive beyond what you want to seperate on there arises the potential for ambiguous cases, like the suffix- and initial-less "Ann Marie van Der Beek".

bitnine
  • 1,752
  • 1
  • 17
  • 20
  • Thanks this works well but one small issue with this for names with Suffix. For example, Robert K. Downey, JR. if i use this, it is displayed as JR., Robert – user3571153 Aug 18 '16 at 16:10
  • Ah, suffixes. What would the desired output of those be? You could just put in a string replacement of ", " to ",". But if your name database has people like "James Michael D Van Der Beek, Jr" in it, simple expressions are going to get awfully convoluted trying to handle the cases. Particularly if its live and being added to. – bitnine Aug 18 '16 at 16:41
  • Desired output for suffixes would be "Downey, JR., Robert" Thanks for this man. I really appreciate it! – user3571153 Aug 18 '16 at 17:08
1

Try using this expression:

=RIGHT(
Fields!agent_name.Value,LEN(Fields!agent_name.Value)-InStrRev(Fields!agent_name.Value," "))
 & ", " & LEFT(
 Fields!agent_name.Value,
 InStr(Fields!agent_name.Value," ")-1
)

It is a native SSRS solution however I recommend using custom code to get an maintainable solution.

UPDATE: Regex solution:

=System.Text.RegularExpressions.Regex.Replace(
  Fields!agent_name.Value,
  "(\w+)\s+(\w+\.*\s)?(\w+)",
  "$3, $1"
 )

Let me know if this helps.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • Minor note: looks like this expression has an OBOB that clips the last character off the first name. – bitnine Aug 18 '16 at 17:09
  • @bitnine, Thanks for check it. When I test the expression it works for names containing only the first middle name (if any) letter. ie. `John S Doe`, `Allan F Alsop`. How did you test it? – alejandro zuleta Aug 18 '16 at 18:54
  • I used a dataset with the single static line "SELECT 'Joeseph A Bank' AS agent_name;". Then I pasted expressions into a bare-bones Tablix to do a quick check. The result I saw was "Bank, Joesep". – bitnine Aug 18 '16 at 19:01
  • @bitnine, yes, you are right, the error was caused by unnecesary substraction in the expression. Thanks for the time testing. – alejandro zuleta Aug 18 '16 at 21:48