0

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.

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
  • If I'm understanding what you want to do, it sounds like you can simply add row grouping within the SSRS report. Column group on name and email, then row group on value, I believe. – jw11432 Oct 15 '20 at 16:32
  • can u please add a screenshot of ur design, couldn't make it work :( – Eray Balkanli Oct 15 '20 at 16:41
  • Sorry, I don't have an example of this. I just know it will involve the grouping feature within SSRS. – jw11432 Oct 15 '20 at 17:18
  • What does "couldn't make it work" mean - error message, wrong result, nothing happens? Your issue is with design, not code and therefore not really appropriate for SO - there is no code to analyze. Grouping on report should provide desired output. – June7 Oct 15 '20 at 19:00

1 Answers1

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

enter image description here

The final output looks like this..

enter image description here

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

enter image description here

The final output like this

enter image description here

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35