SSRS 2005 - Difference between ROUND(x,2) and N2

Recently I have an interesting finding while using ROUND function in SSRS and in SQL Server 2005.

Let me take an example and walk you through my experience:

Consider the following three values:

Input :
1) 10.422
2) 10.425
3) 10.427

Applying ROUND(x,2) function on the above values in an SQL query would give you the following output (respectively)

1) 10.42
2) 10.43
3) 10.43

As expected, the ROUND(x,2) function does a floor when the last digit is less than 5 and does a ceil when the last digit is equal to or greater than 5

So far, so good, now if you do the same thing in SSRS 2005, the results are suprising:

1) 10.42
2) 10.42
3) 10.43

Observe that the ROUND(x,2) function in SSRS 2005 did a floor even when the last digit is equal to 5 !!

I know that it is a bit frustrating when your client calls up and brings this issue up in a system that is in PRODUCTION !!

Here is a solution to make the ROUND(x,2) function in SSRS 2005 give you the expected result in such a situation:

Use ROUND(x,2,System.MidpointRounding.AwayFromZero)

Isn't life so cool now :)

Let me introduce some confusion and solve it myself ;-)

In SSRS 2005, you can set the format on a number field to N2. If you apply N2 and run the report, you would see that it presents the value as expected (just like how a ROUND(x,2) function does in SQL 2005).


Is N2 equal to ROUND(x,2,,System.MidpointRounding.AwayFromZero) ?

The answer is NO - when you look at the report, there is no visible difference but when you export the report to excel, you would observe that the value (after applying N2) is still output as 10.425 although the formatting is set to two decimals.

Here are the screenshots illustrating the difference: