Number Format in SQL SERVER and SSRS

  • I Want to diplay float value in following format

    can any bodt tell me function for that

    Actaul val Val to display

    188 --> 188

    188.02 --> 188.0

    188.25 --> 188.3

    188.96 --> 189

    Please tell me any function or custom format for this

  • Try With this:

    FormatCurrency((Fields!P_OvertimeRate.Value),3)

  • =Format(Fields!YourFieldName.value, "##,##0.00")

    this works better for float numbers, as you can set the decimals ny changing the format string above

  • Thanks all for replying. But both answers are not satisfying my requirement.

    In Case of currency, this number is simple number, not a currency so I dodn't need any $ sign before these numbers.

    Other thing, IN case of simple integer like 182, I want to display 182 only

    not 182.0

    even in case of 182.96 I want to display 183 not 183.0

    while in case of 182.02 it should be 182.0

    and 182.36 it should be 182.4

    I think now I made it clear.

    Thanks again for your help.

  • =Math.Round(188.36, 1, MidpointRounding.AwayFromZero)

    will work for all but this: while in case of 182.02 it should be 182.0

    View Siva Gurusamy's profile on LinkedIn

    "Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation

  • Try this:

    =iif(

    (YourField.Value/cint(YourField.Value))=1

    ,formatnumber(YourField.Value,0)

    ,formatnumber(YourField.Value,1)

    )

    Just substitute "YourField" with the field your need to evaluate.

    --pete

  • Peter, your exp did not work for this req: even in case of 182.96 I want to display 183 not 183.0

    Modified a bit a to arrive at:

    =iif(int32.tryparse(182.96,182.96),182.96,iif(round(182.96) > 182.96,round(182.96),format(Math.Round(182.96, 1, MidpointRounding.AwayFromZero),"#.0#")))

    Check this out Yadav

    View Siva Gurusamy's profile on LinkedIn

    "Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation

  • Well, shucks. Looks like I tested for situations of 182 vs 182.09; didn't test for 182.99. Good catch.

  • Thanks everybody for replying.

    I was looking for some function, which solve my proble.

    But I didn't find any.

    Now I made my own case

    CASE WHEN CHARINDEX('.',MYVALUE) = 0 THEN CAST(MYVALUE AS VARCHAR)

    WHEN CHARINDEX('.9',MYVALUE) = 0 THEN CAST(CONVERT(DECIMAL(6,1),MYVALUE) AS VARCHAR(7))

    ELSE REPLACE(CAST(CONVERT(DECIMAL(6,1),MYVALUE) AS VARCHAR(7)),'.0','')

    END

    Thanks again to all

Viewing 9 posts - 1 through 8 (of 8 total)

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