4

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))

enter image description here

But I'm getting the #Error. The cell that has the text #Error should be reading $11.25.

Pedram
  • 6,256
  • 10
  • 65
  • 87
kyle_13
  • 1,173
  • 6
  • 25
  • 47
  • How are you ensuring that the expression looks only at the rows for a given Order #? – Ben C. Aug 14 '13 at 16:06
  • I believe that is covered by the grouping in the report, the row is grouped on Order #. The correct value is displaying for the second order, so it does appear to be looking at that given order. – kyle_13 Aug 14 '13 at 16:15
  • 1
    It looks like a _formatting_ issue. What is the datatype of Fields!Weight.Value field? Try this `=SUM(IIF(Fields!RowNumber.Value = 1, CDBL(Fields!WEIGHT.Value), 0.0))` . If that doesn't work Try `=SUM(CDBL(Fields!WEIGHT.Value))` and see if you still get errors and attack the problem from there. – Anup Agrawal Aug 14 '13 at 16:38
  • Hi @Anup Agrawal, you are correct in that it was a data conversion issue. Thanks. – kyle_13 Aug 15 '13 at 14:02

2 Answers2

3

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,

Ian Preston
  • 38,816
  • 8
  • 95
  • 92
  • Hey @Ian Preston, that was it! It was a data conversion issue. The second statement you gave me did the trick. Thanks so much, such a simple issue that was driving me crazy. – kyle_13 Aug 15 '13 at 14:01
0

See here: http://www.bidn.com/forums/microsoft-business-intelligence/reporting-services/589/how-to-sum-at-group-level

If your group is called "Order#", your expression should be:
=SUM(IIF(Fields!RowNumber.Value = 1, Fields!Weight.Value, 0),"Order#")

Ben C.
  • 1,761
  • 5
  • 15
  • 24