Removing blank address lines

Question

Removing blank address lines

paul.farnell

SSC Eights!

Points: 810
More actions
June 18, 2015 at 10:38 am

#318314

In report builder I am building up an invoice with an address with 6 lines plus a postcode. They are showing one field below the other on the report. However sometimes as you would expect an address line is empty.
How could I hide/ignore this field whilst at the same time shuffling the other lines up the page.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 1

paul.farnell (6/18/2015)
In report builder I am building up an invoice with an address with 6 lines plus a postcode. They are showing one field below the other on the report. However sometimes as you would expect an address line is empty.
How could I hide/ignore this field whilst at the same time shuffling the other lines up the page.

In the "Row Visibility" property: isNothing(Fields!<addressLineX>.Value)

Don Simpson

I'm not sure about Heisenberg.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You could also correct the source if at all possible,

Here's an example using AdventureWorks2012

SELECT a.AddressID,

a.AddressLine1,

a.AddressLine2,

a.City,

sp.StateProvinceCode,

sp.CountryRegionCode,

sp.IsOnlyStateProvinceFlag,

a.PostalCode,

a.AddressLine1 + CHAR( 10)

+ ISNULL(a.AddressLine2 + CHAR( 10), '')

+ a.City + ', '

+ sp.StateProvinceCode

+ CASE WHEN sp.IsOnlyStateProvinceFlag = 0 THEN ', ' + sp.CountryRegionCode ELSE '' END + CHAR(10)

+ a.PostalCode AS FullAddress

FROM AdventureWorks2012.Person.Address a

JOIN AdventureWorks2012.Person.StateProvince sp ON a.StateProvinceID = sp.StateProvinceID;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 3

Luis Cazares (6/18/2015)
You could also correct the source if at all possible,
Here's an example using AdventureWorks2012
SELECT a.AddressID,
a.AddressLine1,
a.AddressLine2,
a.City,
sp.StateProvinceCode,
sp.CountryRegionCode,
sp.IsOnlyStateProvinceFlag,
a.PostalCode,
a.AddressLine1 + CHAR( 10)
+ ISNULL(a.AddressLine2 + CHAR( 10), '')
+ a.City + ', '
+ sp.StateProvinceCode
+ CASE WHEN sp.IsOnlyStateProvinceFlag = 0 THEN ', ' + sp.CountryRegionCode ELSE '' END + CHAR(10)
+ a.PostalCode AS FullAddress
FROM AdventureWorks2012.Person.Address a
JOIN AdventureWorks2012.Person.StateProvince sp ON a.StateProvinceID = sp.StateProvinceID;

Yes, you could I guess, but I really don't like handling presentation logic in SQL when you have a tool like SSRS or Crystal. Maybe if you were pushing data out to a text based Perl report or something like that.

Don Simpson

I'm not sure about Heisenberg.