Dividing by Zero formula Error, Cannot Resolve 2

[awaria]

Getting a division by zero error, cannot resolve formula;
Have formula where attempting to divide by field with null value.

Field A / Field B (where B is null)

Use ISNOTHING to replace null with zero in Field B
Here is the formula:

=iif(isnothing(sum(fieldB)) = “True”, 0,iif(sum(fieldB = 0, 0, Sum(fieldA)/Sum(FieldB)))

I am expecting the result to be zero with the ISNOTHING = “True” holding value and making the result
zero. However, result is error from dividing by zero.

When I replace the Field references with values, the formula works. Tested by setting ISNOTHING to True and False.

Can someone explain the problem with the formula.

Thanks,

awaria

 

[xlbo]

the problem is that SSRS calculates ALL parts of a formula but only presents those where the criteria match

Therefore if any part of the formula can error, it returns an error

Sum(fieldA)/Sum(iif(isnothing(FieldB,0,FieldB))

you need to convert the NULL to 0 on a row by row basis - SSRS calculates in running datasets so

1st convert any values that may cause errors
2nd aggregate up the converted dataset

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[awaria]

Geoff,

Can you apply the syntax and logic to my example.

I am having difficulty working this out. Cannot get past
the issue with any of my re-works on the formula.

Appreciate your help.

awaria

 

[SQLScholar]

awaria,

I may be wrong but your brackets look wrong as well.

=iif(isnothing(sum(fieldB)) = "True", 0,iif(sum(fieldB = 0, 0, Sum(fieldA)/Sum(FieldB)))

I am not sure where the highlighted bracket ends.

So (with guessing with the brackets

=iif(isnothing(sum(fieldB)) = "True", 0,iif(sum(fieldB) = 0, 0, IIF(isnothing(sum(fieldB)) = "FALSE" AND sum(fieldB) <> 0, Sum(fieldA)/Sum(FieldB),0)))

Something like this would fix it.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[awaria]

Dan,

I am still getting a div by 0 warning. The report does however run very quickly, just can't resolve the div by 0 issue.

Here's my current formula, can you take look and make any recommendations.

Thanks again for your input.

=iif(isnothing(sum(fieldB)) = "True", 0,
iif(sum(fieldB) = 0, 0,
IIF(sum(fieldB) = "FALSE" AND Sum(FieldB) <> 0,
Sum(fieldA/Sum(FieldB),0)))

Thanks,

awaria

 

[SQLScholar]

How about this?

=iif(isnothing(sum(fieldB)) = "True", 0,
iif(sum(fieldB) = 0, 0,
IIF(isnothing(sum(fieldB)) = "FALSE" AND Sum(FieldB) <> 0,
Sum(fieldA/Sum(FieldB),0)))



----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[awaria]

Many thanks, working great.

I have an unrealted follow up question for you.

Can you reference textbox names in formulas in other textboxes?

Example, variances. In a new textbox10, be able in your expression to use textbox 5 / textbox 7, instead of copy the lengthy expressions out of each textbox to use in the new one?

Thanks again for your help.

awaria

 

[SQLScholar]

Hmmm..... Not that i am aware of.

No problem.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[awaria]

Thanks for help.

 

[xlbo]

a simpler formula would be:

=iif(sum(iif(isnothing(FieldB,0,FieldB)))=0,0,Sum(fieldA)/Sum(iif(isnothing(FieldB,0,FieldB))))

all your tests for isnothing can be done "inline" - they do not nee to be done seperately

In terms of referencing a textbox, yes you can - you can use the ReportItems collection. This should really only be used for referencing textboxes etc outside of teh current table scope however as otherwise the logic may not work out

If you want to break functions down into multiple simpler parts or reference pre-calculated values, you should have a look at generating calculated fields in the dataset area

for instance, you could set up a calculated field called "calcFieldB" which would be =iif(isnothing(FieldB),0,FieldB)

you should ten be able to use that in further calculations by referencing calcFieldB ratehr than just FieldB




Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question