Function to Case Text within a Field

Question

Post reply

Function to Case Text within a Field

shelts

SSChasing Mays

Points: 618
More actions
November 28, 2014 at 6:57 am

#301456

Hi
We have a user who wants to just case the First word in a string. For example, they have a field Event_Type which says Home Visit and they want it to say Home visit
I am able to get it to display all lowercase or all uppercase but cannot find anything that relates to just casing the first letter!
Any help would be much appreciated
Cheers

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

I don't know much about SSRS (other than spell it), but this could give you an idea.

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

shelts SSChasing Mays Points: 618 More actions · Answer 2

Luis Cazares (11/28/2014)
I don't know much about SSRS (other than spell it), but this could give you an idea.
=UCase(Left(Fields!Event_Type.Value)) & LCase(Right(Fields!Event_Type.Value, Len(Fields!Event_Type.Value) - 1))

thanks, I'll give it a go

HD_JOHN SSC Rookie Points: 36 More actions · Answer 3

They can add code to the report in the report properties (I have provided an example below). I have done this before and it works. Let me know if you need any more help.

Public Function FirstWordUpper(ByVal word As String)

Dim newString As String = ""

Dim words()

words = word.Split(New Char() {" "c}, 2)

If words.Length > 1 Then

newString = UCase(words(0)) + " " + words(1)

Else

newString = words(0)

End If

Return newString

End Function

then in the field they want changed add the expression

=FirstWordUpper(Fields!Event_Type.Value)

shelts SSChasing Mays Points: 618 More actions · Answer 4

Thanks to all who replied, neither scenario appears to work though 🙁

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

shelts (12/4/2014)
Thanks to all who replied, neither scenario appears to work though 🙁

Why not? If you get more details, you could get better answers. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

shelts SSChasing Mays Points: 618 More actions · Answer 6

Luis Cazares (12/4/2014)
shelts (12/4/2014)
Thanks to all who replied, neither scenario appears to work though 🙁
Why not? If you get more details, you could get better answers. 😉

Sorry, with your solution I got an error

The Value expression for the textrun ‘Event_Type.Paragraphs[0].TextRuns[0]’ contains an error: [BC30455] Argument not specified for parameter 'Length' of 'Public Function Left(str As String, Length As Integer) As String'.

at Microsoft.ReportingServices.Library.ReportingService2005Impl.SetReportDefinition(String Report, Byte[] Definition, Guid batchId, Warning[]& Warnings)

at Microsoft.ReportingServices.Library.ReportingService2010Impl.SetItemDefinition(String ItemPath, Byte[] Definition, String expectedItemTypeName, Property[] Properties, Warning[]& Warnings)

at Microsoft.ReportingServices.WebServer.ReportingService2010.SetItemDefinition(String ItemPath, Byte[] Definition, Property[] Properties, Warning[]& Warnings)

HD_JOHN SSC Rookie Points: 36 More actions · Answer 7

After talking with you briefly and understanding more this is what I came up with for anyone else that might need this.

Public Function FirstWordProper(ByVal word As String)

Dim newString As String = ""

Dim words()

words = word.Split(New Char() {" "c}, 2)

If words.Length > 1 Then

newString = UCase(words(0).ToString().Substring(0, 1)) + LCase(words(0).ToString().Substring(1, words(0).ToString().Length - 1)) + " " + LCase(words(1).ToString())

Else

newString = words(0)

End If

Return newString

End Function

shelts SSChasing Mays Points: 618 More actions · Answer 8

HD_JOHN (12/4/2014)
After talking with you briefly and understanding more this is what I came up with for anyone else that might need this.
Public Function FirstWordProper(ByVal word As String)
Dim newString As String = ""
Dim words()
words = word.Split(New Char() {" "c}, 2)
If words.Length > 1 Then
newString = UCase(words(0).ToString().Substring(0, 1)) + LCase(words(0).ToString().Substring(1, words(0).ToString().Length - 1)) + " " + LCase(words(1).ToString().Substring(0, 1)) + words(1).ToString().Substring(1, words(1).ToString().Length - 1)
Else
newString = words(0)
End If
Return newString
End Function

It works perfectly, thanks very much for your help

The expression needed to run it is

=Code.FirstWordProper(Fields!Event_Type.Value)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 9

Yes, I made a mistake by not including the 2nd parameter of LEFT function. You should have been able to solve it by yourself.

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

shelts SSChasing Mays Points: 618 More actions · Answer 10

Luis Cazares (12/4/2014)
Yes, I made a mistake by not including the 2nd parameter of LEFT function. You should have been able to solve it by yourself.
=UCase(Left(Fields!Event_Type.Value,1)) & LCase(Right(Fields!Event_Type.Value, Len(Fields!Event_Type.Value) - 1))

Thanks, this also works perfectly, I did try but couldn't figure it out for some reason. Thanks for your help