Line Break & Null Values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Line Break & Null Values

Author

Topic

steve_c
Yak Posting Veteran

54 Posts

Posted - 2007-10-22 : 08:09:02

Hi all - I'm using Reporting Services for despatch notes. I need to have the client's address in one of the reports. This can change depending on the client. I have a config table with fields 'ADDRESS1', 'ADDRESS2', 'ADDRESS3', 'ADDRESS4', 'ADDRESS5', 'POSTCODE'

Now this is fine if the client has 5 address lines and a postcode. However, if the client only has 3 address lines, the postcode will appear 2 lines below the last address line such as this:

Smith Packagin
123 Jones Street
London

W13 4RG

Is there a way I can create a proc to generate the correct format with line breaks for use within a report?

Thanks,

Steve.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-22 : 08:17:21

In Crystal report, you can create a new section for each address and hide them if the value is null. See if you can do the same in SSRS

Madhivanan

Failing to plan is Planning to fail

steve_c
Yak Posting Veteran

54 Posts

Posted - 2007-10-22 : 08:18:25

Hi - even if I hide the textbox if the value is null, the boxes underneath won't move up to take the space :(

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-22 : 08:20:20

quote:
Originally posted by steve_c

Hi - even if I hide the textbox if the value is null, the boxes underneath won't move up to take the space :(

Yes I know. Thats why I told you to use new section and hide the entire section based on NULL value. I am not sure if this can be done in SSRS

Madhivanan

Failing to plan is Planning to fail

steve_c
Yak Posting Veteran

54 Posts

Posted - 2007-10-22 : 08:26:46

Sorry I misread your first post. As far as I can tell I can't add sections in SSRS - I am aware that you can in Crystal - I must admit that was a nice feature.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-22 : 08:43:16

I am not sure how you can do it there
But If possible, you concatenate the data and split them using comma when you display
ex



declare @t table(a1 varchar(10),a2 varchar(10),a3 varchar(10))
insert into @t
select 'a','b','c' union all
select 'a',null,'c' union all
select 'a','b',null 

select coalesce(a1,'')+coalesce(','+a2,'')+coalesce(','+a3,'') as a3 from @t

Otherwise post your question at Reporting Services Forum

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-22 : 15:02:03

create a formula that concatenates the columns together with char(10)+char(13) in your reporting tool. In that formula, only add the cr/lf if the column value is not null.

i.e., something like this (pseudo-code)

dim ret as string = ""
if (Not ADDRESS1 is null) ret = ret + ADDRESS1 + char(10) + char(13)
if (NOT ADDRESS2 is null) ret = ret + ADDRESS2 + char(10) + char(13)
.. etc ..
return ret

Then just pop that formula into your report.

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 01:38:51

quote:
Originally posted by jsmith8858

create a formula that concatenates the columns together with char(10)+char(13) in your reporting tool. In that formula, only add the cr/lf if the column value is not null.

i.e., something like this (pseudo-code)

dim ret as string = ""
if (Not ADDRESS1 is null) ret = ret + ADDRESS1 + char(10) + char(13)
if (NOT ADDRESS2 is null) ret = ret + ADDRESS2 + char(10) + char(13)
.. etc ..
return ret

Then just pop that formula into your report.

- Jeff
http://weblogs.sqlteam.com/JeffS

Well. I thought of suggesting using Formula fields and I was not sure if SSRS supports them

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources