rdl中的SSRS表达式除以零

Question

我应该如何处理SSRS 2017的rdl表达式中的除以零错误？

我尝试过以下方法，但都不起作用。

1.

=iif((iif((SUM(Fields!Consumption.Value)/
(iif((Fields!daysINDate.Value)<=0,1,Fields!daysINDate.Value))*30)<=0,0,
(SUM(Fields!Consumption.Value)/
(iif((Fields!daysINDate.Value)<=0,1,Fields!daysINDate.Value))*30)))=0,0,
SUM(Fields!ClosingStock.Value)/(iif((SUM(Fields!Consumption.Value)/
(iif((Fields!daysINDate.Value)<=0,1,Fields!daysINDate.Value))*30)<=0,0,
(SUM(Fields!Consumption.Value)/
(iif((Fields!daysINDate.Value)<=0,1,Fields!daysINDate.Value))*30))))*30)

2. (SUM(字段！ClosingStock.Value(/(iif(SUM(字段！消耗量.Value(/(iif(Fields！daysINDate.Value<=0.1，Fields！daysINDate.Value((*30(<0,0，(SUM(字段！消耗值(/(iif(Fields！daysINDate.Value<=0.1，Fields！daysINDate.Value((*30(

Answer 1

您应该只检查分母是否为零，而不是除法运算。

=IIF(Fields!daysINDate.Value = 0, 0, SUM(Fields!Consumption.Value))
/
IIF(Fields!daysINDate.Value = 0, 1, Fields!daysINDate.Value) 
*30

Answer 2

SSRS检查IIF语句的两侧。。。如果THEN或ELSE部分IIF为0，它将抛出一个错误。

绕过它的一种方法是使用VB代码。

右键单击您的报告->报表属性->编码并粘贴以下代码：

Public Function NDZ(Byval a As Decimal,Byval b As Decimal, Byval c As Decimal) As Decimal
'  Fix for divide by zero problem in VB
' calculates  a/b  and either returns result or c if b = 0 

if b = 0 then 
return c 
else 
return a/b 
end if 
end function 

现在，在您的报告中，在您要执行计算的文本框中输入以下表达式：

=code.NDZ(SUM(Fields!Consumption.Value), Fields!daysINDate.Value, 0) 

所以如果Fields！daysINDate.Value或SUM(Fields！Consumption.Value(为0，则它将返回0而不是错误。