When you are creating a CRM report involving an address block, you will need to take into account the fact that most of the time, the additional address lines will be blank.  Since it is generally bad form to have extra blank lines in your address block, we need a way of only adding those fields with data.  Take a look at the following, pretty normal address block layout with examples:

Address Block Layout:

Customer Name
Address Line 1
Address Line 2
City, State Zipcode

Example 1:

Bob Smith
123 South Center Street
Apt. 3A
Dallas, TX 00111

Example 2:

Bob Smith
123 South Center Street, Apt 3A

Dallas, TX 00111

Notice how the second example's Address Line 2 is blank, and therefore we have that ugly blank line in the middle of our address?  Well, I don't know about you, but most of my customers ( and me ) find this unacceptable and want the extra blank line removed.

To accomplish this, we will use the Visual Basic IIF function.  This function performs a test that you specify and returns one value if true, and another value if false.

So, here is how we would create the expression to populate a text box within a SQL Reporting Services report that will contain our customer's mailing address:

=Fields!name.Value & vbCRLF &
Fields!address1_line1.Value & vbCRLF &
IIF(IsNothing(Fields!address1_line2.Value), "", Fields!address1_line2.Value & vbCRLF) &
Fields!address1_city.Value & ", " &
Fields!address1_postalcode.Value & " " &
Fields!address1_stateorprovince.Value

Let's break down some of the more special features of this calculation:

  • vbCRLF is the Visual Basic pre-defined constant that contains the values of a carriage return and a line feed - what is sometimes called a "New Line" character.
  • IsNothing is a Visual Basic function that returns TRUE if the variable being tested is blank.
  • The & sign is used to concatenate two strings.

The pseudo code for the IIF statement looks like this:

  1. Check to see if the address1_line2.Value Field is blank.
  2. If it is blank, return a blank ( "" ).
  3. If it is not blank, return the address1_line2.Value Field with a carriage return, line feed character appended to it.

 

Note: You will need to substitute the field names found in your report for the ones listed in the expression.

References:

Visual Basic Language Reference: IIf Function.