Post reply

Matrix iif Expression to deal with NULL and Empty Values

  • September 27, 2012 at 4:16 am

    #264919

    Anyone,

    I am trying to sum up some data based on an iif statement in an expression ni a Matrix table.

    In basic form its:

    iif(Fields!A.Value = "Y", sum(Fields!B.Value),0)

    Because fields b contains NULL and empty "values" the sum will not work.

    I have tried using the following to eliminate NULLs and empty "Values":

    iif(Fields!A.Value = "Y", iif(Fields!B.Value IS NOTHING or Fields!B.Value = "" ,0,sum(Fields!B.Value)),0)

    Also tried

    iif(Fields!A.Value = "Y", iif(Fields!B.Value = "null" or Fields!B.Value = "" ,0,sum(Fields!B.Value)),0)

    Both seem not to work.

    Does anyone know how to deal with these Values?

    Regards

    Newbie.

  • September 27, 2012 at 4:30 am

    #1542782

    can you provide more details? or a sample code?

    containing output scenario based on the inputs.

  • September 27, 2012 at 4:57 am

    #1542790

    Hi Vivek,

    Well thats basically it.

    I have one dataset just so that I can test.

    It has multiple colums and I look at a particulat column that has either Y or N (Yes of No) and based on what value I want I want to sum up a count row to get the total count.

    This is what my actual Expression looks like:

    =iif(Fields!INTERNET_DISCOUNT_IND.Value = "Y",iif(Fields!Policy___Record_Count.Value is nothing or Fields!Policy___Record_Count.Value = "",0,sum(Fields!Policy___Record_Count.Value)),0)

    Results either return a zero or error.

  • September 27, 2012 at 10:01 am

    #1542985

    try case statement

    CASE

    WHEN Fields!A.Value = "Y" THEN sum(Fields!B.Value)

    ELSE 0

    END

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

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