May 3, 2011 at 5:19 pm
I have the column address above. Now i want to change it so that i can display address1 ,address 2, ..city in seperate lines. I can remove the coalesce and put each them in separate rows but i don't want to do that. I want
them concatnated in SQL and split in SSRS report.Actually in my SSRS
report i have customerId,CustomerName,
ContactName, address in separate rows. I am sure this is not a SSRS question. This is something that needs in the SQL statement.
I tried to concatenate CHAR(13)+CHAR(10) to address1, address2, city but in SSRS they display in the same line
Select c.customerId,c.CustomerName,c.CustomerNumber, a.ContactName, coalesce(a.Address1,'')+ CHAR(13) + ' ' + coalesce(a.Address2, '')+ CHAR(13) + ', ' + coalesce(a.city, '')+ CHAR(13) + ', ' + coalesce(a.StateTerritory, '')+ CHAR(13) + ', ' + coalesce(a.PostalCode, '')+ CHAR(13) as Address
from AX.Customer c inner join AX.CustomerAddress a on c.CustomerID = a.CustomerID
May 4, 2011 at 8:42 am
It may be that SSRS has a property that allows it to recognise CR/LF, which would be the easiest way.
Other than that, it's a case of 'messily' combining results then grouping in the report writer
Select c.customerId,c.CustomerName,c.CustomerNumber
, 1 as addressline
, coalesce(a.Address1,'')
from #customer as c
inner join #customeraddress as a
on c.customerid = a.customerid
union all
Select c.customerId,c.CustomerName,c.CustomerNumber
, 2 as addressline
, coalesce(a.Address2,'')
from #customer as c
inner join #customeraddress as a
on c.customerid = a.customerid
order by customerid, addressline
and so on for all other fields.
Might get you started
HTH
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
May 4, 2011 at 8:48 am
Hi, have you checked that SSRS is actually removing these characters and it's not just something like you're using a table without autogrow set to false?
Maybe whack it into a large area text box and see what it does?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply