交叉透视表

更新时间：2023-11-13

例如我们要交叉分析不同「地区」和「产品类别」的销售额情况，如下配置即可：

基于此我们还可以做非常复杂的交叉透视分析，如不同「地区」「省份」按照「产品类别」和「产品子类别」来交叉展示对应的「成本」和「价格」:

自定义数据格式化

普通表格和交叉透视表支持自定义数据格式化，可以指定具体维度值对应的单元格的格式化。举例来说，我们可以指定地区为东北时的格式化为百分比（*100%），配置项和效果如下图所示：

在度量字段上右键->数据格式化->自定义数据格式化。

点选自定义数据格式化之后，会弹出配置弹窗，支持根据 1 个维度和 2 个维度的值设置具体的格式化方式。

1 个维度

当类型选择 1 个维度时，可以单选 1 个生效维度，以及对应的数据格式化。这里我们生效维度选择省份，添加两条数据格式化，第一条选择吉林和广东，并选择对应的格式化为百分比（*100%），第二条选择辽宁，对应的格式化选择为字节转MB/GB。配置和效果如下图所示：

2 个维度

当类型选择 2 个维度时，可以选择两个生效维度，并针对这两个维度交叉出的区域分别设置不同的数据格式化。详情请参考下面这个案例。

我们选择维度1为省份，维度值选择吉林和黑龙江，维度2为类别，维度值为办公用品和家具。
此时新增一条数据格式化，格式化区域的可选选项就会出现吉林和黑龙江与办公用品和家具两两交叉出来的四个选项：吉林-办公用品、吉林-家具、黑龙江-办公用品，和黑龙江-家具。
第一条数据格式化的格式化区域勾选吉林-办公用品和吉林-家具，数据格式化类型选择百分比（*100%）。
再新增一条数据格式化，格式化区域勾选黑龙江-办公用品和吉林-家具，数据格式化类型选择字节转MB/GB。
点击确定，配置和效果如下图所示：

合计

交叉透视表支持开启合计，可以分别开启行上和列上的合计，如下：

自定义合计

在一些情况下，可能合计并不是简单的累加（如百分比类型的指标），为此 Sugar BI 中支持自定义的合计方式，如下图中的情况，我们设置价格指标不需要进行合计，并设置利润率指标的合计计算表达式为 {利润}/{成本}，Sugar BI 的表达式中支持加减乘除计算，如果想要引用指标，就使用大括号即可如：{指标名} (这个指标必须是交叉透视表中已经展示的指标)。

在开启自定义合计之后，我们还可以指定不需要合计的维度。如下图所示，我们开启了合计行，行上目前有类别和子类别两个维度，左侧表格中显示了每一个类别的小计，以及所有类别的合计。如果我们不想要小计或者合计展示出来，就可以在不需要合计的维度中进行配置。

如果我们勾选了小计（类别），那么交叉透视表中类别的小计就会不展示，只展示合计，如下图所示：

如果我们勾选了合计，那么交叉透视表中的合计就会不展示，只展示小计，如下图所示：

超级表头表尾

普通表格和交叉透视表支持在「数据」面板中配置「超级表头」和「超级表尾」，如下图所示。

超级表头和表尾都支持对内容、是否HTML、对齐方式、宽度占几列和第几行进行配置。

注：超级表头表尾开启后，冻结列功能将不会生效。

内容, 是否 HTML

超级表头表尾的内容支持直接输入文本，也支持写符合格式要求的 HTML 脚本（是否HTML选择“是”），同时也支持嵌入过滤条件、日期宏定义、用户信息参数。

HTML 脚本需要满足以下格式要求：

支持 a 标签、span 标签、div 标签和换行标签（<br />）
支持标签与纯文本节点混写，但嵌套结构在导出 excel 时将不会生效
支持在标签上通过 style 属性设置 css 样式，但只有 color、font-size 和 font-weight 属性将会在导出 Excel 时生效

HTML 案例

案例 1：在这个例子里，我们用了两个 span 标签，并分别通过 style 属性设置了不同的字体大小和颜色。

