Conditional SUM IIF statement

  • Shabbaranks

    Right there with Babe

    Points: 742

    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: 4530

    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

    Right there with Babe

    Points: 742

    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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply