CODEWITHSUNDEEP
reporting-servicesreportingservices-2005
marc_s
marc_s

Reputation: 755541

SQL Server Reporting Services - suppress empty line in free-form report

I have a free-form report based on a List control in SQL Server Reporting Services, basically displaying an address for a company:

Company Name
Address 1
Address 2
ZipCode City

However, some of my company records don't have a value for the Address2 field - in which case, I'd like to completely remove the line (i.e. Textbox) which contains the non-existant Address2 data - so it should look something like:

Company Name
Address 1
ZipCode City

I can set the Hidden attribute of the Textbox on the report just fine - however that only results in the textbox being hidden - and a blank line shows up where Address2 used to be.... I'd like to suppress that blank line. HOW?!?!

I'm not inside a Tablix or anything like that - it's just free-form textboxes on a report. Any chance to get this to do what I'm trying to do without a huge effort?

Upvotes: 1

Views: 5011

Answers (3)

csname1910
csname1910

Reputation: 1235

Put all fields in one line and check before each field if you need a line feed.

For example:

[Name1]«Expr»[Name2]«Expr»[Name3]
[Street]
[City]

With Expressions:

=Iif(Fields!Name2.Value="",Nothing,vbCRLF)
=Iif(Fields!Name3.Value="",Nothing,vbCRLF)

Upvotes: 8

CEHP
CEHP

Reputation: 21

Just put this expression in my textbox. (Allows you to include all the other address field before and after it in the same textbox.) Super easy!

=iif(Fields!Street2.Value is nothing, Fields!Street1.Value, Fields!Street1.Value + vbCRLF + Fields!Street2.Value)

Upvotes: 0

Ian Preston
Ian Preston

Reputation: 39606

Well, it can be done, kind of, depending on what you need. Is it a huge effort? Up to you to decide:

First I created a dataset.

select company = 'A'
, add1 = 'Add 1 A'
, add2 = 'Add 2 A'
, zip = '90210'
union all
select company = 'B'
, add1 = 'Add 1 B'
, add2 = null
, add3 = '12345'

Next, create a List and group it by Company.

enter image description here

In this List add a Table with rows for each field. In each of these rows you add a Rectangle, then a Textbox with the field details. Since we have a Table we can hide rows based on an expression, and because there is a Rectangle in each row we can move the Texbox around as required:

enter image description here

In the second address row, set the row visibility based on an expression:

=IIf(IsNothing(Fields!add2.Value), true, false)

Final result:

enter image description here

You can move the fields around as required. It's not perfect and a bit fiddly, but at least this will give you a bit more flexibility and maybe an idea on how you might like to proceed if it's not 100% suitable.

Upvotes: 3

Related Questions