Solved

Error Calculating Percentages in SQL Reporting Services 2005 SP1

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

11 Experts available now in Live!

Get 1:1 Help Now