sql reporting-services rdlc ssrs-tablix rdl

Rounding to nearest 0.5 in SSRS reporting .rdl

I am trying to round to the nearest 0.5 half. HELP.....I am going round in circles!!

I am adding 4 numbers together and dividing the total to give me the nearest 0.5;

Example:

(5+4+4+4) = 17 / 4 result is 4.25. I want this to show as 4.5 but this shows as 4.

Another example: (5.5+5+5+5.5) = 21 / result is 5.25 and I would like to round this to 5.5 but this rounds down to 5.

Also if the resulting value is for example 4.24 then this should be rounded to 4.

Here is the expression in the report:

=ROUND(
IIF(CDec(IIF((Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "sP", Fields!txtResult.Value, Nothing))) <> "",
Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "sP", Fields!txtResult.Value, Nothing)), 0)) +
CDec(IIF((Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "mP", Fields!txtResult.Value, Nothing))) <> "",
Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "mP", Fields!txtResult.Value, Nothing)), 0))
>0,
(CDEC(IIF((Max(IIF(Fields!intReportCycleID.Value = 41 And Fields!intReportCycleTerm.Value = 1, Fields!txtResult.Value, Nothing))) <>"",
Max(IIF(Fields!intReportCycleID.Value = 41 AND Fields!intReportCycleTerm.Value = 1, Fields!txtResult.Value, Nothing)),0)) 0.25)
+
(CDEC(IIF((Max(IIF(Fields!intReportCycleID.Value = 47 And Fields!intReportCycleTerm.Value = 2, Fields!txtResult.Value, Nothing))) <>"",
Max(IIF(Fields!intReportCycleID.Value = 47 AND Fields!intReportCycleTerm.Value = 2, Fields!txtResult.Value, Nothing)),0)) 0.25)
+
((CDec(IIF((Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "sP", Fields!txtResult.Value, Nothing))) <> "",
Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "sP", Fields!txtResult.Value, Nothing)), 0)) 0.25)
+
CDec(IIF((Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "mP", Fields!txtResult.Value, Nothing))) <> "",
Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "mP", Fields!txtResult.Value, Nothing)), 0)) 0.25)
,
(CDEC(IIF((Max(IIF(Fields!intReportCycleID.Value = 41 And Fields!intReportCycleTerm.Value = 1, Fields!txtResult.Value, Nothing))) <>"",
Max(IIF(Fields!intReportCycleID.Value = 41 And Fields!intReportCycleTerm.Value = 1, Fields!txtResult.Value, Nothing)),0)) 0.50)
+
(CDEC(IIF((Max(IIF(Fields!intReportCycleID.Value = 47 And Fields!intReportCycleTerm.Value = 2, Fields!txtResult.Value, Nothing))) <>"",
Max(IIF(Fields!intReportCycleID.Value = 47 And Fields!intReportCycleTerm.Value = 2, Fields!txtResult.Value, Nothing)),0)) 0.50)
)
* 2)/2

Thank you in advance and hope someone can help.

Solution

SSRS uses what is sometimes known as Bankers Rounding. You can choose to override that...

=Round(4.5, 0, MidpointRounding.AwayFromZero)
=> 5

EDIT: As you asked me to be explicit...

=ROUND(
IIF(CDec(IIF((Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "sP", Fields!txtResult.Value, Nothing))) <> "",
Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "sP", Fields!txtResult.Value, Nothing)), 0)) +
CDec(IIF((Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "mP", Fields!txtResult.Value, Nothing))) <> "",
Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "mP", Fields!txtResult.Value, Nothing)), 0))
>0,
(CDEC(IIF((Max(IIF(Fields!intReportCycleID.Value = 41 And Fields!intReportCycleTerm.Value = 1, Fields!txtResult.Value, Nothing))) <>"",
Max(IIF(Fields!intReportCycleID.Value = 41 AND Fields!intReportCycleTerm.Value = 1, Fields!txtResult.Value, Nothing)),0)) 0.25)
+
(CDEC(IIF((Max(IIF(Fields!intReportCycleID.Value = 47 And Fields!intReportCycleTerm.Value = 2, Fields!txtResult.Value, Nothing))) <>"",
Max(IIF(Fields!intReportCycleID.Value = 47 AND Fields!intReportCycleTerm.Value = 2, Fields!txtResult.Value, Nothing)),0)) 0.25)
+
((CDec(IIF((Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "sP", Fields!txtResult.Value, Nothing))) <> "",
Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "sP", Fields!txtResult.Value, Nothing)), 0)) 0.25)
+
CDec(IIF((Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "mP", Fields!txtResult.Value, Nothing))) <> "",
Max(IIF(Fields!intReportCycleID.Value = 50 AND Fields!txtResultName.Value = "mP", Fields!txtResult.Value, Nothing)), 0)) 0.25)
,
(CDEC(IIF((Max(IIF(Fields!intReportCycleID.Value = 41 And Fields!intReportCycleTerm.Value = 1, Fields!txtResult.Value, Nothing))) <>"",
Max(IIF(Fields!intReportCycleID.Value = 41 And Fields!intReportCycleTerm.Value = 1, Fields!txtResult.Value, Nothing)),0)) 0.50)
+
(CDEC(IIF((Max(IIF(Fields!intReportCycleID.Value = 47 And Fields!intReportCycleTerm.Value = 2, Fields!txtResult.Value, Nothing))) <>"",
Max(IIF(Fields!intReportCycleID.Value = 47 And Fields!intReportCycleTerm.Value = 2, Fields!txtResult.Value, Nothing)),0)) 0.50)
)
-- Keep your `* 2) / 2`
-- But add that you want 0 decimal places, and round midpoint away from zero
* 2, 0, MidpointRounding.AwayFromZero)/2