iff statement not working

barmanvarn · August 24th, 2005, 08:35 PM

I have the following statement:

=iif( Fields!Ticket_Budget.Value = 0,0, Fields!Ticket_Sales.Value/Fields!Ticket_Budget.Value)

For some reason, it keeps trying to divide by zero when the budget fields IS equal to zero.

Any suggestions?

bsullins · October 3rd, 2005, 12:12 PM

This is one of the major PITA's in Reporting Services...

Reporting Services evaluates every part of the if statement so you have to do a nested if that evaluates to 1 if the value of ur field is 0.

Like so...

=iif(Fields!Ticket_Budget.Value=0,0,iif(Fields!Tic ket_Budget.Value=0,1,Fields!Ticket_Sales.Value/Fields!Ticket_Budget.Value))

Try that...

--
Ben Sullins
Sql Developer - Our Vacation Store

harryhrn · January 23rd, 2009, 05:58 AM

example1:
=Switch( Fields!QtyTobeFilled.Value<= Fields!QuantityOnHand.Value,"Green",
Fields!QtyTobeFilled.Value>Fields!QuantityOnHand.V alue,"Blue")

example2:
iif(Fields!QtyTobeFilled.Value<= Fields!QuantityOnHand.Value,"Green",iif(Fields!Qty TobeFilled.Value>Fields!QuantityOnHand.Value,"Red" ))

Old Pedant · January 23rd, 2009, 04:25 PM

Quote:

Originally Posted by bsullins

Reporting Services evaluates every part of the if statement so you have to do a nested if that evaluates to 1 if the value of ur field is 0.

=iif(Fields!Ticket_Budget.Value=0,0,iif(Fields!Tic ket_Budget.Value=0,1,Fields!Ticket_Sales.Value/Fields!Ticket_Budget.Value))

Ummm...how does that fix the problem??? You *STILL* end up doing a divide by zero if *ALL* parts of *ALL* IIF's are evaluated.

I think you just made a typo. I think you meant to write:

Code:

=iif(Fields!Ticket_Budget.Value=0, 0,
     Fields!Ticket_Sales.Value / iif(Fields!Ticket_Budget.Value=0,1,Fields!Ticket_Budget.Value)
)

That way, if the value *IS* zero, you change it to 1 so no "divisiion by zero". But then the prior IIF ends up ignoring the division quotient, anyway, so the divide by 1 has no impact.

You *COULD* also do

Code:

= ( Fields!Ticket_Sales.Value / iif(Fields!Ticket_Budget.Value=0,1E100,Fields!Ticket_Budget.Value) )

So that the quotient becomes a really tiny number and, when displayed, will show as zero. Don't do this if you will use the result in any further calculation, but it should work for display-only purposes.