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
Solved

Error Calculating Percentages in SQL Reporting Services 2005 SP1

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

860 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