Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Don’t seek, don’t search, don’t ask, don’t knock, don’t demand – relax. If you relax, it comes. If you relax, it is there. If you relax, you start vibrating with it
 
Osho
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 *

To prove you are human what is:

98 + 96 =