0

wizards, experts, simple workingmen like me and all other well-meaning people I might have forgotten: I have a problem and need your help......

I have a report 'TRAINERMEETING' in which I count the number of people (people_ID) that have attended a meeting:

TRAINER -|-MEETING -|- NUMBER OF ATTENDANTS

[TrainerID] -|- [MeetingID] -|- =countdistinct(peopleID)

Johnny -|- Coolness-training -|- 5

The details (individual peopleID) are available in the report but not visible. Everything is grouped on TRAINER, then MEETING

What I would like to do is create a list of commaseparated values that contains the 5 individual PeopleID that are counted in '=countdistinct(peopleID)' I want to pass this list to a parameter @PeopleID in another report (LISTALLPEOPLEID) that contains all peopleID's. That parameter will be used to filter the report (LISTALLPEOPLEID). I tried '=join(PeopleID,",")' but that appeared to be to simple ;)

Who knows whether/how it is possible to create a list of items that make up a aggregate? Or otherwise simply use the peopleID's to filter the PeopleID's report? I am very much obliged,

Henro

EDIT:

Thanks to a tip I got (do not remember who, shame in me) I solved it using XML:

SUBSTRING( replace( replace( (SELECT me.meeting FROM trainer as tr1
WHERE tr.trainer_id = tr1.trainer_id ORDER BY me.meeting FOR XML PATH('')) ,'','') ,'','
- ') ,5,2000) AS Meetings

Henrov
  • 1,610
  • 1
  • 24
  • 52

1 Answers1

0

Thanks to a tip I got (do not remember who or where, shame on me) I solved it using XML:

SUBSTRING( replace( replace( (SELECT me.meeting FROM trainer as tr1 WHERE tr.trainer_id = tr1.trainer_id ORDER BY me.meeting FOR XML PATH('')) ,'','') ,'',' - ') ,5,2000) AS Meetings

Henrov
  • 1,610
  • 1
  • 24
  • 52