Numeric Formatting - Display Zero as Dash?

  • peterzeke

    SSCrazy Eights

    Points: 8998

    I might be overlooking the obvious, but I can't figure out how to display a zero (0) as a "-" in SSRS 2005 using Format codes. I have a column that contains either a 1 or 0, so rather than display zero, I'd like to display a dash. (I want to avoid using IIF() or Switch() functions)

    Example:

    Current Output=

    ClientTrained?

    ==========

    0

    0

    0

    1

    0

    1

    Desired Output=

    ClientTrained?

    ==========

    -

    -

    -

    1

    -

    1

    Thanks,

    Pete

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    2008 has a - for a 0 built in. When I choose it the expression it creates is:

    0.00;(0.00);'-'

    Try putting that in your format.

  • durai nagarajan

    SSCoach

    Points: 15656

    looks same like iif

    can i know why you want to avoid iif

    thanks

    Regards
    Durai Nagarajan

  • peterzeke

    SSCrazy Eights

    Points: 8998

    durai nagarajan (5/12/2011)


    looks same like iif

    can i know why you want to avoid iif

    thanks

    I have about a dozen columns in a table where the field values are either 1 or 0. For satisfactory display, it would be best to format the zeros to dashes. If a simple format code exists, then it saves the effort of having to write conditional expressions for each field.

  • peterzeke

    SSCrazy Eights

    Points: 8998

    Daniel Bowlin (5/12/2011)


    2008 has a - for a 0 built in. When I choose it the expression it creates is:

    0.00;(0.00);'-'

    Try putting that in your format.

    Daniel -- thanks for checking SSRS 2008. That format code works. Ironically, as I was saving the formatting expression in a plain ol' text file within my "SSRS bag of tricks" folder, I discovered I had come up with the formatting code a long time ago -- I just didn't label the file very well.

    So, I have your format code along with my original one =

    #,###;(#,###);-

    Looks like the third segment is what handles zeros.

    Thanks again,

    Pete

  • durai nagarajan

    SSCoach

    Points: 15656

    Is ther any way around for ssrs 2005.

    thanks in advance.

    Regards
    Durai Nagarajan

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    That format should work in 2005 also.

  • dbanks 93850

    SSC Journeyman

    Points: 88

    Ideally you want #,##0.00;(#,##0.00);'-' Add a 0 if you don't want the 0 to drop off.

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

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