I have a custom SSRS Expression that is not working properly-CodePudding

I have a custom SSRS expression to calculate percentage:

=IIF(Sum(Fields!BidTotal.Value)=0,0,Sum(Fields!Bid_Margin.Value)/Sum(Fields!BidTotal.Value))

However when I go to preview the report I keep receiving the #Error message, due to diving by zero, but the IIF statement should resolve this. I'm using the same formula for other fields and it's working as intended, not sure why this expression would be any different.

CodePudding user response：

Unfortunately, SSRS evaluates both sides of the IIF regardless of the outcome of the initial expression.

You just need an extra IIF to make it complicated enough so that it doesn't find an issue.

=IIF(Sum(Fields!BidTotal.Value) = 0, 0, Sum(Fields!Bid_Margin.Value))  
 /
 IIF(Sum(Fields!BidTotal.Value) = 0, 1, Sum(Fields!BidTotal.Value))