February 25, 2009 at 7:59 am
I've got a textfield for gross margin percent, and I have the value set like this:
=IIF(Sum(Fields!sales.Value) <> 0,(Sum(Fields!sales.Value)-Sum(Fields!cost.Value))/Sum(Fields!sales.Value),0)
The intention is to use the value 0 if the sum of sales is 0, to avoid dividing by 0 errors.
I'm still getting #Error as a result, and the error "[rsRuntimeErrorInExpression] The Value expression for the textbox ‘textbox4’ contains an error: Attempted to divide by zero."
February 25, 2009 at 8:03 am
the problem is that everything gets evaluated before the iff is executed... hence the error.
You need to create a function in the code pane with 3 parameters (numerator, denominator, valueifzero).
Then in the code do the iff. That'll work 100%.
February 25, 2009 at 8:14 am
Ninja's_RGR'us (2/25/2009)
the problem is that everything gets evaluated before the iff is executed... hence the error.You need to create a function in the code pane with 3 parameters (numerator, denominator, valueifzero).
Then in the code do the iff. That'll work 100%.
I'm not sure what the code pane is.. ?
There's the expression editor.
Then there's the XML code for the rdl report definition file.
February 25, 2009 at 8:48 am
OK.. I found this article here:
http://www.sqlservercentral.com/scripts/Data+Warehouse/31510/
So he's got a function called CalcRatio that is put into Report --> Properties --> code tab.
However, I must be calling it wrong, because I'm getting the following error:
"The Value expresssion for the textbox 'textbox4' contains an error: [BC30451] Name 'CalcRatio' is not declared."
Suggestions are welcome!
February 25, 2009 at 8:50 am
I don't have RS in front of me but I can guide you...
open up the report in design view.
Then right click somewhere in or under the report where there's no control. If you see the parameters... option you are in the correct menu for the code section.
Then this is straight forward vb code (hit F1 if you need help on creating a function with parameters).
February 25, 2009 at 8:52 am
You need to put a prefix in front of the function (sorry I don't remember it).
It's like Code.FunctionName (parameters, go, here).
Again F1 with keyword search function should lead you to the exact syntax.
February 25, 2009 at 8:58 am
Yes, the "Code." prefix did the trick, as for calling the function that was in the code tab in report properties.
Ninja, you might want to open your window. I'm going to throw a bottle of Schlafly beer, as hard as I can, in your general direction!
Thanks!
February 25, 2009 at 9:10 am
You better drink it then, I'm not into alcool all that much.
Thanks for the thoughts. 😛
February 25, 2009 at 9:12 am
No worries.. you can cook with it as well!
March 4, 2009 at 2:27 am
You can guess error from the error message..
You cannot Divide by Zero.So make a checking for Sum(Fields!sales.Value) .This value should not be 0
March 9, 2009 at 5:36 pm
tung (2/25/2009)
I've got a textfield for gross margin percent, and I have the value set like this:=IIF(Sum(Fields!sales.Value) <> 0,(Sum(Fields!sales.Value)-Sum(Fields!cost.Value))/Sum(Fields!sales.Value),0)
The intention is to use the value 0 if the sum of sales is 0, to avoid dividing by 0 errors.
...
If you don't like the VB code solution described above (some sites don't like code in reports) you can also use something like:
=IIF(Sum(Fields!sales.Value) <> 0,(Sum(Fields!sales.Value)-Sum(Fields!cost.Value))/(IIF(Sum(Fields!sales.Value) <> 0,Sum(Fields!sales.Value),1),0)
This protects you from the divide by zero error by dividing by one instead, then hiding the result with the outer IIf.
However, it is a bit more convoluted - it could be hard to read and easy to introduce bugs.
March 9, 2009 at 5:47 pm
If you use a SWITCH checking for a zero divisor, rather than the IIF, you don't have the problem caused by evaluating the entire expression.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply