locked
SUM negative numbers in expression RRS feed

  • Question

  • I am trying to use the sum function in reporting services, and when all the numbers are negative it just errors out. Why is the happening?

    I should also state its used in a IIF expression. Everytime it just throws an #error

    ex.

    =
    IIF(SUM(Fields!TestNumbers.Value <= -1),"-", "test")

     

    Thursday, April 1, 2010 6:16 PM

Answers

  • thats what I wrote in my original question.

    Actually my formula is very different than yours - note the placement of the parentheses.

    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    • Marked as answer by spark29er Thursday, April 1, 2010 6:36 PM
    Thursday, April 1, 2010 6:24 PM

All replies

  • Well, there are a number of things wrong with this expression.  First of all, "SUM(Fields!TestNumbers.Value <= -1)" will not return a boolean type, so the IIF statement cannot work. 

    Maybe try this:

    =IIF(SUM(Fields!TestNumbers.Value) <= -1,"-","test")

    That shouldn't error out.



    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    Thursday, April 1, 2010 6:20 PM
  • thats what I wrote in my original question.
    Thursday, April 1, 2010 6:23 PM
  • But really, all this is going to do is check if the SUM of TestNumbers as as whole is negative, and if so it will return "-", and if not it will return "test".

    So when you simply do "=SUM(Fields!TestNumbers.Value)" , does this error out as well?



    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    Thursday, April 1, 2010 6:23 PM
  • thats what I wrote in my original question.

    Actually my formula is very different than yours - note the placement of the parentheses.

    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    • Marked as answer by spark29er Thursday, April 1, 2010 6:36 PM
    Thursday, April 1, 2010 6:24 PM
  • Try this way:

    =

    IIF(SUM(Fields!value.Value) <= -1,

    "-", "test")

     

    you just misplaced that brace i think.

    Thursday, April 1, 2010 6:27 PM
  • Try this way:

    =

    IIF(SUM(Fields!value.Value) <= -1,

    "-", "test")

     

    you just misplaced that brace i think.

    Thursday, April 1, 2010 6:27 PM
  • Aaron is right, you need iif condition if you don't want to sum up negative numbers

    =sum(iif(fields!myfield.value < 0, 0, fields!myfield.value))

    or

    just add all the numbers

    =sum( fields!myfield.value)

     


    mark it as answer if it answered your question :)
    Thursday, April 1, 2010 6:29 PM
  • thanks, works now
    Thursday, April 1, 2010 6:36 PM