Conditional SUM IIF statement

• Shabbaranks

  SSC Eights!

  Points: 880

  More actions

  March 21, 2019 at 5:47 am

  #382028

  Hi,
  Im working on a report where I would like to sum a quantity if 2 column values are met - 1 column is text based and the other number based. Currently I have the expression as
  =Sum(IIF(Fields!ProductName.Value="TEST" And Fields!users_text04.Value="99", Fields!PAL_oqty.Value, 0))
  but all I get from this expression is 0 I assum because the conditions aren't met yet the records returned within the ProductName field are TEST and users_text04 contain the values 99.
  Any ideas?
  Thanks

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  March 21, 2019 at 7:39 am

  #2025102

  Shabbaranks - Thursday, March 21, 2019 5:47 AM

  Hi,
  Im working on a report where I would like to sum a quantity if 2 column values are met - 1 column is text based and the other number based. Currently I have the expression as
  =Sum(IIF(Fields!ProductName.Value="TEST" And Fields!users_text04.Value="99", Fields!PAL_oqty.Value, 0))
  but all I get from this expression is 0 I assum because the conditions aren't met yet the records returned within the ProductName field are TEST and users_text04 contain the values 99.
  Any ideas?
  Thanks

  There may be 2 possibility . 

  1) You are passing Fields!users_text04.Value as string value instead of integer/number
  2) Fields!PAL_oqty.Value column may actually have zero as a value.

  Saravanan

• Shabbaranks

  SSC Eights!

  Points: 880

  More actions

  March 21, 2019 at 11:07 am

  #2025145

  Hi,
  When you say I might be passing Fields!users_text04.Value as a string how do I check? I thought that having "'s around the value defines what it is? On the properties of the field within the report I have set it to Number also. And with regards to  Fields!PAL_oqty.Value column may actually have zero as a value, it has various values but one of which being 99.

  Thanks