Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrm23
Posting Yak Master
198 Posts |
 Posted - 2008-08-12 : 05:57:46
|
| Hi,I am calulating effort in the layout using the fields from two different datasets.This effort is to be calculated for a particular date range.The effort is given by this expression:=IIf(Sum(Fields!planned_effort.Value, "Planned_effort"),0,FormatNumber((Sum(Fields!actual_effort.Value, "Actual_Effort")-Sum(Fields!planned_effort.Value, "Planned_effort"))/Sum(Fields!planned_effort.Value, "Planned_effort")))this worked fine till now. now, if i want to calculate for this particular date range - between 31-JUL-2008 and 31-AUG-2008 i am getting error. i found tht there are no records in the table for planned_effort for that date range.Now i want to display planned_effort as 0 if there are no rows in the table. how to do?Please helpThanks in advance |
|
|
sumit.microsofttech
Starting Member
6 Posts |
Posted - 2008-08-12 : 10:49:39
|
| Try using the COUNT function in the dataset and add one more outer IIF condition to your conditional statement of effort which will return 0 if Fields!Count.value = 0 otherwise calculate the result from your formula.Let me know if you get something like #ERROR |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-12 : 14:12:50
|
| =IIF(Sum(Fields!planned_effort.Value, "Planned_effort")>0,IIf(Sum(Fields!planned_effort.Value, "Planned_effort"),0,FormatNumber((Sum(Fields!actual_effort.Value, "Actual_Effort")-Sum(Fields!planned_effort.Value, "Planned_effort"))/Sum(Fields!planned_effort.Value, "Planned_effort"))),0) |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2008-08-13 : 01:20:36
|
| Hi,Thanks to you both for your reply. As sumit said i had used that count condition in my query itself.But it didnt work. Now i cant use it in the expression either. Its is giving #Error as you said.I tried to implement visakh's suggestion too. even that gives #Error.Is there any other way in which i can handle this?Thanks.... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-13 : 10:13:34
|
| [code]=IIF(Sum(Fields!planned_effort.Value, "Planned_effort")>0,IIf(Sum(Fields!planned_effort.Value, "Planned_effort"),0,IIF(Sum(Fields!planned_effort.Value, "Planned_effort")>0,FormatNumber((Sum(Fields!actual_effort.Value, "Actual_Effort")-Sum(Fields!planned_effort.Value, "Planned_effort"))/Sum(Fields!planned_effort.Value, "Planned_effort")),0)),0)[/code] |
|
|
|
|
|
|
|
|
Error when there are no rows for a column