反转矩阵控件中嵌套列组的标题行

Question

在矩阵控件中，我按“大小”对列进行分组，然后按“颜色”对列进行分组。生成的表格如下所示：

我需要反转标题行，因此表格如下所示：

子组中的值应显示在父组中相应值的上方。

Answer 1

也许可以通过将“父组”设置为“大小”和“颜色”的组合分组，但仅显示颜色，然后按子组/子组的“大小”进行分组。

更新：

好的，所以我创建了一个小数据集，我不确定该数据集是否与您返回的数据集相同，但也许它可以激发一些关于如何在 SQL 中操作数据以帮助获得您想要的内容的其他想法报告。

首先，我刚刚创建了一堆 SELECT ... UNION ALL 语句，但经过一番尝试后，我仍然无法获得接近所需视觉输出/分组的任何内容。所以这就是我使用的：

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

你可以忽略CSGroup我最终没有使用它。

因此，它给了我你的“外观”需要在数据集中。

我制定了一个矩阵并按大小和分组进行分组。 Color (=Fields!size.Value & Fields!color.Value)

I 然后插入一个组，并按 Size (=Fields!size.Value) 进行

分组“顶部”列分组我有 =Fields!color.Value

在第二列分组中我有 =First(Fields!Size.Value)

在数据文本框中我有 < code>=Sum(Fields!Amount.Value)

然后，右键单击第二个列分组并选中“隐藏重复项”框。然后我在下拉列表中选择了Dataset1。

我唯一无法做的就是使大小居中，因为我无法合并文本框。