Home » SQL Server

SSRS option to make capital first letter and rest small letters of a string

In the 'Name' field of String type is appearing all capital letters. I want to appear it as first letter is capital and rest all small. If there is space between two characters, then the second should appear as Caps again.

1. PETER Should appear like Peter
2. ALEXANDAR PETER Should appear as Alexander Peter

Is it possible in SSRS or SQL Query ?
I am using SQL Server 2005 SSRS Reporting Services.



10 Answers Found


Answer 1

There is not RS option  but rather .NET option. Write your own report custom code in VB.NET. It's not a hard problem.


Answer 2

Sure.  You could do this in VB or SQL.  Here's a quick exmaple of doing this using a VB function.  Open the Report Properties dialog and paste this code into the Code window:

Function ProperCase(InputString as String) As String
         Dim i as Integer
         If InputString  <> "" Then
            Mid(InputString , 1, 1) = UCase(Mid(InputString , 1, 1))
            For i = 1 To Len(InputString) - 1
               If Mid(InputString, i, 2) = Chr(13) + Chr(10) Then
                  Mid(InputString, i + 2, 1) = UCase(Mid(InputString, i + 2, 1))
               End If
               If Mid(InputString, i, 1) = " " Then
                  Mid(InputString, i + 1, 1) = UCase(Mid(InputString, i + 1, 1))
               End If
            Return InputString
         End If
End Function

In a textbox, right-click.  Choose Expression... and type:

=Code.ProperCase("the cow jumped over the moon.")

If you want to apply this logic to a field, enter:


Preview the report.

I hope this is helpful.

Paul Turley [Hitachi Consulting] SQLServerBIBlog.com

Answer 3

When I type  '=Code.ProperCase(Fields!<ColumnName>.Value)' in Expression editor, there is no change in the result



Answer 4

Hi Meemkay,

Using Custom Code as Paul mentioned to achieve that is a good choice, it works fine in my case. Did you get some warning message? Or when you type  the expression like this: =Code.ProperCase("the cow jumped over the moon."), can you get the respected result?




Answer 5

Yeah...... it works when i place in the expression editor like this: =Code.ProperCase("the cow jumped over the moon."),but when i place =Code.ProperCase(Fields!SomeFieldName.Value), it doesn't work.
Note: It does not fetch any error messege, indeed the result set is not affected at all.



Answer 6

Strange, it works fine both with field  and string. Could you create three columns for comparison, to see if there are some differences between three columns?



= LCase(Fields!SomeFieldName.Value)

Also, if this issue is caused by the field format, it will through warning message not the error message.

Hope this helps.



Answer 7

It works with =Code.ProperCase(LCase(Fields!SomeFieldName.Value)).
Thanks a lot.....


Answer 8

meemkay, have you seen in the code above the characters  are being set to upper case?

So you will probably have to do something like:


If you source string  is all uppercase

Answer 9

Gracias. me sirvio de mucha ayuda

Answer 10

I use this with no custom code in the Code window.  Much easier.





<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure