Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
A powerful imagination is something to strive for
 
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!!!!

17
August

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) &

TRIM(Fields!Post_Code.Value)

MAKE A COMMENT

Name *

Email Address *

Comment *