IIF Ignores Divide By Zero in Report Viewer 1

[jontout]

Hi all,
I'm having a rare old time with a feature of Report Viewer, passing details from a SQL query via Oracle - these don't appear to be an issue as not other parts of my report are failing, however this line in a report viewer cell is causing no end of grief.

=IIF(Fields!LIVE_NET_RETAINED.Value/Fields!VARCONV.Value <= 0,0,Fields!LIVE_NET_RETAINED.Value/Fields!VARCONV.Value)

VARCONV is a total of 3 other fields and in one instance, which is prone to happen again, has returned 0 - causing the division against LIVE_NET_RETAINED to cause a divide by error, which should be fixed by the IIF statement, but isn't.

The full query is

=IIF(SUM(Fields!LIVE_NET_RETAINED.Value)/((SUM(Fields!NTU_NET_RETAINED.Value)
+SUM(Fields!QUOTE_NET_RETAINED.Value)+SUM(Fields!LIVE_NET_RETAINED.Value)))=0,0,
SUM(Fields!LIVE_NET_RETAINED.Value)/(SUM(Fields!NTU_NET_RETAINED.Value)
+SUM(Fields!QUOTE_NET_RETAINED.Value)+SUM(Fields!LIVE_NET_RETAINED.Value)))

and is used to work out the conversion commission against a quoted & sold product.

Thanks in advance.

Cheers,

Jon


The full query is

 

[Andrzejek]

=IIF([red]Fields!LIVE_NET_RETAINED.Value/Fields!VARCONV.Value[/red] <= 0,0,Fields!LIVE_NET_RETAINED.Value/Fields!VARCONV.Value)

If your [tt]Fields!VARCONV.Value[/tt] = 0, your first division by 0 fails, because you divide by 0 and it will never be equal to anything.

You may want to wrap your IIF in another IIF, something like:

IIF(Fields!VARCONV.Value = 0, 0, (your original IIF goes here))

Have fun.

---- Andy

 

[jontout]

=IIF(SUM(Fields!LIVE_NET_RETAINED.Value) <> 0, sum(Fields!LIVE_NET_RETAINED.Value) / 
IIF(SUM(Fields!LIVE_NET_RETAINED.Value) <> 0, (SUM(Fields!VARCONV.Value)), 42), 0)code]

Thanks Andy - I'd played with a nested IIF about an hour before, based on something else I'd found and dropped, I've come up with the above which seems to work, although we don't exactly know why!

Cheers,

Jon