CODEWITHSUNDEEP
reporting-servicesssrs-2008iif
Lalalala9999
Lalalala9999

Reputation: 149

IIF condition in SSRS and handling zeros

I've got a fairly complex formula in SSRS and for some reason it shows an error when it encounters a zero value, even though I'm handling zeros in my formula. Can anybody advise how to fix this issue?

=iif(datepart("h",Now())=6, iif(SUM(Fields!PYRevenue.Value) = 0, 0,((SUM(Fields!Revenue.Value) - SUM(Fields!PYRevenue.Value)) / SUM(Fields!PYRevenue.Value))) ,iif(SUM(Fields!PYRevenue.Value) = 0, 0,(SUM(Fields!Revenue.Value) / SUM(Fields!PYRevenue.Value))))

Upvotes: 0

Views: 1137

Answers (2)

Alan Schofield
Alan Schofield

Reputation: 21703

Apologies for the hurried answer... Doing this on my phone..

Even though your last expression will not be used if SUM(PYRevenue) is zero, it will still get evaluated and give a divide by zero error. 2 ways to resolve. I personally use SWITCH instead of IIF for anything that is not simple.

Alternatively you need to check SUM(PYRevenue) on both side of your division. Pseudo code would be =IIF(SUM(PYRevenue)=0,0,SUM(Revenue)) / IIF(SUM(PYRevenue)=0, 1, SUM(PYRevenue)) So if sum PY is zero then do 0/1 else do revenue/pyrevenue

Upvotes: 0

Chris Hackett
Chris Hackett

Reputation: 449

Sorry, I misread your formula until I indented it properly. Even though you have a check for PYRevenue = 0, you need to also include another IIF() after both occurrences of "/", like so:

iif( SUM(Fields!PYRevenue.Value) = 0, 0, ( (SUM(Fields!Revenue.Value) - SUM(Fields!PYRevenue.Value) ) / IIF(SUM(Fields!PYRevenue.Value) = 0, 1, SUM(Fields!PYRevenue.Value)) )

SSRS evaluates all the expressions so you have to handle the 0 value even if your parent logic prevents that block from being hit.

Upvotes: 0

Related Questions