1

I am creating a SSRS Report to produce some address labels. I have a SELECT statement as follows:

SELECT DISTINCT 
                         AddressLink.intAddressID, Addresses.txtLabelSalutation, Addresses.txtAddress1, Addresses.txtAddress2, Addresses.txtAddress3, Addresses.txtTown, 
                         Addresses.txtCounty, Addresses.txtCountry, Addresses.txtPostcode, Addresses.txtRelationType, Pupils.intNCYear, Pupils.txtSchoolID, Pupils.txtForename, 
                         Pupils.txtSurname
FROM            TblPupilManagementAddresses AS Addresses INNER JOIN
                         TblPupilManagementAddressLink AS AddressLink ON Addresses.TblPupilManagementAddressesID = AddressLink.intAddressID LEFT OUTER JOIN
                         TblPupilManagementPupils AS Pupils ON AddressLink.txtSchoolID = Pupils.txtSchoolID
WHERE        (Pupils.intSystemStatus = 1) AND (Addresses.txtAddress1 <> '') AND (Addresses.txtRelationType <> 'OLD Billing') AND (Addresses.intJustContact <> 1)

And within the report itself I am using the following expression to produce the addresses:

=Fields!txtLabelSalutation.Value & vbCRLF & 
Fields!txtAddress1.Value & vbCRLF &
IIF(
       Fields!txtAddress2.Value <> "", Fields!txtAddress2.Value, nothing) & " " &
       IIF (
                     Fields!txtAddress3.Value<> "", Fields!txtAddress3.Value, nothing) & vbCRLF & Fields!txtTown.Value
       & vbCRLF &
       IIF(
              Fields!txtCounty.Value <> "", Fields!txtCounty.Value, nothing) & vbCRLF &
              IIF(
                     Fields!txtCountry.Value <> "United Kingdom", Fields!txtCountry.Value, nothing) & vbCRLF & Fields!txtPostcode.Value

My issue is that this expression is producing blank lines for fields that have no data, so I am getting a format like:

Mr & Mrs Smith
22 Somewhere Street


Somewhere Town

SS48 9TQ

How can I concatenate rows together if there are blanks so the address appears in one clean block? I am sure I need to amend my expression, but I am not sure how. I need it to look like:

Mr & Mrs Smith
22 Somewhere Street
Somewhere Town
SS48 9TQ

I have already tried amending the SQL statement using a CAST, however this then removes the DISTINCT clause and this part is vital. I also tried by grouping with the cast but that also failed.

2

With a bit of help I have solved this, but thought I would post the answer in case it helps someone else.

I have changed the expression to read:

=Fields!txtLabelSalutation.Value & vbCRLF & Fields!txtAddress1.Value & vbCRLF &
    IIF(
            Fields!txtAddress2.Value <> "", Fields!txtAddress2.Value & vbCRLF, nothing) &
    IIF (
            Fields!txtAddress3.Value <> "", Fields!txtAddress3.Value & vbCRLF, nothing) &
    IIF(        
            Fields!txtTown.Value <> "", Fields!txtTown.Value & vbCRLF, nothing) &
    IIF(
            Fields!txtCounty.Value <> "", Fields!txtCounty.Value & vbCRLF, nothing) &
    IIF(
            Fields!txtCountry.Value <> "United Kingdom", Fields!txtCountry.Value & vbCRLF, nothing) &
    IIF(
            Fields!txtPostcode.Value <> "", Fields!txtPostcode.Value & vbCRLF, nothing)

My problem was where I was including the line break within the statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.