Rounding issue in calculated field using round (,1) function

I have a couple of calculated fields in a BI Dev Studio Report as follows:

1. round(Fields!CM_Perf_1st_Mth.Value, 1)

2. round(Fields!CM_Perf_1st_Mth.Value, 1) - round(Fields!BM_Perf_1st_Mth.Value, 1)

The first calc field above is returning wrong results i.e. for a value of 2.25, instead of returning 2.3, it is returning 2.2. Similarly for -0.05, it is returning 0.0, instead of -0.1.

Since the results from the first function are wrong, the second function is also returning off values.

Has anyone faced this issue? How does one get around this? I have SQL 2005 Reporting Services with SP1. The result was the same without SP1 also. Seems like a big bug in the round function...

TIA.

Had similiar issues and went with doing rounding functions in a custom code function.

Try using the functions there, you will see a difference.

Daryl

|||The dataset is coming from a SQL Server 2000 database stored proc.|||

doesn't matter where the dataset is coming from. use the =Code.myroundfunction in the field, and pass the field to the code function.

IE:

field1

if you had "=round(field!myfield.value)"

change it to =code.myroundfunction(field!myfield.value)

|||what does the raw sql data look like?|||

Wrote a custom code fx as:

Public Function MyRound(byVal x as decimal, byval y as int16) as decimal
return round(x,y)
end function

Calling this from the calculated field expression does not make a difference at all.

And here's the raw data:

dbo.Composite_Performance

Composite_Code

LU_Weight_Type_Code

Perf_Date

LU_Data_Stage

Perf_Gross

lc1s

ac

9/30/06

Prelim

2.25

dbo.Composite_Performance

Composite_Code

LU_Weight_Type_Code

Perf_Date

LU_Data_Stage

Perf_Gross

ls

ac

9/30/06

Prelim

-0.05

Any other ideas would be greatly appreciated as I am stuck on this and the deliverable is long due and this is the only issue left... TIA.

|||

Here is a link to another forum which should help you out.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=315706&SiteId=1

Daryl

|||

This function works for me.

Public Function MyRound(ByVal x As Decimal, ByVal y As Integer) As Decimal

Return Round(x, y, MidpointRounding.AwayFromZero)

End Function

the definition for round is really confusing.

Daryl