SSRS Hide empty address value including whitespace using VB expression-CodePudding

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.

CodePudding user response：

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") = ""