0

I am getting this error while dividing the two column Values in ssrs report.

1.OccupiedUnitsAtEndOfDate = 0.00

2.UnitCapacityAtEndOfDate = 0.00

These are the values I am Getting.

But If I put this expression I am getting #Error in result set for this expression.

=IIF(Sum(Fields!UnitCapacityAtEndOfDate.Value) = 0.00 OR 
Sum(Fields!OccupiedUnitsAtEndOfDate.Value) = 0.00, 0, 
Sum(Fields!OccupiedUnitsAtEndOfDate.Value) /Sum(Fields!UnitCapacityAtEndOfDate.Value))

Can anyone Suggest Me where I am doing wrong.Please help me on this?

boopathi s
  • 11
  • 4

2 Answers2

0

SSRS evaluates all of the possible output values in an IIF expression, regardless of whether the condition is true or false. Your expression needs to look as follows:

=IIF(Sum(Fields!UnitCapacityAtEndOfDate.Value) = 0.00 
    OR Sum(Fields!OccupiedUnitsAtEndOfDate.Value) = 0.00, 0, 
    Sum(Fields!OccupiedUnitsAtEndOfDate.Value) / IIF(Sum(Fields!UnitCapacityAtEndOfDate.Value)=0.00,1,Sum(Fields!UnitCapacityAtEndOfDate.Value))
Wesley Marshall
  • 434
  • 4
  • 16
-1

Try to declare the Dataset name for each field.

=IIF(Sum(Fields!UnitCapacityAtEndOfDate.Value, "DatasetName") = 0.00,0,IIF(
Sum(Fields!OccupiedUnitsAtEndOfDate.Value, "DatasetName") = 0.00, 0, 
Sum(Fields!OccupiedUnitsAtEndOfDate.Value, "DatasetName") /Sum(Fields!UnitCapacityAtEndOfDate.Value,"DatasetName")))
Aldrin
  • 756
  • 6
  • 18
  • thanks for your reply bro, – boopathi s Mar 14 '17 at 11:29
  • =IIF(Sum(Fields!UnitCapacityAtEndOfDate.Value,"dsSalesActivities") = 0.00 OR Sum(Fields!OccupiedUnitsAtEndOfDate.Value,"dsSalesActivities") = 0.00, 0, Sum(Fields!OccupiedUnitsAtEndOfDate.Value,"dsSalesActivities") / Sum(Fields!UnitCapacityAtEndOfDate.Value,"dsSalesActivities")) I put like this but now also it throws error only. – boopathi s Mar 14 '17 at 11:30
  • can anyone help me on this..is there any text box properties issue or some other else – boopathi s Mar 14 '17 at 12:16
  • Try the edited expression, I used two IIFs instead of OR. – Aldrin Mar 14 '17 at 12:24
  • I expect that this will not work, even as modified. SSRS evaluates both of the possible output values in an IIF expression, regardless of whether the condition is true or false. – Wesley Marshall Mar 15 '17 at 18:44