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?

Eureka! Ok, this should help to get an answer. I actually have three Address lines. The example I'm pulling only uses one of these lines so the other two are not used and displaying an empty field. Is there a way to suppress the field if the value of said field is null. — 2boolORNOT2bool, Jun 20 '11 at 19:12

score 14 · Accepted Answer · answered Jun 21 '11 at 08:31

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:

Preview of Address rendered in Visual Studio

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.

Very Nice! I am slowly finding that VB is used in SSRS and I have NO VB background. I do appreciate the help! — 2boolORNOT2bool, Jun 21 '11 at 15:20
I'm also learning visual basic as I go along. The following website is quite useful: http://msdn.microsoft.com/en-us/library/ms157328.aspx — Fillet, Jun 21 '11 at 20:39

score 1 · Answer 2 · answered Oct 24 '14 at 13:32

1

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)

answered Oct 24 '14 at 13:32

Melad

855
12
14

score 1 · Answer 3 · edited Aug 09 '12 at 23:21

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)

SSRS 2008 R2 Carriage Return Problem

3 Answers3

Linked