question

wolfeste avatar image
wolfeste asked

Divide by zero error - SSRS

The below ssrs expression I randomly get a value returned as #Error. I believe it is caused from some values being NULL or 0. Anyone have a fix for this? I am fine with just hiding the value #error too. =(sum(iif(Fields!NoteDate.Value = Format(CDate(Parameters!StopDate.Value), "yyyy"),Fields!TotalVists.Value,nothing)) - sum(iif(Fields!NoteDate.Value = Format(CDate(Parameters!StopDate.Value), "yyyy")-1,Fields!TotalVists.Value,nothing)))/sum(iif(Fields!NoteDate.Value = Format(CDate(Parameters!StopDate.Value), "yyyy")-1,Fields!TotalVists.Value,nothing))
ssrs-2012
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

wolfeste avatar image wolfeste commented ·
I would be fine with setting the value to 0 instead of #error. I tried using IIF(IsNothing) but that doesn't seem to work.
0 Likes 0 ·

1 Answer

·
erlokeshsharma08 avatar image
erlokeshsharma08 answered
These are some steps you can follow:- 1. Remove the denominator part and check if you a value or an error. 2. If denominator is the cause than you need to add a condition and that is =iif( sum(denominator) > 0,sum(numerator)/sum(denominator), 0) Baicaly before calculating the expression, check whether the denominator is not equal to 0 or null, so that you don't get a resultant value like numerator/null or numerator/0 Thanks Lokesh
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.