Display set text when string is null?

[kgl_m]

Hi, I thought this was fairly simple but apparently I'm missing something...

We have a field that can contain text or null. This field is in a List Group Header, so I would like it to display the text "Others" when there are null values that don't fit anywhere else. Right now it just gives us a header with no text for all values that lack a name.

I tried various variations of this text, but it didn't work. Could anyone help?

case when cast([Presentation Layer].[Organizations].[Lead Department],varchar(20)) =  '' then 'Others'
else [Presentation Layer].[Organizations].[Lead Department]
end

[jayani]

Hi,

There is a solution.
Go to the column for which you want to display 'Others' for null values.
In Properties pane-> Data Format -> e.g. Text (Select the type which suits the column's datatype)
-> In 'Missing value characters' type 'Others'.
Now, whereever null values are there for that particular column, 'Others' will be displayed.... :)

~Jayani

[blom0344]

null <> empty string !!

Other than by using formatting, the best option is the coalesce function:

coalesce([somedataitem],'Other')

You should interpret a null as a missing or 'unknown' value, rather than an empty string.
(which is why your expression fails)