SSRS - how to list separate values of a group combined into a merged row

Question

In Microsoft Report Builder, I have a report designed like below:

Name    Email    Value
[Name], [Email], [Value]

where the data source is like: select name,email,value from mytable

The output of the report is like:

Name   Email           Value
Chris, chris@xxx.com   1
Chris, chris@xxx.com   2
Chris, chris@xxx.com   3
Alex,  alex@xxx.com    1
Alex,  alex@xxx.com    2
Alex,  alex@xxx.com    4
Alex,  alex@xxx.com    7
John,  john@xxx.com    3

What I need to do is to group the table by name and email and list the values under the group as a separate row, like:

        Name   Email
Row1    Chris  chris@xxx.com
Row2     1
Row2     2
Row2     3
Row3     Alex   alex@xxx.com
Row4     1
Row4     2
Row4     4
Row4     7
Row5    John   john@xxx.com
Row6     3

Row2 is merge of 3 rows, Row4 is merge of 4 rows.

What is the easiest way to manage this? Any help would be appreciated.

Answer 1

Here's s brief guide to doing this.

1. Create a new table.
2. Drag the Value field to the first column in the table
3. In the row group panel (below the main design panel) you will have a 'details' row.
4. Right click the row and choose "Add Group ==> Parent Group"
5. In the parent group options, choose Email as the parent field and select 'Add group header'
6. In the newly created Email cell, right-click and choose "Insert Column ==> Inside group-left"
7. In the new column click the empty cell and choose Name from the drop down
8. Finally, Delete the extra columns at the end of the table

Your design should look something like this

The final output looks like this..

If you want the Value directly under the name then ...

1. right-click the value cell and choose insert column-right
2. click the cell above Value and choose Name
3. click the cell in the end column and choose email
4. Set the column headers as you want (just type the column names)
5. delete the first two columns (but not the groups if you are prompted)

The design should look like this

The final output like this