Avoiding Divide by Zero in Report

What is the experession to evaluate if the result of a computation would be a divide by zero error for a text box in report?

IIF(divide by zero, display nothing, else display computed result)...?

you should actually use it in your query,but...

=iif(value1 = 0 or value2=0,0, value1/value2)

where the values are the values your dividing by.

it's much easier to do it in sql though...

|||I'd recommend either doing it in your Stored Procedure or creating a function to handle it. You don't want to be putting that expression in every sell in which you're doing division.

You can create a function like the one below and place it in the Report code block or put it in an assembly and reference the assembly in the report.

Public Function CalcAvg(dblNum As Double, dblDen As Double) as Object

if (dblDen = nothing) or (dblDen = 0)
CalcAvg = 0
else
CalcAvg = dblNum / dblDen
end if

End Function|||

I have this code in my Code section

Code Snippet

Public Shared Function DivideXbyY(ByVal X As Decimal, ByVal Y As Decimal) As Decimal
DivideXbyY = IIF(Y=0, 0, X / IIF(Y=0, 1, Y))
End Function

then I call it in the Table/Matrix

Code Snippet

=Code.DivideXbyY(Fields!amt.Value,Fields!tix.Value)

|||Thanks guys, your responses are very helpful and much appreciated.

Avoid Drill trough for zero value

Hi,
I am using Drillthrough in table Cell in one of my report.
Table cell displays sum which is sometimes Zero. And when sum is zero, I
don't want Drillthrough featur at that point, so that I can avoid unnecessary
click.
I tried using expression =IIF( sum =0, "", ReportName)
but this then take me report server and displays all the avaialble report on
RS, which is big security hole.
Please let me know the solution.
Many Thanks,
MaheshHi,
I am using Drillthrough in table Cell in one of my report.
Table cell displays sum which is sometimes Zero. And when sum is zero, I
don't want Drillthrough featur at that point, so that I can avoid unnecessary
click.
I tried using expression =IIF( sum =0, "", ReportName)
but this then take me report server and displays all the avaialble report on
RS, which is big security hole.
Please let me know the solution.
Many Thanks,
Mahesh|||Instead of "", use the keyword Nothing: =iif( Sum(...) = 0, Nothing,
ReportName)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mahesh Gaware" <MaheshGaware@.discussions.microsoft.com> wrote in message
news:B92CEC44-8A06-47A2-930D-E0463834DB32@.microsoft.com...
> Hi,
> I am using Drillthrough in table Cell in one of my report.
> Table cell displays sum which is sometimes Zero. And when sum is zero, I
> don't want Drillthrough featur at that point, so that I can avoid
> unnecessary
> click.
> I tried using expression =IIF( sum =0, "", ReportName)
> but this then take me report server and displays all the avaialble report
> on
> RS, which is big security hole.
> Please let me know the solution.
> Many Thanks,
> Mahesh
>

AVG() aggregate with CASE

i have 2 columns of type decimal that are divided and rounded and
return zero if the divisor is zero. it all works fine until i try to
AVG() them. now it seems my CASE to avoid dividing by zero is no
longer working...or i'm just approaching it all the wrong way. my
brain is a bit fried from staying up late to meet a deadline and i was
wondering if someone could give some advice.
SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND((b.Value1 / b.Value2 *
1000) * 100, 2, 1) END AS Rating FROM a INNER JOIN b ON a.ID = b.ID
b.Value1 = 101664
a.Value2 = 7730
Rating = 1.31
i now try putting the AVG() function to work like this:
SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE AVG( ROUND((b.Value1 / b.Value2
* 1000) * 100, 2, 1) ) END AS Rating FROM a INNER JOIN b ON a.ID =
b.ID GROUP BY a.Value2
if i have 5 rows of results .96, .97, .98, 1.13, 1.31, then it works i
get one record with the Rating = 1.07. yay!
the problem comes in where sometimes, the Values are 0. before using
the AVG() function, the CASE took care of these columns so that my
Rating results would be 0. but now i keep getting a "Divide by zero
error encountered". if the CASE is working, it shouldn't even try the
AVG() calculation right? or am i approaching this the wrong way? and
no, i can't use the SET ANSI_WARNINGS OFF because we're not using
stored procedures in this case.
thanks for any input or insight you may provide!
susieWhy not avoid the 0 problem by putting in a WHERE clause? ie
WHERE value1 > 0
AND value2 > 0
If that's not going to work, post some more sample values for tables a and b
.
Thanks
Damien
"TuBuGuRL" wrote:

