Solved

Error Calculating Percentages in SQL Reporting Services 2005 SP1

Posted on 2007-04-09
5
1,126 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
  • 3
  • 2
5 Comments
 
LVL 142

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 142

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 142

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

805 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