How do I aggregate more than one result into a SSRS list report

Question

I am creating a SSRS list report based on this SQL:

select name, job_id, job_title from HR


name        job_id      job_title   
---------   ---------   ---------   
Mike Jones  123         Manager      
Mike Jones  775         Analyst 
Patty Bea   562         Director
Patty Bea   964         Deputy CFO

After creating the design and previewing the report, I get 4 pages based on my SQL results.

When a name has more than one jod_id and job title, I would like to list both on one preview page of the SSRS report.

Answer 1

Your preview looks like your desired result - 'I would like to list both on one preview page ' - rather than the 4 page current result.

If you haven't done it - you'll want to only GROUP on the name and use a LOOKUPSET to get all the values associated (and use a JOIN to combine them into a single string):

=Join(LookupSet(Fields!name.Value, Fields!name.Value, Fields!job_id.Value, "DataSet1"), ", ")

The LookupSet looks up all the results that match the name field (argument 1) to the name field of the dataset (argument 2) and gets the job_id field (argument 3) from DataSet1 (argument 4).

For the job_title field, do the same but change the job_id field to job_title.