#Error on calculation for dividing by 0

I am using the following expression but am getting strange results:
I am trying to avoid using the calulation when the qtyprocessed field is 0
to avoid the error. What the expression basically says is if qtyprocessed = 0 make the Calulated field = 0, else use the calculation as there is a realy
number <> 0. However I still recieve the #Error.
=IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, 0,
FormatNumber(((Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
Fields!qtyprocessed.Value) * 100, 2) & "%")
This does work below. When qtyprocessed = 0 i get true and false otherwise.
This function domonstrates the functionallity correctly and how I want it to
work.
=IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, "True", "False")
There is a problem when I throw the calculation in there. This is very
confusion as the calculated field should be set to 0 when qtyprocessed = 0
to avoid the error. The only explanation I can think of is that somehow the
calucation is being parsed or looked at regardless of the Iif.
TIA,
SteveIs it because your concatenating a string to a number? Shouldn't you
convert all results to a common value like string so it would be:
=IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, "0",
FormatNumber(((Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
Fields!qtyprocessed.Value) * 100, 2).ToString() & "%")
"Steve Wofford" <IntraRELY@.yahoo.com> wrote in message
news:%23d6WnMpMGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am using the following expression but am getting strange results:
> I am trying to avoid using the calulation when the qtyprocessed field is 0
> to avoid the error. What the expression basically says is if qtyprocessed
> = 0 make the Calulated field = 0, else use the calculation as there is a
> realy number <> 0. However I still recieve the #Error.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, 0,
> FormatNumber(((Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
> Fields!qtyprocessed.Value) * 100, 2) & "%")
> This does work below. When qtyprocessed = 0 i get true and false
> otherwise. This function domonstrates the functionallity correctly and how
> I want it to work.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, "True", "False")
> There is a problem when I throw the calculation in there. This is very
> confusion as the calculated field should be set to 0 when qtyprocessed = 0
> to avoid the error. The only explanation I can think of is that somehow
> the calucation is being parsed or looked at regardless of the Iif.
> TIA,
> Steve
>|||Additionally why not take out the formatting, and simply set the textbox
format to P2..Then your formula could be
=IIf(Fields!qtyprocessed.Value = 0, 0,
((Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
Fields!qtyprocessed.Value) * 100)
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Steve Wofford" wrote:
> I am using the following expression but am getting strange results:
> I am trying to avoid using the calulation when the qtyprocessed field is 0
> to avoid the error. What the expression basically says is if qtyprocessed => 0 make the Calulated field = 0, else use the calculation as there is a realy
> number <> 0. However I still recieve the #Error.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, 0,
> FormatNumber(((Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
> Fields!qtyprocessed.Value) * 100, 2) & "%")
> This does work below. When qtyprocessed = 0 i get true and false otherwise.
> This function domonstrates the functionallity correctly and how I want it to
> work.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, "True", "False")
> There is a problem when I throw the calculation in there. This is very
> confusion as the calculated field should be set to 0 when qtyprocessed = 0
> to avoid the error. The only explanation I can think of is that somehow the
> calucation is being parsed or looked at regardless of the Iif.
> TIA,
> Steve
>
>|||you can format the number after execute iff expression... too
try this...
=FormatNumber(IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, 0,
(Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
Fields!qtyprocessed.Value) * 100, 2) & "%"
"Steve Wofford" <IntraRELY@.yahoo.com> escreveu na mensagem
news:%23d6WnMpMGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am using the following expression but am getting strange results:
> I am trying to avoid using the calulation when the qtyprocessed field is 0
> to avoid the error. What the expression basically says is if qtyprocessed
> = 0 make the Calulated field = 0, else use the calculation as there is a
> realy number <> 0. However I still recieve the #Error.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, 0,
> FormatNumber(((Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
> Fields!qtyprocessed.Value) * 100, 2) & "%")
> This does work below. When qtyprocessed = 0 i get true and false
> otherwise. This function domonstrates the functionallity correctly and how
> I want it to work.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, "True", "False")
> There is a problem when I throw the calculation in there. This is very
> confusion as the calculated field should be set to 0 when qtyprocessed = 0
> to avoid the error. The only explanation I can think of is that somehow
> the calucation is being parsed or looked at regardless of the Iif.
> TIA,
> Steve
>|||Steve,
First, set the format property of the cell to P2 (Percentage with 2
decimal places). This eliminates the need for FormatNumber and the *
100 and the & "%".
Second, your expression will throw the #Error every time the
qtyprocessed value is zero because SSRS evaluates all the parts of the
expression. Makes no sense but it does.
So, to get around this, most people create a custom code function that
does the divide by zero check and calculations, then insert that into
the cell you formatted as P2.
I use:
Public Function DivideBy(ByVal exp1, ByVal exp2)
If exp2 = 0 Then
DivideBy = 0
Else
DivideBy = exp1 / exp2
End If
End Function
Once the function is created, insert this expression into your cell:
=code.DivideBy((Fields!qtyprocessed.Value -
Fields!qtyerror.Value),Fields!qtyprocessed.Value)
That should do you well.|||Thanks for all the input. After testing all of the repsonses the one that
worked was the one with the custom function. I still recieved an #Error on
all the other strings. I do appreciate all the input.
TIA,
Steve
"Steve Wofford" <IntraRELY@.yahoo.com> wrote in message
news:%23d6WnMpMGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am using the following expression but am getting strange results:
> I am trying to avoid using the calulation when the qtyprocessed field is 0
> to avoid the error. What the expression basically says is if qtyprocessed
> = 0 make the Calulated field = 0, else use the calculation as there is a
> realy number <> 0. However I still recieve the #Error.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, 0,
> FormatNumber(((Fields!qtyprocessed.Value - Fields!qtyerror.Value) /
> Fields!qtyprocessed.Value) * 100, 2) & "%")
> This does work below. When qtyprocessed = 0 i get true and false
> otherwise. This function domonstrates the functionallity correctly and how
> I want it to work.
> =IIf(FormatNumber(Fields!qtyprocessed.Value, 0) = 0, "True", "False")
> There is a problem when I throw the calculation in there. This is very
> confusion as the calculated field should be set to 0 when qtyprocessed = 0
> to avoid the error. The only explanation I can think of is that somehow
> the calucation is being parsed or looked at regardless of the Iif.
> TIA,
> Steve
>

