Solved

Error Calculating Percentages in SQL Reporting Services 2005 SP1

Posted on 2007-04-09
5
1,122 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now