观远 BI

复杂报表交替插入数据案例分享

创建于 2022-11-01 / 最近更新于 2023-11-02 / 3552
字体: [默认] [大] [更大]

场景

在案例复杂报表实现多层表头横向扩展的场景里,只写了一列公式,就实现了多个字段横向扩展的效果。但是这些数值字段只能采用统一的计算方式和数值格式,如果遇到以下任何一种情况,就必须为每个数值字段单独设置一列公式,然后设置交替插入数据。

  1. 要扩展的多个数值字段的内容来源于不同的视图

  2. 要扩展的多个数值字段需要用不同的计算方式(例如求和、平均值、最大值,或者格间计算);

  3. 多个数值字段要设置不同的格式(例如金额、百分比)

本文基于上个案例同一个场景,需要横向扩展出每个月每个城市线的零售流水、目标,以及完成率。最终效果如下图。

image.png

实现方法

主要通过在复杂报表里使用公式并配合设置“动态属性”来实现横向或者纵向扩展时交替插入数据。

image.png

实现步骤

数据准备

新建复杂报表,新建视图,把需要呈现的必要数据先计算出来。此案例里横线扩展部分数据“流水”来源于视图“流水”里的“累计流水”, “目标”来源于另一个视图“目标”里的“累计目标”,“完成率”计算方式为累计流水/累计目标,不能直接在视图里计算得到。这里筛选了日期范围“2019年”和城市线“一线”和“二线”。

image.png

模板编辑

A. 表头部分。进入模板编辑界面,该例子里横向扩展从 J 列开始,表头部分公式使用方法参考 复杂报表实现多层表头横向扩展。因为有三个数值字段,两个城市线,所以第一层表头“月份”需要合并6列,第二层“城市线”需要合并3列,第三层在公式里列出三个字段要显示的文本表头。三个公式都放在同一列,动态属性不需要设置,后面新增两列,表头单元格留空。(注:下图列宽非最终效果)

image.png

B. 数值部分

1) 流水:

公式:=G_LOOKUPEXP(9,流水,月|城市线,流水,累计流水,大区:A{r},月:J1,城市线:J2)

动态属性:勾选“间隔扩展数值列”,“视图名”和“间隔扩展字段”要和公式里派生表视图名和派生字段保持一致;“间隔”填3(包含当前列);勾选“不额外添加行列数”,如果不勾选,则会在这一列扩展结束后才会开始扩展后面字段,会插入大量空白列。

图片.png

2) 目标:

公式:=G_LOOKUPEXP(9,流水,月|城市线,目标,累计目标,大区:A{r},月:J1,城市线:J2)

动态属性:同“流水”

3) 完成率:

公式:=G_CELLCALCEXP(if(iserror({-2}{r}/{-1}{r}),"--",{-2}{r}/{-1}{r}))

正常情况下分母为0或空值会导致除法报错,用 iserror 函数判断报错,用 if 函数把报错转换为“--”; {-2}{r} 代表当前使用公式的单元格所在行向前偏移2列,对应“流水”, {-1}{r} 代表当前使用公式的单元格所在行向前偏移1列,对应“目标”。格间计算具体表示方法请参考 复杂报表本地编辑模式公式说明

动态属性:跟随A列维度字段“区域”设置纵向扩展,勾选“复制单元格样式”;勾选“间隔扩展数值列”;因为格间计算公式里没有用到派生表,这里需要指定用来生成派生表的视图名和字段,跟上面横向扩展表头和前面G_LOOKUPEXP公式里使用同一个派生表即可;“间隔”填3(包含当前列);作为横向扩展最后一列公式,不能勾选“不额外添加行列数”,勾选后可能会导致报表格式不生效或者直接报错(后续待优化)。

image.png

C. 列总计

横向扩展列总计一般使用G_GRANDTOTAL,但是该公式默认对数据求和,不能设置其他计算方式(例如平均值、最大值),本案例里“完成率”是格间计算出来的百分比,不能直接求和;另外,G_GRANDTOTAL会根据派生表自动横向扩展,且不支持设置间隔列扩展(交替插入计算),所以也不能对“流水”和“目标”使用G_GRANDTOTAL,单独对“完成率”使用格间计算公式(计算结果会被覆盖掉)。

下图为使用 =G_GRANDTOTAL(流水,月|城市线,3,J4:K4) 的计算效果。

image.png

推荐方法:

流水总计:=G_LOOKUPEXP(9,流水,月|城市线,流水,累计流水,月:J1,城市线:J2) 跟上面“流水”列计算方式相同,只是去掉了查询字段“大区”。两个公式单元格动态属性完全一致。

目标总计:=G_LOOKUPEXP(9,流水,月|城市线,目标,累计目标,月:J1,城市线:J2) 动态属性同上。

完成率总计:=G_CELLCALCEXP(if(iserror({-2}{r}/{-1}{r}),"--",{-2}{r}/{-1}{r})) 直接复制上面格间计算公式即可。

动态属性:只需要设置横向扩展;间隔写入”设置为2(不包含当前列); “不额外添加行列数”可勾选,也可不勾选(因为是本模板里最后一个扩展公式)。设置如下图。

image.png

预览保存

为每一列单独设置数值格式和样式,点击“保存并继续”来进入下一步预览效果,然后再点击“模板编辑”回到模板上调整公式或者样式,直至达到需要的效果。最后点击“完成”按钮保存即可。


3 人点赞过