sql - 在 SSRS 报告 .rdl 中四舍五入到最接近的 0.5

Question

我试图四舍五入到最接近的 0.5 一半。帮助.....我在绕圈子!
我将 4 个数字相加，然后将总数除以得出最接近的 0.5；
例子:
(5+4+4+4) = 17/4 结果是 4.25。我希望它显示为 4.5，但这显示为 4。
另一个例子:(5.5+5+5+5.5) = 21/结果是 5.25，我想把它四舍五入到 5.5，但这四舍五入到 5。
此外，如果结果值是例如 4.24，那么这应该四舍五入为 4。
这是报告中的表达:

=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

在此先感谢您，并希望有人可以提供帮助。

Answer 1

SSRS 使用有时称为银行家舍入的方法。您可以选择覆盖...

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

编辑 : 正如你要求我明确...

=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