Keep group rows then begin next group

Question

Imagine 3 tables:

Tickets
Comments
Attachments

The relationship is one ticket has 0 to many comments Also one ticket can have multiple (0 to many) attachments

Example:

Ticket 1
  1 Comment
  2 Comment
  3 Comment
  1 Attachment
Ticket 2
  1 Comment
Ticket 3
  1 Attachment

I am using reporting services and I created a table layout report that has 3 groups. The group for the ticket, a group for the comments and a group for the attachments The issue is as soon as there is an attachment it is displayed after every comment. For instance looking at Ticket 1 from the sample above the report would end up looking like this:

Ticket 1
  Comment 1
  Attachment 1
  Comment 2
  Attachment 1
  Comment 3
  Attachment 1

But I wanted it to display:

Ticket 1
  Comment 1
  Comment 2
  Comment 3
  Attachment 1

That is keep the entire group rows together then begin the next group. RS simply displays all the groups and goes back to the original group. I tried several things like creatign a group and merging all the cells and placing a table within it and placed the comments field in this table. Then I added another group and inserted a table for the attachments, etc. When I ran the report its the same issue. How do I display all the data for a group then allow it to go to the next group?

Answer 1

I think the nature of the data means that you can't do this easily in one object, since comments and attachments don't seem directly related there's no natural hierarchy there.

I would create a table with a ticket group and three group header rows, one with the ticket details and two with a subreport embedded in each row; one for comments and one for attachments. Each subreport will take the ticket as the parameter (and any other parent report parameters) and display the comments and attachments associated with that ticket respectively.

It's not the most elegant solution, but it should be easy enough to get going and would definitely get around the problem.