none
How do I Format Decimal Places to 0 or 2 Decimal Places RRS feed

  • Question

  • I am using SSRS 2005 and I am trying to show no decimal places if the result is a whole number and show 2 decimal places if it is 1.5

    Eg 1 woud show as 1 and not 1.00 and 1.5 would show as 1.50

    Is this possible?

    Thanks very much


    Darren
    Saturday, October 29, 2011 12:45 PM

Answers

  • Darren,

    Try this one

    =Replace(Round(Fields!Rate.Value,2),".00","")

    • Marked as answer by Darren Thorley Saturday, October 29, 2011 5:07 PM
    Saturday, October 29, 2011 5:03 PM
  • Hi Darren,

    I used the following expression for the field value and I didn't mention any number format, I just left it as default.

    =Round(CDec(Replace(Replace(Fields!Rate.Value,".00",""),"00","")),2)  - use this as a calculated field for better performance. Hope this works for you. 

    • Marked as answer by Darren Thorley Saturday, October 29, 2011 4:21 PM
    • Unmarked as answer by Darren Thorley Saturday, October 29, 2011 4:32 PM
    • Marked as answer by Darren Thorley Saturday, October 29, 2011 4:55 PM
    Saturday, October 29, 2011 4:09 PM

All replies

  • If using BIDS; Go to the Properties Window (F4) and find Number then enter N2 as the format (or N0 is you want no decimal places).

    (Just noticed you're using 2005, not 2008, I'd assume it's similar)


    I work to UK time. If this post, or another, answers your question, please click the 'Mark as answer' button so others with the same problem can find a solution more easily.
    Saturday, October 29, 2011 1:49 PM
  • Sorry I hadn't made myself clear.

    That will only let me do one or the other.

    I am trying to get it so that it sets the format based on whether the value has decimal places or not. If it is a whole number then no decimal places. If it is not a whole number then show 2 decimal places.

    Is this possible?

    Thanks again for any pointers you can give.


    Darren
    Saturday, October 29, 2011 1:53 PM
  • Ah, sorry, my brain re-arranged your example to put the 'not' in a different place!

    In SSRS2008 you can enter an expression as the format, so a conditional expression that returns N0 if it's a whole number and N2 if it isn't should do the trick. Again, not sure if 2005 is the same.


    I work to UK time. If this post, or another, answers your question, please click the 'Mark as answer' button so others with the same problem can find a solution more easily.
    Saturday, October 29, 2011 1:57 PM
  • OK, I get I need to use a conditional formula. Thanks.

    Some of the numbers are up to 5 decimal places (e.g. 1.00001). I know I can use N2 to round this to 1.00 or N0 to 1.

    N2 also displays 1.75001 as 1.75.

    How do I get the conditional formula to recognise 1.00 as 1 and display 1 and how do I get it to recognise 1.75 as 1.75 and display 1.75? I am not sure of the function I need to pick to differentiate between the two number types?


    Darren
    Saturday, October 29, 2011 2:14 PM
  • The usual way is to take the difference between a number and the same number rounded to a whole number; if the absolute value of the difference is bigger than rounding error (e.g. 0.00001) then it's not a whole number.
    I work to UK time. If this post, or another, answers your question, please click the 'Mark as answer' button so others with the same problem can find a solution more easily.
    Saturday, October 29, 2011 2:32 PM
  • Hi Darren,

    I used the following expression for the field value and I didn't mention any number format, I just left it as default.

    =Round(CDec(Replace(Replace(Fields!Rate.Value,".00",""),"00","")),2)  - use this as a calculated field for better performance. Hope this works for you. 

    • Marked as answer by Darren Thorley Saturday, October 29, 2011 4:21 PM
    • Unmarked as answer by Darren Thorley Saturday, October 29, 2011 4:32 PM
    • Marked as answer by Darren Thorley Saturday, October 29, 2011 4:55 PM
    Saturday, October 29, 2011 4:09 PM
  • Hi Neo,

    Perfect!

    Awesome - Thanks very much!


    Darren
    Saturday, October 29, 2011 4:22 PM
  • Oops, spoke too soon.

    The solution strips off the last two zeros of something that is in the hundreds (i.e. 400 becomes 4 and 2500 becomes 25)

    So close to getting down the pub. ;)

    Thanks


    Darren
    Saturday, October 29, 2011 4:32 PM
  • You put me on the right track. I tweaked your solution a little and it now works for me:

     

    =Round(CDec(Replace(Replace(Sum(Fields!RateCardCost.Value),".00",""),"0000","")),2)

    Thanks - pub here I come!


    Darren
    Saturday, October 29, 2011 4:55 PM
  • Darren,

    Try this one

    =Replace(Round(Fields!Rate.Value,2),".00","")

    • Marked as answer by Darren Thorley Saturday, October 29, 2011 5:07 PM
    Saturday, October 29, 2011 5:03 PM
  • Works even better. Thanks
    Darren
    Saturday, October 29, 2011 5:08 PM