July 21, 2008 at 12:19 pm
I have two columns "Fields!Sales_YAG.Value" n "Fields!Sales_Cur.Value"
I need the third column as
(Fields!Sales_Cur.Value - Fields!Sales_YAG.Value)/Fields!Sales_YAG.Value
but when the divider is zero its turning out as #Error ...I tried this code
=IIF((Fields!Sales_YAG.Value)<> 0,
Round((((Fields!Sales_Cur.Value)-(Fields!Sales_YAG.Value))/
(Fields!Sales_YAG.Value)),2), "0.00")
nut not working any suggestions...also i want to take the positive value of subtraction
(Fields!Sales_Cur.Value)-(Fields!Sales_YAG.Value)
how these problems can be solved...please help
July 21, 2008 at 12:48 pm
The code you have posted looks like it should work to avoid the divide by 0 error. Are you sure you are always getting a value in the divisor (Fields!Sales_YAG.Value)? If you have a NULL in there you may get the error as well so you would want to check the Fields!Sales_YAG.Value Is Not Nothing as well as <> 0.
In order to get the non-negative or absolute value of the subtraction just use the ABS() function,
ABS((Fields!Sales_Cur.Value)-(Fields!Sales_YAG.Value))
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 21, 2008 at 1:19 pm
Thanks for the "ABS" function...and yes the divider is not always zero ...but when its zero this code i have wrote is not working...can you please suggest the corrections to be made in the code.
thnks a bunch in advance
July 21, 2008 at 1:52 pm
Just so you know why the IIF doesn't work: IIF is a function. SSRS reads the entire statement kind of from the inside out. It sees the division operation before it sees the IIF conditions. If the divider is zero, it still sees the zero even though you've conditioned it away.
Two ways to deal with the divide by zero. My preference is #1.
1. Use Custom Code instead of IIF. In the Code tab/window of Report Properties, enter the following:
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 = 0 Then
DivideBy = 0
Else : DivideBy = Exp1 / Exp2
End If
End Function
Then use =code.DivideBy(value1,value2)
instead of =IIF(value2 = 0, 0, value1/value2)
OR
2. Use nested IIF to correct divide by zero errors. (From Brian Welker's weblog)
Info: People often ask how to avoid divide by zero problems in their Reporting Services reports. Let's say you have a textbox that calculates profit margin via the expression:
=Fields!Price.Value / Fields!Cost.Value
This works fine unless a value in the Cost field is zero (and is not an integer or double), in which case you will get '#error' displayed in your report. This is because a divide by zero with a decimal field will throw an exception. This exception is caught by the report processing engine and #error is displayed. If you would rather have a string like "N/A" instead of #error, you might think about creating a Reporting Services expression using the IIf function:
=IIf(Fields!Cost.Value = 0, "N/A", Fields!Price.Value / Fields!Cost.Value)
But when you preview the report, you still see #error in your report. What gives?
If you take a look at the IIf function description in the Visual Basic documentation, you will see the following:
As part of preparing the argument list for the call to IIf, the Visual Basic compiler calls every function in every expression. This means that you cannot rely on a particular function not being called if the other argument is selected by Expression.
This means that even through the value for cost is zero, the error will still be generated. So how do you work around this? You have to force the expression evaluation to avoid the division with a nested IIf:
=IIf(Fields!Cost.Value = 0, "N/A", Fields!Price.Value / IIf(Fields!Cost.Value = 0, 1, Fields!Cost.Value))
The nested IIf is evaluated first so that the divide by zero can be avoided but is not used by the outer expression if it is zero.
posted at: http://blogs.msdn.com/bwelcker/archive/2006/09/26/772650.aspx
HTH
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
July 22, 2008 at 7:08 am
toolman (7/21/2008)
Just so you know why the IIF doesn't work: IIF is a function. SSRS reads the entire statement kind of from the inside out. It sees the division operation before it sees the IIF conditions. If the divider is zero, it still sees the zero even though you've conditioned it away.Two ways to deal with the divide by zero. My preference is #1.
1. Use Custom Code instead of IIF. In the Code tab/window of Report Properties, enter the following:
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 = 0 Then
DivideBy = 0
Else : DivideBy = Exp1 / Exp2
End If
End Function
Then use =code.DivideBy(value1,value2)
instead of =IIF(value2 = 0, 0, value1/value2)
Yeah,
I agree with toolman's first point. Its best way to avoid DIVIDE BY ZERO #error
July 22, 2008 at 7:38 am
The toolman has that right. You could even compile that into a DLL, in which you would have other commonly used custom functions in SSRS, and reference the DLL in all your reports and then you don't have to re-type (or cut and paste) the code.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply