SSRS 2008 R2 Carriage Return Problem

Question

I have a report with a simple textbox that holds Name, Address, and ZipCode fields. The fields work fine when previewed but when I put them in a block format like:

Name
Address
Zipcode

I get double spaced text. A friend showed me a little trick, putting all the fields on one line and instead of hitting return I hit Shift + Return. This worked but only for one line. In other words, I got this result:

Name
Address

ZipCode

Etc.

I'm sure this is a trivial problem that an experienced user could solve in a second. Unfortunately, I am not experienced. So, Does anyone have a fix for this?

Answer 1

I'm using this dataset:

select 'Mr2Bool' as Name,
'1 TrueStreet' as Address1,
 NULL as Address2,
'NewTrueshire' as Address3,
'1010101' as ZipCode

and put in a Textbox with the following expression:

= First(Fields!Name.Value, "DataSet1") & VBCRLF &
First(Fields!Address1.Value, "DataSet1") & VBCRLF &
IIF(First(Fields!Address2.Value, "DataSet1") Is Nothing, "", First(Fields!Address2.Value, "DataSet1")  & VBCRLF) &
IIF(First(Fields!Address3.Value, "DataSet1") Is Nothing, "", First(Fields!Address3.Value, "DataSet1")  & VBCRLF) &
First(Fields!ZipCode.Value, "DataSet1")

which gives the following output:

VBCRLF stands for "Visual Basic Carriage Return Line Feed", and gives a new line. If a field is null, then no new line is added, so you don't get any breaks in the address.

You'll have to decide which fields can be null. I assumed that Name, Address1 and ZipCode cannot be null, but maybe you set up things differently.

Answer 2

Here's some code for a mailing label, hope it helps someone later.

=IIf(IsNothing(Fields!WholeName.Value), "", Fields!WholeName.Value) & vbCRLF & 
IIf(IsNothing(Fields!Address1.Value), "", Fields!Address1.Value) & vbCRLF & 
IIf(IsNothing(Fields!Address2.Value), "", Fields!Address2.Value) & vbCRLF & 
IIf(IsNothing(Fields!City.Value), "", Fields!City.Value) & ", " & 
IIf(IsNothing(Fields!State.Value), "", Fields!State.Value) & " " & 
IIf(IsNothing(Fields!Zip.Value), "", Fields!Zip.Value)

Answer 3

Here's a small Addition :

You can do this in the SQL of your report:

SELECT Postalcode + ' ' + City + ', ' + Country as pccString FROM ....

and in a Textbox in the report

=First(Fields!pccString.Value).Replace(",", VbCrLf)