Divide By Zero in Impromptu

[badhri]

Greetings,

Can anyone suggestt me on how to handle the Divide By Zero Exception in an Impromptu Report. I have couple of calcualetd measures Margin % and Discount % that can raise a Divide by Zero Exception.

Kindly help me with your thoughts on this.

Thanks in Advance,

Badhri ...

 

[griffindm]

Hi Badhri,

There used to be a good thread on this I could point you to, but the search engine doesn't want to find it.

If the 'normal' If-Then-Else logic isn't cutting it for you, try this variation:

numerator / If(denomenator <> 0) then (denomenator) else NULL ...

This works well, especially on Oracle, where Impromptu's 'optimistic' logic gets it into trouble. If this still doesn't work, and you are on Oracle, turn off the automatic Decode function, which is a pain in the butt anyway.

The underlying problem with the logic usually used (i.e. If (denomenator <> 0) then (numerator/denomenator) else (0)...) is that Impromptu tries to send the division to the database before determining the result of the If-Then-Else.

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[benbotektips]

I can't swear to it, but I'm pretty sure I avoided Cognos's 'optimistic' divide-by-zero problem, by putting the zero test before the division.
ex. If (denominator-field = 0) then 0 else (numerator / denominator).

 

[iguanadon]

benbotektips, you're right that putting the zero test before the division should/does work occassionally, but we found that in 6.0 and higher, doing it the way griffindm stated is more reliable. It had something to do with a list footers problem. We found this out the hard way... staring at the data definition and saying THIS SHOULD WORK, IT SURE LOOKS RIGHT?!?!?!

 

[benbotektips]

Well, not to quibble, but our reports here are pretty vanilla, no list headers or footers, and we're running 6.0, rolling out 7.1. So, I guess we just got lucky, having unfancy list reports, with heavy SQL and grouping, but no headers & footers. For any new division fields, I'll try Griff's idea.
P.S. We have Oracle and SQL-Server. Coincidentally, the division reports are off Oracle.