Empty or Null value display in SSRS text boxes

Question

Using SSRS (2008) what is the best way you have found to handle null or empty values and replace them with something else to display. The quite obvious solutions is

=IIF(IsNothing(Fields!MyField.Value),"NA",Fields!MyFields.Value)

That works though it is tedious, my hope was to find something like an EmptyText property on the textbox but alas nothing. Any ideas? Should I make a custom report item from the TextBox base that creates this property?

Answer 1

While probably not any better than your solution, you could adjust your T-SQL to return the same result using COALESCE:

SELECT MyField = COALESCE(table.MyField, " NA")

The reasoning for the extra space before the NA is to allow sorting to place the NA results at the top. Since your data may vary, that may not be a great option.

Answer 2

Call a custom function?

http://msdn.microsoft.com/en-us/library/ms155798.aspx

You could always put a case stmt in there to handle different types of 'blank' data

Answer 3

I agree on performing the replace on the SQL side, but using the ISNULL function would be the way I'd go.

SELECT ISNULL(table.MyField, "NA") AS MyField

I usually do as much processing of data on our SQL servers and try to do as little data manipulation in SSRS as possible. This is mainly because my SQL server is considerably more powerful than my SSRS server.