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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I had a similar situation but the following worked best for me     Iif Fields Sales Diff Value  gt 1 Fields Sales Diff Value

User · Answer

Try this    IIF IsNothing Fields MyField Value  TRUE  NA  Fields MyFields Value

User · Answer

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

[ssrs-2008] Empty or Null value display in SSRS text boxes

Examples related to ssrs-2008

Examples related to reporting-services