Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Continuous effort—not strength or intelligence—is the key to unlocking our potentia
Winston Churchill
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!


How Do I Exclude Blank Lines in Concatenated Fields in SQL Server Reporting Services

The Issue:

During a recent consultancy the client asked how they could exclude spaces in their address concatenated fields, due to missing data, in SQL Server Reporting Services.

The Solution:

Create an expression formula that checks to see if the address fields are empty, before adding them into the formula:

=Fields!CompanyName.Value & vbcrlf &

TRIM(Fields!Address_1.Value) & vbcrlf &

IIF(ISNOTHING(Fields!Address_2.Value) OR Fields!Address_2.Value ="", "",

TRIM(Fields!Address_2.Value) & vbcrlf) &

IIF(ISNOTHING(Fields!Town.Value) OR Fields!Town.Value ="", "",

TRIM(Fields!Town.Value) & vbcrlf) &

IIF(ISNOTHING(Fields!City.Value) OR Fields!City.Value ="", "",

TRIM(Fields!City.Value) & vbcrlf) &

IIF(ISNOTHING(Fields!County.Value) OR Fields!County.Value ="", "",

TRIM(Fields!County.Value) & vbcrlf) &



Name *

Email Address *

Comment *

To prove you are human what is:

60 + 21 =