asked on

SSRS Expressio #Error (Division bu 0?)

This is my expression
=iIf(not(isnothing(fields!MonthSales1_LY.value)) and not(isnothing(fields!MonthSales1_LY.value)),fields!MonthSales1.value/fields!MonthSales1_LY.value - 1,"-")

When fields!MonthSales1.value/fields!MonthSales1_LY.value is a null value and fields!MonthSales1.value is a positive value I get #Error as my result. I asume it means division by 0 or unknown value.

But if I remove the arithmetic calculation and substitute "100"
=iIf(not(isnothing(fields!MonthSales1_LY.value)) and not(isnothing(fields!MonthSales1_LY.value)),"100","-")

I get "100" when both values exist and "-" when either does not. Why is it trying to divide by the null value in the first expression?

dqmq

1. You have a typo: using MonthSales1_LY in both isnothing() functions
2. Probably should use isnull() instead of isnothing()
3. The True side of your IFF returns a number, the False side of your IIF returns a string. There may be some implicit conversion saving your butt, but it's a bad practice

sbdt8631

ASKER

The typo was just the result of frustrated changes. I get the same results with the typo corrected.

When I try isnull() in the expression, it marks it as an error called "unrecognized identifier." I used isnothing after a google search for "ssrs is null" suggested isnothing. I am open for alternatives.

dqmq

my error, I got confused with ISNULL() from another language.

I know this is not correct, but does it avoid the error:

=iIf(not(isnothing(fields!MonthSales1.value)) and not(isnothing(fields!MonthSales1_LY.value)),fields!MonthSales1.value/fields!MonthSales1_LY.value - 1,0)

Nico Bontenbal

What version of SSRS are you using? I tried to reproduce your problem in 2008 R2 and it worked fine with this expression:
=iIf(not(isnothing(fields!MonthSales1_LY.value)) and not(isnothing(fields!MonthSales1.value)),fields!MonthSales1.value/fields!MonthSales1_LY.value - 1,"-")
This gave me this result:

But you might find the solution for your problem here:
http://www.sqlservercentral.com/Forums/Topic442497-150-1.aspx

Alpesh Patel

=IIF(Fields!Order.Value >0, Fields!Order.Value, Join("-" , CStr(Fields!Order.Value))

Nico Bontenbal

@PatelAlpesh, I get an error when I use that expression, did you test it?

sbdt8631

ASKER

Everyone, I attached a pdf of the output. January is the only month that applies.

Nicobo
Based on your link above I tried
=iIf(not(isnothing(fields!MonthSales1.value)),fields!MonthSales1.value /
IIF(not(isnothing(fields!MonthSales1_LY.value)),fields!MonthSales1_LY.value - 1, "-"), "-")
and
=iIf(not(isnothing(fields!MonthSales1.value)),fields!MonthSales1.value /
IIF(isnothing(fields!MonthSales1_LY.value), "-",fields!MonthSales1_LY.value - 1), "-")
I still get #Error if no LY sales

PatelAlpesh
I am afraid I don't understand what you are suggesting.

I tried updating my Visual Studio 2005 to service pack 1 in case this was just a bug that might have been fixed, but that did not help.

dqmq
=iIf(not(isnothing(fields!MonthSales1.value)) and not(isnothing(fields!MonthSales1_LY.value)),fields!MonthSales1.value/fields!MonthSales1_LY.value - 1,0)
This was pretty much my original syntax, but I tried copy/paste and still get the #Error.
Sales-by-KAM.pdf

sbdt8631

ASKER

I just viewed the file I attached and it did not export very well. Page two demonstrates the problem.

Nico Bontenbal

Try:

=iIf(not(isnothing(fields!MonthSales1.value)) and not(isnothing(fields!MonthSales1_LY.value)),fields!MonthSales1.value / 
IIF(not(isnothing(fields!MonthSales1_LY.value)),fields!MonthSales1_LY.value - 1, 1), "-")

Open in new window

You had
IIF(not(isnothing(fields!MonthSales1_LY.value)),fields!MonthSales1_LY.value - 1, "-")
Which would try to divide by "-" when MonthSales1_LY is null. That would cause an error.
You are using SSRS 2005 right?

ASKER CERTIFIED SOLUTION

Nico Bontenbal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sbdt8631

ASKER

That worked. Thank you