Solved

Error Calculating Percentages in SQL Reporting Services 2005 SP1

Posted on 2007-04-09
5
1,130 Views
Last Modified: 2008-01-09
Hello Experts,
I have created a report in SSRS 2005 SP1 and I'm running into a problem evaluating the following expression:
=iif(Fields!xtndprce.Value=0,0,(Fields!xtndprce.Value-Fields!extdcost.Value)/Fields!xtndprce.Value)

When the report is built, the following error appears:
[rsRuntimeErrorInExpression] The Value expression for the textbox ‘textbox70’ contains an error: Attempted to divide by zero.

As you can see from the expression, I am making sure the denominator of my formula (Fields!xtndprce.Value) is not zero.  It seems as if SSRS is evaluating the "false" part of my expression regardless of whether the condition is true or false.  I have also tried "IsError" with the same results ("False" if Fields!xtndprce.Value<>0 and "#Error" if it is).

Thanks in advance.
0
Comment
Question by:mikesauce
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18876848
=iif(Fields!xtndprce.Value=0,0, iif(Fields!xtndprce.Value=0,0,(Fields!xtndprce.Value-Fields!extdcost.Value)/Fields!xtndprce.Value))
0
 
LVL 1

Author Comment

by:mikesauce
ID: 18877154
Hi angelIII,
That didn't work either...same error message.
Thanks!
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 18878413
sorry, I pasted the wrong code :-(
please try this:
=iif(Fields!xtndprce.Value=0, 0, Fields!xtndprce.Value-Fields!extdcost.Value) / iif(Fields!xtndprce.Value=0, 1, Fields!xtndprce.Value)
0
 
LVL 1

Author Comment

by:mikesauce
ID: 18878544
That worked.  Any idea on why RS looks trys to evaluate the second part of the iif statement regardless?  
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18878576
"yes". I know that all microsoft database products work like this, they do NOT have the "C" programming manner to for example stop evaluation of a boolean OR expression as soon as they found true or a AND expression as soon as they found false. similary here, all expression parts are evaluated, despite of the "fact" that in some cases, some expressions evaluations are not needed.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question