Invert header rows of nested column groups in matrix control

Question

In a matrix control, I group columns by 'Size', and then by 'Color'. The resulting table looks like this:

I need to invert the header rows, so the table looks like this:

The values from the child group should display above the corresponding value from the parent group.

Answer 1

Maybe by making the Parent group a combined grouping of both Size and Color, however only display Colors, then Group on Size for the Child/Subgroup.

Update:

Ok, so I created a small dataset, I'm not sure the dataset is anything like you are getting back, but maybe it can spur some other ideas on how you can manipulate the data in SQL to help get what you want in the report.

First I just created a bunch of SELECT ... UNION ALL statements, but after some toying, I still couldn't get anything close to the required visual output/grouping. So here is what I used:

with CTE (Color, Size, CSGroup, Amt) As (
select 'Red' As color, 'Small' as size, 'RedSmall' as CSGroup, 1 as Amt union all
select 'Red' As color, 'Small' as size, 'RedSmall' as CSGroup, 1 as Amt union all
select 'Red' As color, 'Medium' as size,'RedMedium' as CSGroup, 1 as Amt union all
select 'Red' As color, 'Medium' as size, 'RedMedium' as CSGroup, 1 as Amt union all
select 'Red' As color, 'Medium' as size, 'RedMedium' as CSGroup, 1 as Amt union all
select 'Red' As color, 'Small' as size, 'RedSmall' as CSGroup, 1 as Amt union all

select 'Yellow' As color, 'Small' as size, 'YellowSmall' as CSGroup ,1 as Amt union all
select 'Yellow' As color, 'Small' as size, 'YellowSmall' as CSGroup ,1 as Amt union all
select 'Yellow' As color, 'Large' as size, 'YellowLarge' as CSGroup ,1 as Amt union all
select 'Yellow' As color, 'Large' as size, 'YellowLarge' as CSGroup ,1 as Amt union all
select 'Yellow' As color, 'Large' as size, 'YellowLarge' as CSGroup ,1 as Amt union all
select 'Yellow' As color, 'Medium' as size, 'YellowMedium' as CSGroup ,1 as Amt union all
select 'Yellow' As color, 'Medium' as size, 'YellowMedium' as CSGroup ,1 as Amt union all

select 'Blue' As color, 'Medium' as size, 'BlueMedium' as CSGroup, 1 as Amt union all
select 'Blue' As color, 'Medium' as size, 'BlueMedium' as CSGroup, 1 as Amt union all
select 'Blue' As color, 'Medium' as size, 'BlueMedium' as CSGroup, 1 as Amt union all
select 'Blue' As color, 'Small' as size, 'BlueSmall' as CSGroup, 1 as Amt union all
select 'Blue' As color, 'Large' as size, 'BlueLarge' as CSGroup, 1 as Amt union all
select 'Blue' As color, 'Large' as size, 'BlueLarge' as CSGroup, 1 as Amt union all

select 'Green' As color, 'Medium' as size, 'GreenMedium' as CSGroup, 1 as Amt union all
select 'Green' As color, 'Medium' as size, 'GreenMedium' as CSGroup, 1 as Amt union all
select 'Green' As color, 'Large' as size, 'GreenLarge' as CSGroup, 1 as Amt union all
select 'Green' As color, 'Large' as size, 'GreenLarge' as CSGroup, 1 as Amt union all
select 'Green' As color, 'Large' as size, 'GreenLarge' as CSGroup, 1 as Amt union all
select 'Green' As color, 'Small' as size, 'GreenSmall' as CSGroup, 1 as Amt union all
select 'Green' As color, 'Small' as size, 'GreenSmall' as CSGroup, 1 as Amt)
Select Color, Size, SUM(Amt) As Amount From CTE group by Color, Size

You can ignore the CSGroup I didn't end up using it.

So with that, it about gave me the "look" of what you need in the Dataset.

I drug out a Matrix and grouped on Size & Color (=Fields!size.Value & Fields!color.Value)

I Then inserted a group, and grouped on Size (=Fields!size.Value)

In the "top" column grouping I have =Fields!color.Value

In the second column grouping I have =First(Fields!Size.Value)

In the Data textbox I have =Sum(Fields!Amount.Value)

Then, Right-Click on the second Column Grouping and Check the Box "Hide Duplicates". I then selected Dataset1 in the drop down.

The only thing I was Unable to do was get the Size centered since I was unable to Merge the textboxes.