0

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

Pedram
  • 6,256
  • 10
  • 65
  • 87
dp3
  • 1,607
  • 4
  • 19
  • 28

1 Answers1

0
=SUM(IIF(Fields!acct.Value >= "40000" and Fields!acct.Value <= "50000", Fields!amt.Value, 0))
msmucker0527
  • 5,164
  • 2
  • 22
  • 36
  • msmucker, tried that. I get the following error: The Value expression for the textrun ‘Textbox167.Paragraphs[0].TextRuns[0]’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data. I tried putting CInt() before everything, changing 0 to NOTHING, using FormatCurrency(), etc. All values are numeric. I'm really confused here... – dp3 Sep 10 '12 at 19:13
  • I tried the following code: =SUM(IIF(Fields!acct.Value >= CInt(90200) and Fields!acct.Value <= CInt(90299), (CInt(Fields!amt.Value)), NOTHING)) – dp3 Sep 10 '12 at 19:14
  • The datatype for the acct is nvarchar and for the amt it is decimal. – dp3 Sep 10 '12 at 19:16
  • 1
    try adding quotes around the acct filters (see edit above), if it's not that then it has to be getting a string value for Fields!amt.value, make sure it's a numeric type – msmucker0527 Sep 10 '12 at 19:32
  • Thank you, it still gives the error but ultimately still loads in the report! – dp3 Sep 10 '12 at 20:06