> i have 2 columns of type decimal that are divided and rounded and
> return zero if the divisor is zero. it all works fine until i try to
> AVG() them. now it seems my CASE to avoid dividing by zero is no
> longer working...or i'm just approaching it all the wrong way. my
> brain is a bit fried from staying up late to meet a deadline and i was
> wondering if someone could give some advice.
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND((b.Value1 / b.Value2 *
> 1000) * 100, 2, 1) END AS Rating FROM a INNER JOIN b ON a.ID = b.ID
> b.Value1 = 101664
> a.Value2 = 7730
> Rating = 1.31
> i now try putting the AVG() function to work like this:
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE AVG( ROUND((b.Value1 / b.Value2
> * 1000) * 100, 2, 1) ) END AS Rating FROM a INNER JOIN b ON a.ID =
> b.ID GROUP BY a.Value2
> if i have 5 rows of results .96, .97, .98, 1.13, 1.31, then it works i
> get one record with the Rating = 1.07. yay!
> the problem comes in where sometimes, the Values are 0. before using
> the AVG() function, the CASE took care of these columns so that my
> Rating results would be 0. but now i keep getting a "Divide by zero
> error encountered". if the CASE is working, it shouldn't even try the
> AVG() calculation right? or am i approaching this the wrong way? and
> no, i can't use the SET ANSI_WARNINGS OFF because we're not using
> stored procedures in this case.
> thanks for any input or insight you may provide!
> susie
>|||hi damien,
thanks for the reply. the WHERE clause is a good idea, but i don't
want to exclude these values, i need them to show up as averaged 0 in
my report.
i just realized that in my post above it's supposed to say "... ROUND(
(b.Value1 / a.Value2) ) ..." NOT "... ROUND( (b.Value1 / b.Value2) )
..."
anyway more sample data:
a.Value2 = 5910
b.Value1 = 120779
Rating = 2.04
5 different Ratings: 2.04, 1.01, 0.98, 0.91, 0.41
Avg() rating = 1.07
data used when i receive the error:
a.Value2 = 0
b.Value2 = 0
Rating = .0000000000000000
5 different Ratings: all .0000000000000000
shouldn't the CASE i use in the SELECT avoid even trying to AVG() the 0
Ratings?
thanks,
susie|||Put the CASE inside the AVG
...
AVG(CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND(...) END)
...
TuBuGuRL wrote:
> i have 2 columns of type decimal that are divided and rounded and
> return zero if the divisor is zero. it all works fine until i try to
> AVG() them. now it seems my CASE to avoid dividing by zero is no
> longer working...or i'm just approaching it all the wrong way. my
> brain is a bit fried from staying up late to meet a deadline and i was
> wondering if someone could give some advice.
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND((b.Value1 / b.Value2 *
> 1000) * 100, 2, 1) END AS Rating FROM a INNER JOIN b ON a.ID = b.ID
> b.Value1 = 101664
> a.Value2 = 7730
> Rating = 1.31
> i now try putting the AVG() function to work like this:
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE AVG( ROUND((b.Value1 / b.Value2
> * 1000) * 100, 2, 1) ) END AS Rating FROM a INNER JOIN b ON a.ID =
> b.ID GROUP BY a.Value2
> if i have 5 rows of results .96, .97, .98, 1.13, 1.31, then it works i
> get one record with the Rating = 1.07. yay!
> the problem comes in where sometimes, the Values are 0. before using
> the AVG() function, the CASE took care of these columns so that my
> Rating results would be 0. but now i keep getting a "Divide by zero
> error encountered". if the CASE is working, it shouldn't even try the
> AVG() calculation right? or am i approaching this the wrong way? and
> no, i can't use the SET ANSI_WARNINGS OFF because we're not using
> stored procedures in this case.
> thanks for any input or insight you may provide!
> susie
>