#Error as field result Plese help.

I am getting #Error as the result of an expression and could use some help.
This doe not make sense to me since the the data is on the "false" side of an
if statement.
expression 1 is: = iif ( isdate(Fields!RDD.Value) and Fields!STD.Value >0 ,
"true", "false")
... then I edited the "true" part of the if statement
expression 2 is: = iif ( isdate(Fields!RDD.Value) and Fields!STD.Value >0 ,
format(dateadd("d", (0- Fields!STD.Value ), Fields!RDD.Value),
"MM/dd/yy"),"false")
My results:
num RDD STD Result Exp1 Result Exp2
-- -- -- -- --
1 02/01/05 9 true 01/23/05
2 03/05/05 9 true 02/24/05
3 9 false #Error
4 12/25/04 0 false false
5 0 false false
Why does #3 evaluate as "false" under expression 1 and not under exression 2
as do #4 and #5?You may be having a problem with the null value for RDD - if you ensure
that RDD has some value to replace a null value (like 'Fred', or
something else obviously not a date) you may get past this.|||Thanks for the response. I have tried several variations on that and still
get the error. It still baffles me as to why it is evaluated differently
between the expressions.
"Parker" wrote:
> You may be having a problem with the null value for RDD - if you ensure
> that RDD has some value to replace a null value (like 'Fred', or
> something else obviously not a date) you may get past this.
>|||Update on this error:
After much hair pulling, it appears that the error is generated in the
dateadd part of the expression.
expression 2 is: = iif ( isdate(Fields!RDD.Value) and Fields!STD.Value >0 ,
format(dateadd("d", (0- Fields!STD.Value ), Fields!RDD.Value),
"MM/dd/yy"),"false")
This generates the error, however if I replace the "(0 - Fields!STD.Value)"
with any positive number the expression works fine. In fact 0 also works,
but any negative number or any epression that evaluates to a negative number
gives the error. Any ideas?
"bugfish69" wrote:
> I am getting #Error as the result of an expression and could use some help.
> This doe not make sense to me since the the data is on the "false" side of an
> if statement.
> expression 1 is: = iif ( isdate(Fields!RDD.Value) and Fields!STD.Value >0 ,
> "true", "false")
> ... then I edited the "true" part of the if statement
> expression 2 is: = iif ( isdate(Fields!RDD.Value) and Fields!STD.Value >0 ,
> format(dateadd("d", (0- Fields!STD.Value ), Fields!RDD.Value),
> "MM/dd/yy"),"false")
> My results:
> num RDD STD Result Exp1 Result Exp2
> -- -- -- -- --
> 1 02/01/05 9 true 01/23/05
> 2 03/05/05 9 true 02/24/05
> 3 9 false #Error
> 4 12/25/04 0 false false
> 5 0 false false
> Why does #3 evaluate as "false" under expression 1 and not under exression 2
> as do #4 and #5?|||One thing to know about IIF is that all clauses are evaluated no matter
whether the condition is true. Lots of times you will want to do:
=F(IIF(Condition, A, B)) instead of =IIF(Condition, F(A), F(B))
Hope this helps.
"bugfish69" <bugfish69@.discussions.microsoft.com> wrote in message
news:5CC22EAC-E827-4EB1-87AF-B9BC2C32531F@.microsoft.com...
> Update on this error:
> After much hair pulling, it appears that the error is generated in the
> dateadd part of the expression.
> expression 2 is: = iif ( isdate(Fields!RDD.Value) and Fields!STD.Value >0
> ,
> format(dateadd("d", (0- Fields!STD.Value ), Fields!RDD.Value),
> "MM/dd/yy"),"false")
> This generates the error, however if I replace the "(0 -
> Fields!STD.Value)"
> with any positive number the expression works fine. In fact 0 also works,
> but any negative number or any epression that evaluates to a negative
> number
> gives the error. Any ideas?
>
>
> "bugfish69" wrote:
>> I am getting #Error as the result of an expression and could use some
>> help.
>> This doe not make sense to me since the the data is on the "false" side
>> of an
>> if statement.
>> expression 1 is: = iif ( isdate(Fields!RDD.Value) and Fields!STD.Value
>> >0 ,
>> "true", "false")
>> ... then I edited the "true" part of the if statement
>> expression 2 is: = iif ( isdate(Fields!RDD.Value) and Fields!STD.Value >0
>> ,
>> format(dateadd("d", (0- Fields!STD.Value ), Fields!RDD.Value),
>> "MM/dd/yy"),"false")
>> My results:
>> num RDD STD Result Exp1 Result Exp2
>> -- -- -- -- --
>> 1 02/01/05 9 true 01/23/05
>> 2 03/05/05 9 true 02/24/05
>> 3 9 false #Error
>> 4 12/25/04 0 false false
>> 5 0 false false
>> Why does #3 evaluate as "false" under expression 1 and not under
>> exression 2
>> as do #4 and #5?

#error

Can someone tell me why this expression generates a #ERROR when I view the
report? Here is my expression:
=FormatCurrency(iif(Fields!FinancialCodes.Value = 1,
SUM(Fields!FinancialAmount.Value),"Div_Group1"),0)
--
Thanks!I ran into a simliar issue. If you look at your output window you will see
warning messagse as to why its showing up. More than likely its something
like string was in the incorrect format, meaning the parser couldn't do
something with it.
Check to see if Finanicalamount.Value is a number before hand with an iif
statement.
"Candy" <Candy@.discussions.microsoft.com> wrote in message
news:A89F6883-422E-469A-B1F3-93A9A57ED6B6@.microsoft.com...
> Can someone tell me why this expression generates a #ERROR when I view the
> report? Here is my expression:
> =FormatCurrency(iif(Fields!FinancialCodes.Value = 1,
> SUM(Fields!FinancialAmount.Value),"Div_Group1"),0)
> --
> Thanks!