round() Function not consistent

Question

round() Function not consistent

kkam

Say Hey Kid

Points: 661
More actions
March 14, 2007 at 5:57 pm

#171638

Hi,
   Ran into some strange behavior today. I have a column of decimals (18,4) that I want to include in a Reporting Service report displaying only two decimals. For this report the values should be rounded to the second decimal.
   I entered the expression =Round(field.value,2) in the report expecting that would take care of it. What I found is that the "Round" function appears to round inconsistently.
   Here is a sample of what I saw:
           Row 1 > .0417     rounded to: .04
           Row 2 > .1250     rounded to: .12
           Row 3 > .2083     rounded to: .21
           Row 4 > .7292     rounded to:  .73
           Row 5 > .3750     rounded to: .38
   The value in the second row didn't round correctly. It appears to be inconsistently following its own pattern since the fifth row did round correctly.
   Any insights, solutions, or "what were you thinking" comments will be appreciated.

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

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

I don't have your data so not sure what else is going on... the following returns 1.3 as expected... (SQL Server Developer Edition and Enterprise Edition, sp4)

DECLARE @test-2 DECIMAL(18,4)

SET @test-2 = .1250

SELECT Round(@Test,2)

It may be that the Reporting Service uses "Bankers Rounding".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kkam Say Hey Kid Points: 661 More actions · Answer 2

Thanks Jeff,

I guess I wasn't very clear in my initial post. My problem isn't with SQL Server's rounding function, its with Reporting Services rounding function. If I round in SQL everything is great.

I have a stored procedure that I was hoping to use in more than one report. For some users, four digits past the decimal is required, for others only the two.

The data is in my original post. The column in question is stored in the database in decimal(18,4) format.

My quandry is that it appears to round up correctly in most cases but not all. That kind of behavior is rather unfortunate. My gripe is that if I can't trust Reporting Services functions then I'm forced to write multiple views/SP's whenever I run across situations like this.

kkam Say Hey Kid Points: 661 More actions · Answer 3

Round() Function - CAUTION!

If you choose to use the Round() function within a Reporting Services report there are three parameters required to get consistent results. The correct use of the function takes on the form:

=round(Field.Value,n,MidpointRounding.AwayFromZero ).

The parameters are:

Field.value - value to be rounded
n - number of digits following the decimal in the result
MidpointRounding.AwayFromZero - Midpoint enumeration that instructs the function to round up if the next value is 5 or greater.

Note: The possible enumerations for MidpointEnumeration are:

AwayFromZero - When a number is halfway between two others, it is rounded toward the nearest number that is away from zero.
ToEven - When a number is halfway between two others, it is rounded toward the nearest even number.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Wouldn't have figured that one out from BOL, it doesn't show the round function accepting 3 parameters. Curious where you found this info.

kkam Say Hey Kid Points: 661 More actions · Answer 5

Sorry, about that...I should have referenced. I found it at:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=828306&SiteID=1.

There were a few postings on different threads that were pointing users to create their own rounding functions like the last post on that page (Oct 16th, 2006 at 8:34 PM). That didn't make sense to me since they were all telling them to write a wrapper function and then using the VB round() function inside it...the same function that Reporting Services uses!

Once I found the example with a third parameter to Round() I looked up MidpointRounding in BOL.

I still have no understanding of why the round() function didn't give me consistent rounding functionality without the third parameter (and why they don't document it better!).

If you look at my first post, the second and the fifth rows should have followed the same pattern (both in the format .##50). They did not. The second row rounded down, the fifth row rounded up. What's up with that!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Rounding to the nearest even number is an accounting trick (I think it is also called a bankers round). Accountants us it to keep numbers so that they add up consistantly. It's been years since I had any accounting classes, and I don't work closely with any at this time either.

kkam Say Hey Kid Points: 661 More actions · Answer 7

kkam

Say Hey Kid

Points: 661

March 15, 2007 at 3:24 pm

#695361

Thanks.