Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

IIF Ignores Divide By Zero in Report Viewer 1

Status
Not open for further replies.

jontout

Technical User
Dec 29, 2006
95
GB
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.

Code:
=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
Code:
=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
 

Code:
=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:
Code:
IIF(Fields!VARCONV.Value = 0, 0, (your original IIF goes here))

Have fun.

---- Andy
 
Code:
=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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top