Expression in SSRS to deal with Column containing NULLS

• rkelly58

  SSCrazy

  Points: 2151

  More actions

  October 20, 2015 at 9:50 am

  #312433

  Hi,

  Wonder if someone can help:

  I have column (Pccount) which displays as follows:

  NULL

  NULL

  NULL

  10

  10

  10

  10

  NULL

  10

  NULL

  I want to do an expression on Pccount to display the word "NONE" when the entire column is full of ALL Nulls, otherwisee show the MAX of the biggest number in that column

  I tried this: =IIF(Max(Fields!pccount.Value) >= 1, Max(Fields!pccount.Value),"NONE ")

  But it displays NULL when I run my report... Any ideas what the correct format is off this please?

  Thanks,

• Spiff

  SSCertifiable

  Points: 7826

  More actions

  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