• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 812
  • Last Modified:

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?
0
sbdt8631
Asked:
sbdt8631
  • 4
  • 4
  • 2
  • +1
1 Solution
 
dqmqCommented:
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
0
 
sbdt8631Author Commented:
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.
0
 
dqmqCommented:
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)

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Nico BontenbalCommented:
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:
Sample
But you might find the solution for your problem here:
http://www.sqlservercentral.com/Forums/Topic442497-150-1.aspx
0
 
Alpesh PatelAssistant ConsultantCommented:
=IIF(Fields!Order.Value >0,  Fields!Order.Value, Join("-" , CStr(Fields!Order.Value))
0
 
Nico BontenbalCommented:
@PatelAlpesh, I get an error when I use that expression, did you test it?
0
 
sbdt8631Author Commented:
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
0
 
sbdt8631Author Commented:
I just viewed the file I attached and it did not export very well.  Page two demonstrates the problem.
0
 
Nico BontenbalCommented:
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?
0
 
Nico BontenbalCommented:
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? I tried to duplicate your problem in 2005, but got the same results as in my screenshot in comment 35711849 with this expression:
=iIf(not(isnothing(fields!MonthSales1_LY.value)) and not(isnothing(fields!MonthSales1.value)),fields!MonthSales1.value/fields!MonthSales1_LY.value - 1,"-")
0
 
sbdt8631Author Commented:
That worked.  Thank you
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now