locked
SUM Function RRS feed

  • Question

  • I'm trying to sum a string column that looks like this

    hhhh:mm

    152:20

    103:15

    -

    101:45

    Trouble is there are "-" in the result set being returned

    Anyone have ideas how i could do this?

    The result has to be in the same format

    Thanks

    Dave

     

     

     

     

     

     

    Friday, March 9, 2007 10:09 AM

Answers

  • Create a custom dataset field (say "test") in the report which will have the same value as it is returned but without '-', you can use Replace function to replace all '-' with a blank string ''

    Then use an expression something like this:

    Str(Sum(CInt(Mid(Fields!test.Value, 1, InStr(Fields!test.Value,":")))) & ":" & Str(Sum(CInt(Mid(Fields!test.Value, InStr(Fields!test.Value,":")+1)))

    Hope this helps.

    Shyam

    Friday, March 9, 2007 10:21 AM

All replies

  • Create a custom dataset field (say "test") in the report which will have the same value as it is returned but without '-', you can use Replace function to replace all '-' with a blank string ''

    Then use an expression something like this:

    Str(Sum(CInt(Mid(Fields!test.Value, 1, InStr(Fields!test.Value,":")))) & ":" & Str(Sum(CInt(Mid(Fields!test.Value, InStr(Fields!test.Value,":")+1)))

    Hope this helps.

    Shyam

    Friday, March 9, 2007 10:21 AM
  • Dont know whether this would work, but try this.

    =SUM(IIF(Fields!test.value = "-", Cint(0), Fields!test.value)
    I am not sure whether the ":" would be a factor or not.

    Bernard Ong

    Friday, March 9, 2007 2:42 PM