Formatting using the Rounding Function

Looking to improve your Crystal Reports skills? Check out Webucator’s Crystal Reports classes.

In general, Crystal Reports users set up the default formatting to display the data in the way it is to be used most often. However, when it is necessary to be able to switch quickly to another format – right-clicking to format the field is usually the solution.

If the data field comes down to Crystal Reports with several decimal points, one solution would be to use the remove decimal places button on the tool formatting bar. That button actually rounds up on decimal place numbers greater than 5 and rounds down on decimal place numbers less than 5.

If that data is included in a formula, the Round function could be used on the number to be formatted. To give the report designer more control there are actually two functions for rounding numbers: Round and RoundUp. Each of these rounding functions have two sub-functions, which we will see below.

Round Function

The syntax is simply Round (x), with x being the numeric or currency field to be rounded. Here are some examples of the Round function:

  • Round(1.234) = 1
  • Round(1.4999) = 1
  • Round(1.5000) = 2

You can also add a second argument to the Round function to indicate how many places to be rounded to as represented by y here: Round(x, y). Some more examples:

  • Round(1.234,2) = 1.23
  • Round(1234.2345,2) = 1234.23
  • Round(2345.23456,0) = 2345

RoundUp Function

The syntax is RoundUp(x), again with x being the numeric or currency field to be rounded. Here are some examples of the RoundUp function:

  • RoundUp(4.234) = 5
  • RoundUp(1.4999) = 2
  • RoundUp(1.25000) = 2

Again, you can also add a second argument to the RoundUp function to indicate how many places to be rounded to as represented by y here: RoundUp(x, y). Some more examples:

  • RoundUp(1.234,2) = 1.25
  • RoundUp(6.36521,3) = 6.366
  • RoundUp(2345.23456,0) = 2345

If the second argument is left blank then the function rounds up to the nearest integer. In other words, 0 is the default for the second argument.

All this sounds complex, but it really isn’t. I would suggest that you try it on simple numbers with decimals first. Then, when you have the expected results, place it in your Crystal Report using it with the database field.

Taking it a step further, as in Arne’s comment – an if-then-else operator would have to be incorporated using syntax similar to this example:

If region in[CA, MA, FL] then Round({orderdetails.orderamount},2)
Else Round({orderdetails.orderamount},0)

Happy Rounding with Crystal Reports!!