Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Display all column values , one per line in a textbox in the SSRS report Expand / Collapse
Author
Message
Posted Monday, February 17, 2014 4:37 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 6:03 AM
Points: 588, Visits: 1,061
Hi
I have a scenario like this:
For a particular audit code, there will be more than one audit description.
I have to show the non-duplicate audit description values in my SSRS report without comma sepaeration
I.e. in case of several entries, list all, one per line in a textbox in the report.
How to achieve this?

Sample Data:

select 'Draft' as auditedAreaDescription, 'A23' as auditId
union select 'Activity1' as auditedAreaDescription, 'A24' as auditId
union select 'Activity2' as auditedAreaDescription, 'A24' as auditId
union select 'Demo' as auditedAreaDescription, 'A25' as auditId
union select 'New Activity ' as auditedAreaDescription, 'A25' as auditId
union select 'New Activity 86932' as auditedAreaDescription, 'A25' as auditId
union select 'New Activity 86933' as auditedAreaDescription, 'A25' as auditId
union select 'New Activity 86934' as auditedAreaDescription, 'A25' as auditId
union select 'New Activity_26' as auditedAreaDescription, 'A26' as auditId


Post #1542041
Posted Monday, February 17, 2014 6:56 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:29 AM
Points: 368, Visits: 1,232
Is the attached the kind of thing your looking for?



  Post Attachments 
Untitled.jpg (14 views, 24.67 KB)
Post #1542093
Posted Monday, February 17, 2014 10:36 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 6:03 AM
Points: 588, Visits: 1,061
Yes michael.higgins, That is the one I am looking for.

But one small thing, I don't need the separation line between the audit descriptions for a particular audit.

i.e. for audit Id, A24, the audit descriptions Activity1 and Activity2 should be in a single text box without the separation line in between them.
Post #1542366
Posted Monday, February 17, 2014 11:06 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:56 AM
Points: 632, Visits: 1,152

WITH C
AS (
select 'Draft' as auditedAreaDescription, 'A23' as auditId
union select 'Activity1' as auditedAreaDescription, 'A24' as auditId
union select 'Activity2' as auditedAreaDescription, 'A24' as auditId
union select 'Demo' as auditedAreaDescription, 'A25' as auditId
union select 'New Activity ' as auditedAreaDescription, 'A25' as auditId
union select 'New Activity 86932' as auditedAreaDescription, 'A25' as auditId
union select 'New Activity 86933' as auditedAreaDescription, 'A25' as auditId
union select 'New Activity 86934' as auditedAreaDescription, 'A25' as auditId
union select 'New Activity_26' as auditedAreaDescription, 'A26' as auditId
)

SELECT auditid,

(
select auditedAreaDescription + ' '
from c cc
where c.auditid = cc.auditid
for XML path ('')
) as auditedAreaDescription

from c

Hope this helps
Post #1542374
Posted Tuesday, February 18, 2014 1:59 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:29 AM
Points: 368, Visits: 1,232
You just need to put a tablix on the report pointing to your data set, create a group on the ID and then another child group with the audit description. From there to remove the borders is just a formatting thing.
Post #1542418
Posted Tuesday, March 11, 2014 3:45 AM This worked for the OP Answer marked as solution


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 6:03 AM
Points: 588, Visits: 1,061
I achieved the answer using this way:

SELECT
isnull((
select stuff((select ','+ convert(varchar(1000),auditedAreaDescription) from Local_AuditedAreas LA
inner join Local_Audits LU ON LU.auditIncId = LA.auditIncId and LU.auditSqlId = LA.auditSqlId AND LU.isDeleted = 0x0 AND LA.isDeleted = 0x0
for xml path('')) , 1,1,'')
),' ') as auditedAreaDescription


Post #1549615
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse