February 17, 2014 at 4:37 am
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
February 17, 2014 at 6:56 am
Is the attached the kind of thing your looking for?
February 17, 2014 at 10:36 pm
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.
February 17, 2014 at 11:06 pm
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
February 18, 2014 at 1:59 am
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.
March 11, 2014 at 3:45 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply