I’ve been on a bit of an SSRS report writing kick at work as of late. One report in particular rounds off values for display where the loss of precision is appropriate. For example, if I’m showing a percentage and I only care about the nearest whole percent (53% not 52.66667%).
As a young person, when we’re introduced to the concept of rounding it is common for us to learn than anything less than X.5 rounds down to X and that X.5 and above round up to X + 1. This is what I’d expected to see, and is how rounding in SQL Server functions:
SELECT ROUND(52.5, 0) -- Yields 53.0
SELECT ROUND(53.49, 0) -- Yields 53.00
SELECT ROUND(53.5, 0) -- Yields 54.0
To accomplish this format, I had been making use of the “Round” function in SSRS with the understanding that it would round the value. As it turns out, the round function in SSRS does round, but makes use of a slightly different default rounding algorithm than that of SQL Server. In my report in SSRS:
=Round(52.5, 0) ' Yields 52.0
=Round(53.49, 0) ' Yields 53.00
=Round(53.5, 0) ' Yields 54.0
What’s going on here? This is what’s commonly referred to as “Banker’s Rounding” in action. Where a half value (X.5) is rounded to the nearest EVEN value rather than always rounding up. Wikipedia has a much more extensive write-up on rounding methods.
On the one hand, I’ve already determined that a loss of precision is acceptable so one might think the default rounding method should also not be of concern. However, SSRS is based on SQL Server and it doesn’t seem far fetched to imagine that they might be consistent within the suite of tools.
I should reiterate that the rounding method in question is merely the default. This can be changed by passing in a third parameter which specifies the rounding method to use. If for example, you would like some consistency and you choose to round the SQL Server way try:
=Round(52.5, 0, MidpointRounding.AwayFromZero) ' Yields 53.0
=Round(53.49, 0, MidpointRounding.AwayFromZero) ' Yields 53.00
=Round(53.5, 0, MidpointRounding.AwayFromZero) ' Yields 54.0
However you implement it, a little consistency is always a good thing.