CODEWITHSUNDEEP
reporting-servicesexpressionrounding
TracyJ
TracyJ

Reputation: 13

Error when rounding to nearest 10 in SSRS Expression

I've searched the existing threads and can't figure out where I'm doing it wrong...

I'm trying to get the average of two values and then round the average to the nearest TEN (10).

My expression is:

=ROUND((Fields!Cyl1Stress.Value + Fields!Cyl2Stress.Value) / 2, -1)

The above returns an error.

Rounding to the nearest TENTH works fine, but as soon as I change the 1 to -1, I get the error.

Where Cyl1Stress.Value = 7600 and Cyl2Stress.Value = 7490.

=ROUND((Fields!Cyl1Stress.Value + Fields!Cyl2Stress.Value) / 2, **1**)

The above code returns 7545.

But I need the result to be 7550, so I change my formula to:

=ROUND((Fields!Cyl1Stress.Value + Fields!Cyl2Stress.Value) / 2, **-1**).

This one returns an error.

Can't figure out why this isn't working!

Text Box Properties are Numeric, 0 decimal places.

Upvotes: 1

Views: 1241

Answers (1)

C Black
C Black

Reputation: 1008

I don't believe you can use a negative value for the Digits argument of Round. The documentation indicates:

The number of fractional digits in the return value. For Decimal values, it can range from 0 to 28. For Double values, it can range from 0 to 15.

You can accomplish what you are looking to do by dividing the value by 10, round to the nearest whole number, and then multiply by 10. Also note that if you want a value ending in 5 to round up to the next 10, you will need to add a third argument to Round to indicate this, otherwise I believe the default behavior rounds to the nearest even number. This would cause your example to round to 7540 instead of 7550.

This expression should be what you are looking for:

=ROUND(((Fields!Cyl1Stress.Value + Fields!Cyl2Stress.Value) / 2) / 10, 0, MidpointRounding.AwayFromZero) * 10

For your example values, this turns 7545 into 754.5, rounds to the nearest whole number and rounds it away from zero instead of to the nearest even number (755), then multiplies by 10 (7550).

Upvotes: 3

Related Questions