SSRS Expression to Switch LastName, FirstName to FirstName LastName in field

Question

SQL Server 2016 SSRS 2016 IDE Visual Studio 2017

Problem: Report Field contains value of Doe, John

Solution/Output: Using SSRS expression require field to output John Doe

Current sample of my expression that gives me an #error when I run preview:

=Split(Fields!Name.Value,",")(1).ToString() &","& Split(Fields!Name.Value,",")(0).ToString()

I found example above online, however throws an error. Relatively new to SSRS advanced expressions. I don't have the option to edit the T-SQL query

Answer 1

This should work...

=SWITCH(
    split(Fields!Name.Value, ",").length = 1, Fields!Name.Value,
    True, TRIM(split(Fields!Name.Value + ",", ",")(1))
        + " "
        + TRIM(split(Fields!Name.Value + ",", ",")(0))
    )

What I've done here is... for the first SWITCH expression pair, check if the name has only a single element, if it does return the name (e.g. for "not Applicable". This method is safer as it will handle anything with no commas present.

The second part True just acts like an ELSE In this case I've added a comma to the end of the Name field value so that the evaluated string always has at least 1 comma (thus preventing the error). I've also trimmed the results so you don't get unwanted spaces.

We now get these results