Home » SQL ServerRSS

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

Hi,
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.

Ex:
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.

Thanks,

 

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
            Next
            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:

=Code.ProperCase(Fields!SomeFieldName.Value)

Preview the report.

I hope this is helpful.

Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
 

Answer 3

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

Thanks,

 

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?

Regards,

Raymond

 

Answer 5

Hi,
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.

Thanks,

 

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?

=Code.ProperCase(LCase(Fields!SomeFieldName.Value))

=Code.ProperCase(cstr(Fields!SomeFieldName.Value))

= 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.

Raymond

 

Answer 7

Hi,
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:

=Code.ProperCase(LCase(Fields!SomeFieldName.Value))

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.

=strConv(Fields!CUST_NAME.Value,vbProperCase)

 

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter