Combining an IIF statement with correct capitalisation - SSRS 2008R2

Question

Any advice appreciated please.

In many of our reports, some text comes through in upper case, eg a name as:

JOHN SMITH

Or in lower case - john smith

Is there any code / an expression we can use to allow regular capitalisation, so the data always appears (in a report) as:

John Smith ?

However this is further complicated by there already being an IIF expression to display the name in the report, eg: =IIF(fields!x.value="", fields!title.value & " " & Fields!Forenames.Value & " " &fields!surname.Value, "no name") - any suggestions on how to combine this expression with one that returns regular capitalisation would be appreciated.

Answer 1

There is no function I am aware of to change to Title Case. You have to do the following:

=UCase(Left(Fields!Forenames.Value, 1)) & LCase(Right(Fields!Forenames.Value, Len(Fields!FieldName.Value)-1))

This basically gets the first letter and Capitalizes it, then works out the Length of the field, decreases it by one, and uses that length to get the rest of the field, transforming it to lower case.

This will fit snugly into your expression with no dramas (in theory!)

=IIF(fields!x.value="", fields!title.value & " " & UCase(Left(Fields!Forenames.Value, 1)) & LCase(Right(Fields!Forenames.Value, Len(Fields!FieldName.Value)-1)) & " " & UCase(Left(Fields!surname.Value, 1)) & LCase(Right(Fieldsurname.Value, Len(Fields!surname.Value)-1)), "no name")

EDIT: Sorry, sorry, I'm getting my languages mixed up. There's no Upper() and Lower() in report builder, it's UCase() and LCase() instead. Edited my reply to reflect that.