Empty or Null value display in SSRS text boxes

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 obvio开发者_JAVA技巧us 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?

=IIF(ISNOTHING(CStr(Fields!MyFields.Value)) or CStr(Fields!MyFields.Value) = "","-",CStr(Fields!MyFields.Value))

Use this expression you may get the answer.

Here CStr is an default function for handling String datatypes.

Call a custom function?

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

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

I would disagree with converting it on the server side. If you do that it's going to come back as a string type rather than a date type with all that entails (it will sort as a string for example)

My principle when dealing with dates is to keep them typed as a date for as long as you possibly can.

If your facing a performance bottleneck on the report server there are better ways to handle it than compromising your logic.

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.

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.

Either in SQL or in report code (as per adolf garlic's function suggestion)

At this moment in time, I'd do it in the report. I have very few reports against a busy OLTP server and an underwhelmed report server. If I had a different mix I'd do it in SQL.

Either way is acceptable...

I couldn't get IsNothing() to behave and I didn't want to create dummy rows in my dataset (e.g. for a given list of customers create a dummy order per month displayed) and noticed that null values were displaying as -247192.

Lo and behold using that worked to suppress it (at least until MSFT changes SSRS for the better from 08R2) so forgive me but:

=iif(Fields!Sales_Diff.Value = -247192,"",Fields!Sales_Diff.Value)

Try this

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

I had a similar situation but the following worked best for me..

=Iif(Fields!Sales_Diff.Value)>1,Fields!Sales_Diff.Value),"")