#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
>