It is always possible that NULL or white spaces appear in the dimensional attributes. If we create a prompt with control type "multi-select" for that dimension, we will see blanks appearing in the list of values. My customer said for any prompt they don't want to see NULL appearing, instead they want to see some text, something like, say "NULL VALUE" .
How to do this?
Add the below in Show "SQL Results" of the prompt
SELECT CASE WHEN TRIM(TABLE."COL NAME") IS NULL THEN 'NULL VALUE' ELSE TABLE."COL NAME" END FROM SUBJECT AREA
TRIM function in the above SQL will convert white spaces to NULL which is then decoded to 'NULL VALUE' using the CASE statement
