Search code examples
vbavisual-studioreporting-servicesremoving-whitespace

SSRS Hide empty address value including whitespace using VB expression

For a SSRS report, I need to produce the full customer address formatted as:

Customer Name
Address 1
Address 2
City, State ZIP
Country

My problem is that sometimes "Address 2" has a value and sometimes not. When it doesn't have a value, then it will display like this, with an unwanted gap:

Customer Name
Address 1

City, State ZIP
Country

I have several datasets in this SSRS report, for the address, they are individual VB expressions (not tablix or textbox) so I would like to solve it using a formula in the expression.

Right now I have an expression that displays "Address 1" then creates a line break before displaying "Address 2", example:

=First(Fields!Address_1.Value, "InvoiceData") & vbcrlf &
First(Fields!Address_2.Value, "InvoiceData")

So I think the solution is to change the coding so that the line break and showing "Address 2" is conditional based on whether or not "Address 2" has a value, but I'm not sure how to code that with proper syntax.

Please keep in mind, I need to refer to the DataSet "InvoiceData" in any suggested solution.

I also did explore turning on "ConsumeContainerWhitespace" on the report, this did not take care of it however.

Thank you.

Solution

  • You can use an IIF in your expression to check for a NULL value using the ISNOTHING function. You'll want to keep the VBCRLF in the IIF so that it doesn't "appear" when there's no address2.

    =First(Fields!Address_1.Value, "InvoiceData") & 
    IIF(ISNOTHING(First(Fields!Address_2.Value, "InvoiceData"))
        ,""
        , vbcrlf & First(Fields!Address_2.Value, "InvoiceData")
        )

    If your data has a blank value instead of a NULL value for Address 2, then you'd change the check condition to look for blanks:

    IIF(First(Fields!Address_2.Value, "InvoiceData") = ""