Post reply

Expression in SSRS to deal with Column containing NULLS

  • October 22, 2015 at 8:27 am

    #1834915

    Nulls are represented by the value Nothing in SSRS and like in SQL they are distinct from 0 or an empty string etc.

    In your expression you are comparing Nothing >= 1 which doesn't work as Nothing is not a number. You have to explicitly check if the value is Nothing using the "Is" operator.

    Try:

    =Iif(Max(Fields!pccount.Value) Is Nothing, "None", Max(Fields!pccount.Value))

    An important thing to note is the way different aggregations behave with Nothing. For example - Count on a column of Nothing will return 0, since Count is a count of the number of values in the column, so always returns an integer of 0 or more. So you could also use:

    =Iif(Count(Fields!pccount.Value) > 0,Max(Fields!pccount.Value),"None")

    The Max of a column of Nothing will return Nothing since Max returns the highest valued value in the Column. In a column with mixed values and Nothings, Min will return the lowest actual value in the column. Sum returns the sum of any values in column or Nothing if the column is all Nothing.

    So for example in SSRS:

    For Values = 10, NULL, 20, 30, NULL, 40

    Max = 40, Min = 10, Count = 4, Sum = 100

    For Values = NULL, NULL, NULL, NULL, NULL, NULL

    Max = Nothing, Min = Nothing, Count = 0, Sum = Nothing

    The below link explains Nothing in greater detail:

    https://msdn.microsoft.com/en-us/library/0x9tb07z.aspx

Viewing post 1 (of 2 total)

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