SSRS Series - How to format addresses in SSRS

·         How to format an address block in SSRS, ideal for SSRS generated letters, invoices, bills and orders. 

·         Removes any blank address lines that the record has in your database.

·         Adds carriage returns after each populated line to give the distinctive address block appearance

There are two ways to approach msaaging/formatting fields into an address block in SSRS, you can either carry out your formatting in the SQL that populates the dataset or you can format the fields from the dataset in an SSRS expression. 

I’ve found that the easiest method is to format the address through an SSRS expression contained in the report, this keeps the SQL that populates the dataset as clear as possible and any issues associated with the expression are easily identifiable.

Example Data

·         Stored in dataset titled “DATASET”

·         The exact number/naming of fields will differ depending on your database.

MAILING_NAME	Mr J Davies
ADD1	The Whitehouse
ADD2
ADD3	Reindeer Road
ADD4
ADD5
TOWN	Reading
PCODE	NA13 6DF
COUNTRY	United Kingdom

Example expression

·         Create a new text box and add the following expression (change the name of the dataset/fields to reflect your data).

=First(Fields!MAILING_NAME.Value,”DATASET”)+IIF(First(Fields!MAILING_NAME.Value,”DATASET”) ="","",", "+VbCrLf)

+First(Fields!ADD1.Value,”DATASET”)+IIF(First(Fields!ADD1.Value,”DATASET”) ="","",", "+VbCrLf)

+First(Fields!ADD2.Value,”DATASET”)+IIF(First(Fields!ADD2.Value,”DATASET”) ="","",", "+VbCrLf)

+First(Fields!ADD3.Value,”DATASET”)+IIF(First(Fields!ADD3.Value,”DATASET”) ="","",", "+VbCrLf)

+First(Fields!ADD4.Value,”DATASET”)+IIF(First(Fields!ADD4.Value,”DATASET”) ="","",", "+VbCrLf)

+First(Fields!ADD5.Value,”DATASET”)+IIF(First(Fields!ADD5.Value,”DATASET”) ="","",", "+VbCrLf)

+First(Fields!TOWN.Value,”DATASET”)+IIF(First(Fields!TOWN.Value,”DATASET”) ="","",", "+VbCrLf)

+First(Fields!PCODE.Value,”DATASET”)+IIF(First(Fields!PCODE.Value,”DATASET”) ="","",""+VbCrLf)

+First(Fields!COUNTRY.Value,”DATASET”)

Example report view

Mr J Davies,

The Whitehouse,

Reindeer Road,

Reading,

NA13 6DF,

United Kingdom