SSRS expression divide by zero in rdl

0
votes

How should I handle divide by zero error in rdl expression for SSRS 2017?

I have tried the below approaches but nothing works.

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)

  1. (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
reporting-servicesssrs-2012rdlcssrs-tablixrdl

2 Answers

0
votes

SSRS checks both sides of the iff statement... a if either the numerator or dinominator is 0, it will throw up an error.

One way to get around it is to use VB code.

Right click on your report -> report properties -> code and paste the following code :

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

Now on your report, enter the following expression in your text box you want to perform the calculation :

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

so if either Fields!daysINDate.Value or SUM(Fields!Consumption.Value) is 0, then it will return 0 and not an error.

0
votes

You should only be checking the denominator for zero - not the division operation.

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