divide by 0 or null SSRS

Question

Post reply

divide by 0 or null SSRS

jvanderlouw-960980

SSC Eights!

Points: 827
More actions
June 4, 2010 at 6:29 am

#221858

I searched the forum for the following issue. I want to calculate for two particular groups a percentage in a matrix. However in the case of 0 or null for the denominator the value in SSRS is displayed as #error. My goal is to show the #error as a 0%. The sum of value G for group 2 equals zero
--> Sqlserver 2005
--> Visual studio 2005
What is my formula like:
Field uren (numerator) means number of hours produced = number(17,2)
Field G (denominator) means number of hours potentially possible = integer
situation 1:
=sum(Fields!uren.value) / sum(Fields!G.Value) returns
Group 1: 0.80%
Group 2: #error
situation 2:
=iif(isnothing(Sum(Fields!G.Value)) or sum(Fields!G.Value)=0,0,sum(Fields!uren.Value)/sum(Fields!G.Value)) returns
Group 1: 0,80%
Group 2: #error
Situation 3 (I replace the denominator by a constant):
=iif(isnothing(Sum(Fields!G.Value)) or sum(Fields!G.Value)=0,0,sum(Fields!uren.Value)/1.5555555) returns
Group 1: 0,51%
Group 2: 0% --> this is what I would like to see
However, as soon as I replace the constant (here 1.5555555) by sum(Fields!G.Value) the #error is displayed
I cannot figure out what the issue is. I hope someone can give me a hint. Thanks a lot.
Joost

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You could change the query in the dataset to retrieve the denominator as:

ISNULL(NULLIF(SUM(someField),0),1)

-- Gianluca Sartori

Bill-89778 Ten Centuries Points: 1201 More actions · Answer 2

This is something I found some time ago...

function SafeDivide(a, b, c)

if b = 0 then

return c

else

return a/b

end if

end function

HTH

jvanderlouw-960980 SSC Eights! Points: 827 More actions · Answer 3

Actually, I did that already:

select .....

isnull(sum((grens_correctie_C/7) * dagen_D),0) as G

from ......

(so indeed the 'isnothing' part is a bit obsolete)

jvanderlouw-960980 SSC Eights! Points: 827 More actions · Answer 4

I would like to avoid the use of a function or is that too naive?

checkai Hall of Fame Points: 3041 More actions · Answer 5

From my understanding, you'll need a function to make sure this is accurate at all times. I have code embedded in every report (in my template).

Public Function DivideBy(ByVal Exp1, ByVal Exp2)

If Exp2 = 0 Then

DivideBy = 0

Else: DivideBy = Exp1/Exp2

End If

End Function

Then my expressions use =code.divideBy(field1.value, field2.value)

Has become second nature and is very effective.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 6

Using a function as checkai suggests is pretty easy. You can embed it into your report. Go to Report >> Report Properties >> and select the Code tab, and paste your function in there and away you go.

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 7

dbowlin (6/9/2010)
Using a function as checkai suggests is pretty easy.

Yeah... and using code function is the BEST option for this problem.

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 8

checkai (6/9/2010)
From my understanding, you'll need a function to make sure this is accurate at all times. I have code embedded in every report (in my template).
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 = 0 Then
DivideBy = 0
Else: DivideBy = Exp1/Exp2
End If
End Function
Then my expressions use =code.divideBy(field1.value, field2.value)
Has become second nature and is very effective.

I hope it's not as slow as a T-SQL scalar function. Does anyone have any performance test comparisons between doing such a thing directly and using such a function?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Shehab.Saad SSC Enthusiast Points: 112 More actions · Answer 9

you can use iff condition like this for example you want to devide the revenue / nights.

and you can assume that may be nights is = 0

So you can use this check condition,,,

if nights is =0 so value =0 or other wise divide on nights

iif( Sum(val(Fields!Nights.Value))=0,0, Sum(val(Fields!Revenue.Value))/Sum(val(Fields!Nights.Value)))

checkai Hall of Fame Points: 3041 More actions · Answer 10

I don't agree with the checking for 0. I've seen that regardless of that check, the report will analyze all values and still throw an error if you even check for divide by zero.

tchovanec 65734 SSCommitted Points: 1740 More actions · Answer 11

I had this same problem last week and used this function. It works great.

Function Divide(Numerator as Double, Denominator as Double)

If Denominator = 0 Then

Return 0

Else

Return Numerator/Denominator

End If

End Function

Then this is the expression I used to get the percentage.

=iif(Sum(Fields!fee_schedule_amount.Value) = 0 or Sum(Fields!NetworkPrice.Value) > Sum(Fields!fee_schedule_amount.Value), 0, Code.Divide(SUM(Fields!FEE_SCHEDULE_SAVINGS.Value),SUM(Fields!fee_schedule_amount.Value)))

jvanderlouw-960980 SSC Eights! Points: 827 More actions · Answer 12

Thank you all for your help. I'll go for the function:-). Indeed the check does not appear to work all the time.