<span style="font-size: 20px; font-weight: 400;">模板1：总部--</span
><span style="font-size: 20px; font-weight: 400; color: red;">银邮电事业部培训管理室</span>

案例 2：在这个例子中，我们使用了
标签对 5 个 span 标签进行换行。

<span style="color: red; font-weight: 400;">1、银保事业部培训管理室编制情况</span>
<br />
<span style="color: red; font-weight: 400;">（1）本科室3个岗位，2个在编</span>
<br />
<span style="color: red; font-weight: 400;"
  >（2）下设17个分公司，1个科室，培训主管，定编17个，在编13个，营销专项培训主管，定编17个，在编13个</span
>
<br />
<span style="color: red; font-weight: 400;">（3）下设40个中支，128个大区的培训专员，定编168个，在编41个</span>
<br />
<span style="color: red; font-weight: 400;"
  >（4）下设总监编制339人，在编224人，部经理定编1354人，在编776人，客户经理定编11434人，在编5538人</span
>

案例 3：在这个例子中，我们使用了 a 标签展示一个超链接，这个超链接在导出 excel 时也会生效。

<a href="http://www.baidu.com" target=\"_blank">百度链接</a>

我们将案例 1,2 和 3 添加到超级表头配置中，是否 HTML 都选择是，对齐方式选择居中，宽度占几列均填 n（后续章节会详细解释），第几行分别选择第 1 行、第 2 行和第 3 行。点击确定后再点击刷新图表（注：为了提升页面性能，配置超级表头后，需要手动点击刷新图表，超级表头的更改才会展示出来），配置如下图所示：

效果如下图所示：

嵌入参数案例

用户参数：在这个例子中，我们使用了一个 span 标签，在文本中审核人的后面嵌入了用户信息参数: {{SUGAR_USER}}, 页面上将会展示出当前登录用户的邮箱。另外，也支持嵌入{{SUGAR_USER_REAL_NAME}}来展示当前登录用户的真实姓名。

<span style="font-weight: 400;">（统计区间：日/周/月/季/年 统计人： 审核人：{{SUGAR_USER}} ）</span>

日期宏：在这个例子中，使用了当前年份的日期宏定义{YEAR}，更多日期宏定义语法参考SQL 中嵌入日期宏定义

当前年份：{YEAR}

过滤条件：在这个例子中，嵌入了当前报表中 key 为 date 的日期过滤组件的值，更多嵌入语法参考SQL 中硬嵌入过滤条件

所选日期：{conditions.date}

我们将案例 1,2 和 3 添加到超级表尾的配置中，是否 HTML 案例 1 选择是，其余选否；对齐方式选择居中；宽度占几列均填 n（后续章节会详细解释），第几行分别选择倒数第 3 行、倒数第 2 行和倒数第 1 行（注：表尾这里的行数是倒数）。点击确定后再点击刷新图表（注：为了提升页面性能，配置超级表尾后，需要手动点击刷新图表，超级表尾的更改才会展示出来），配置如下图所示：

效果如下图所示：

宽度占几列

超级表头表尾配置中的每一行可以认为是一个单元格，宽度占几列表示这个单元格可以横跨几列。

支持直接填入数字，但在交叉透视表中，我们经常是无法确定一共有多少列的，所以这里也支持使用n - 1、n - 2这种 n 减去一个数字的方式动态的设置单元格宽度，其中 n 表示总列数。

进一步理解请参考以下案例，修改嵌入参数案例中的宽度占几列和倒数第几行配置项，第一个单元格宽度设置为n - 5，第二三个分别设置为 5 和 2，并将第一二个单元格都配置到倒数第 2 行，第三个为第 1 行。配置完成后点击确定并刷新图表。配置项如下图所示：

效果如下图所示，可以看到当前表格一共有 10 列，所以 n = 10，第一个表尾单元格占了 n - 5 即 10 - 5 = 5 列，第二个单元占 5 列，并且都是第倒数第 2 行。第三个单元格占了前两列，位于倒数第 1 行，由于倒数第 1 行没有其它表尾配置，所以后 8 列为空。

