SUM based on boolean range - SSRS

• dj1202

  SSCommitted

  Points: 1829

  More actions

  September 10, 2012 at 12:49 pm

  #263952

  Hello everyone, I really need help summing values based on a range (in an SSRS expression).

  I have a dataset that pulls accounts and money values.

  SELECT acct, location, amt FROM Table

  I need help creating an expression for a tablix. I'd like to sum the amt values for accounts lying within a range. For example, I have accounts ranging from 40000 to 99999. So I'd like to do, for the expression:

  =IIF(Fields!acct.Value >= 40000 and Fields!acct.Value <= 50000, SUM(Fields!amt.Value), "0.00")

  The problem with the expression above is that it is returning the wrong sum. I believe it's summing all available amt values from the dataset when an acct falls between that range. I need it to only sum the amt values that fall between the range. I can't simply make the range smaller, because eventually I am trying to do more calculations on other ranges within the expression, similar to...

  =(IIF(Fields!acct.Value >= 40000 and Fields!acct.Value <= 50000, SUM(Fields!amt.Value), "0.00")) - (IIF(Fields!acct.Value >= 55000 and Fields!acct.Value <= 99999, SUM(Fields!amt.Value), "0.00"))

  I am not sure where to look to see the syntaxical issue with this. Do I throw a boolean within the SUM? Really stuck on this...

• Spiff

  SSCertifiable

  Points: 7826

  More actions

  September 11, 2012 at 6:34 am

  #1536193

  I think you need to be putting the Iif statement in the Sum i.e.

  =SUM(IIF(Fields!acct.Value >= 40000 and Fields!acct.Value <= 50000,Fields!amt.Value,0))

  This way it evaluautes the Iif statement for each row and sums the result returned.