Is it possible to create a row that spans all columns of a Matrix in SSRS 2008?

Question

Is it possible to have add a row to an SSRS 2008 Matrix that spans all of the matrix's columns?

This crude diagram shows roughly what I'm looking for:

The basic idea is that each line item is a person, and each column is a field in a form for that person. The fields themselves are dynamic (and implemented as column groups on the matrix). Additional column groups are included to append non-dynamic form fields, like the time the record was entered and who entered it. Under each person's record is a comments field, which should span all of the form fields above it.

At the moment I have the matrix embedded in another tablix, with the name and fields in the matrix and the comments in the parent tablix. This works for the data (each instance of the matrix ends up being one row), but the header repeats too often (once per person) because it's attached to the matrix. The only thought I have as to how to fix this is to create another matrix in the parent tablix with the same grouping and use it to display the headers... But this will require quite a lot of manual synchronization to keep the two matrices the same.

Edit: The key problem here is making the second row span multiple column groups.

Looper

Answer 1

Here's what you can do:

1. Select the column grouping you want to add above and right click and select Add Group... Parent Group...
2. Group the column by something that will not aggregate the data. You'll have to select a field from your dataset to group by so that it creates an overlapping column grouping.
3. Check the Add group header box

You should now see something similar to this:

Now you just need to move the Value1 field and it's header over under the new column to the right beyond the matrix dividers. Once that's done, simply delete the ungrouped column where you just copied Value1 from and be sure to select Delete columns only checkbox.

Your finished product should look like this:

Taut

I believe the question was 'how do i get a header for each row group', rather than a summary stat over all rows. – Kaisership

"Now you just need to move the Value1 field and it's header over under the new column to the right beyond the matrix dividers." not clear, could you add some pics – Washer

Answer 2

Try adding in you column group "header" with a grouping expression of (1=1). Then a detail field will need to be defined. If you define the other group with correct data then the "header" will stretch across all details columns. You may need to merger depending on other options.

Crew

Answer 3

It is possible - And the above answers are partial answers leaving out one key step: Merging the cells of the child row.

First, right click on your grouped row, then select Insert Row -> Inside Group - Below and you will get two rows with the cells aligning on the columns

Second, ctrl click all the cells in your new row (ie row without the data) then right click on one of the highlighted cells, and then select Merge Cells.

Now you have the table you like. To add a value to the new row: first right click your new (multi column) cell,select Create Placeholder, and then add the dataset item you desire to the placeholder.

Menell

Thank you - this is the best solution. Worked like charm! – Undemonstrative

Answer 4

[Edit]

After several attempts, I'm going to say this isn't possible in SSRS. The best I could come up with is a group footer that spans columns 1,2, and 3, but not the User column.

[Original Response]

I recently did something similar to this.

First, what tool are you using to create SSRS reports? (I used SQL Server Business Intelligence Development Studio)

• You'll want to create a row group (grouped on Person)

• Append a row to your table in "Design" view (Right click, "Insert Row --> Inside Group - Below").

• Add an expression to the row that pulls the value for your "Comment" column (=Fields!Comment.Value).

Let me know if that helps...

Hester

I'm using Visual Studio 2008. The matrix already has a row group on the person field... Did you mean to add another one somehow? When I add a new row, it displays in the designer with one cell for each column group. I'd like it to have one cell for all of them. – Looper

Highlight all of the cells in the row, right click, and click "Merge". – Hester

Unfortunately, the merge cells option doesn't appear to be available when I have cells from multiple column groups selected. I need a cell that spans multiple column groups... Which seems to be the main problem here. – Looper

After several attempts, I'm tossing this one up. Now that I've fully understood the issue, I too am unable to create a group footer that spans the both the grouped column, and detail columns. – Hester

Answer 5

The easiest way to do this is to create a Tablix with only one column, and your row grouping. Then, you create two rows inside this group. In the first row inside the group, you insert a Matrix, wich you can then subgroup as you prefer.

I just had a similar problem, and this was my solution.

I banged my head against the wall for a lot of time, until i realized the solution to my problem wasn't "making a cell span multiple column groups", but "making a cell split into multiple column groups".

Mansized

Triggered a thought process which should hopefully allow me to solve my issue which was how to show a sub-report after each row group, without having it embedded in a cell. Having a shell tablix to control the grouping and then using two sub-reports may work. – Faddish

Answer 6

You can accomplish the goal by using a subreport for each person. The subreport will receive the employee id and create the hierarchy for you. Make sure your subreport column widths match the widths of the parent report.

Eckart