自动计算同环比

当表格中绑定了日期维度和度量时，此时可以开启并配置自动计算同环比。

对比日期：行或者列中拖入的日期字段
对比指标：指标中拖入的度量字段
对比方式（同环比选择）：根据日期字段的粒度，下面会显示对应的对比内容，最多显示 4 个对比字段（例如，对比日期选择日粒度，则对比内容选择显示日环比、周同比、月同比、年同比；对比日期选择月粒度，则对比内容选择显示月环比、年同比等）。具体如下表格所示。

日期时间聚合类型	数据对比选项
年	年环比
年-季度	季环比、年同比
年-月	月环比、季同比、年同比
年-周，跨年-周	周环比、年同比
年-月-日	日环比、周同比、月同比、季同比、年同比

填充内容：默认为差异百分比
- 差异百分比：展示的为保留两位小数的百分数
- 差异值：原始值与对比值的差值
填充位置：默认为选择的指标右侧
- 指标右侧：在当前指标的右侧新增一列指标，指标列名为指标-年同比、指标-日环比、指标-月同比、指标-周同比等，即为选中的对比方式，依次展示在指标右侧。

例如下方展示的为在指标（「数量」字段）右侧展示的差异百分比，此时会在「数量」字段右侧增加一列名称为「数量-日环比」的列:

指标下方：在当前指标的下方新增多行对比指标，与当前指标共用一行单元格，在当前指标的表头名下方新增一行名为日环比、周同比、月同比、年同比等，即为选中的对比方式。

填充样式：默认为正数红色，负数绿色，当使用「开启阈值对比」时，此时如果选择「正数红色，负数绿色」，那么符合条件的值显示红色，不符合条件的值显示绿色；选择「正数绿色，负数红色」，那么符合条件的值显示绿色，不符合条件的值显示红色
阈值自定义：将同环比的「差异百分比」或者「差异值」与设置的阈值按照指定的规则进行比较
阈值比较规则设置：支持>、>=、<、<=、=、!=、in、not in、正则规则设置
对比值：与同环比按照规则进行比较的值（注：与百分数对比时请使用小数形式）

自动计算同环比阈值自定义

当设置规则为>、>=、<、<=、=、!=时，此时阈值需要为一个数字类型，如果与百分数对比对比值请使用小数形式，比如：我们想将「数量-月环比」大于 2% 的值展示为红色，小于等于 2% 的值展示为绿色，此时我们可以将「阈值比较规则」设置为「>」，「对比值」设为 0.02

当设置规则为 in、not in 时，此时阈值可以设置多个值(需要用逗号进行分隔)，如果与百分数对比对比值请使用小数形式，比如：我们想将「数量-月环比」为「-9.08%、1.91%、3.42%」的值展示为红色，不为「-9.08%、1.91%、3.42%」的值展示为绿色，此时我们可以将「阈值比较规则」设置为「in」，「对比值」设为「-0.0908,0.0191,0.0342」

当设置规则为正则时，此时阈值可以设置为正则表达式，如果与百分数对比对比值请使用小数形式与正则表达式比较，比如：我们想将「数量-月环比」中「大于等于 0 小于等于 100」的值展示为红色，不为「大于等于 0 小于等于 100」的值展示为绿色，此时我们可以将「阈值比较规则」设置为「正则」，「对比值」设为「^100$|^(\d|[1-9]\d)(.\d+)*$」正则表达式

自动计算同环比和过滤条件同时使用

当日期字段选择了「日期数据聚合」时，此时「数量」会按照「年-月」日期粒度进行聚合此时我们如果添加一个时间范围为「2019-04-15 至 2019-09-15」的日期范围过滤条件时，此时为了求 2019 年 04 月的环比会向前查询 2019 年 03 月的数据，但是此时 2019 年 04 月数据是「2019-04-15 至 2019-04-30」的数据并不是 04 月份完整数据，所以此时 04 月份环比会有偏差，这样得到的 2019-04 月份环比是没有意义的。