Expression in SSRS not working as expected

Question

I have an SSRS report which I want to sum values of a field, but only if the value of another field is equal to 1, as I have made the report output a row number for each row. Bascially, I'm trying to sum the distinct values to come up with a total. Screenshot below. I'm getting an error for orders with more than 1 item. My expression used to calculate the Ship Cost (red text) is as follows ``` =SUM(IIF(Fields!RowNumber.Value = 1, Fields!WEIGHT.Value, 0)) ``` But I'm getting the #Error. The cell that has the text #Error should be reading $11.25.

Accepted Answer

I think you're likely getting a datatype mismatch in the aggregate; SSRS can't handle implicit conversions in these sort of `IIf`/aggregate expressions.

In your expression, `0` will be treated as an int, and assuming the underlying data type of `Fields!WEIGHT.Value` is decimal or double, this will throw a runtime error - if you preview in BIDS it should actually display the error.

To get around this you need to make sure the two `IIf` results have the same datatype - something like:

```
=SUM(IIF(Fields!RowNumber.Value = 1, Fields!WEIGHT.Value, 0.0))
```

or

```
=SUM(IIF(Fields!RowNumber.Value = 1, Fields!WEIGHT.Value, CDec(0)))
```

or

```
=SUM(IIF(Fields!RowNumber.Value = 1, Fields!WEIGHT.Value, CDbl(0)))
```

Depending on the underlying datatype you may have to try a few combinations but hopefully one of the examples will work correctly,

Expression in SSRS not working as expected

Solution

Problem