CODEWITHSUNDEEP
sql-serverreportingreportbuilder3.0
LondonUK
LondonUK

Reputation: 517

capitalize first letter in Report Builder filed

I am writing a report using report builder and the one of the filed value of the data set is in upper-case I want to capitalize first letter of this data field value in the text box. Please let me know if you could help.

Thanks Yogi

Upvotes: 3

Views: 5953

Answers (2)

Galactus
Galactus

Reputation: 91

In the report, you can do the casing with the Common Text Fuction StrConv() to make proper casing (first character of every word in capitals):

StrConv([Field].value, vbProperCase)

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/strconv-function

If you only want the first character capitalized (and leave the rest as is):

UCase(Left([Field].value, 1)) & Right([Field].value, Len([Field].value) - 1)

(the question title refers to Report Builder, so the given answer in SQL might not suit erveryones needs)

Upvotes: 7

John Bell
John Bell

Reputation: 2350

I'm assuming you're using a query from the database to fetch your data through a connection object. Based on what you've said here, a reasonable approach would be:

SELECT UPPER(LEFT([FIELD],1))+LOWER(SUBSTRING([FIELD],2,LEN([FIELD])))

If you have more than one word in your data, you will have to create a UDF to handle to pascal casing:

CREATE FUNCTION [dbo].[pCase] 
( 
    @strIn VARCHAR(255) 
) 
RETURNS VARCHAR(255) 
AS 
BEGIN 
    IF @strIn IS NULL 
        RETURN NULL 

    DECLARE 
        @strOut VARCHAR(255), 
        @i INT, 
        @Up BIT, 
        @c VARCHAR(2) 

    SELECT 
        @strOut = '', 
        @i = 0,  
        @Up = 1 

    WHILE @i <= DATALENGTH(@strIn) 
    BEGIN 
        SET @c = SUBSTRING(@strIn,@i,1) 
        IF @c IN (' ','-','''') 
        BEGIN 
            SET @strOut = @strOut + @c 
            SET @Up = 1 
        END 
        ELSE 
        BEGIN 
            IF @up = 1 
                SET @c = UPPER(@c) 
            ELSE 
                SET @c = LOWER(@c) 

            SET @strOut = @strOut + @c 
            SET @Up = 0 
        END 
        SET @i = @i + 1 
    END 
    RETURN @strOut 
END

And then utilise the function like so:

SELECT DBO.PCASE([FIELD])

Upvotes: 1

Related Questions