分类： 敏捷开发
2020-09-14 14:59:46
我们知道，报表呈现的数据常常并不是直接从数据库（源）取出来的数据，而还要进行一些运算，报表工具通常也会提供一定的运算能力（如过滤、分组等）以应对这种需求。但是，情况复杂时，报表数据集上的运算可能要多个步骤才能完成，这时候就要考查报表工具对过程式计算的支持程度了。
统计要求
列出指定时段的大客户。所谓大客户，定义为销售额占前一半的客户，也就是把客户销售额从大到小排序后，前面若干个客户的合计销售额构成总销售的一半，这些客户被称为大客户。
报表式样
数据结构
[销售记录表]
特点分析：
这个报表式样很简单，主要麻烦之处在于需要从原始数据集计算出大客户后再呈现，而这个计算并不是简单一步能完成的。
我们的重点是考查报表工具的过程计算能力，所以假定不使用 SQL 或数据源的计算能力来完成。
制作过程:
1、 配置并连接数据源。
2、 设置数据集
润乾报表提供了一个独立的计算引擎，可以通过内置的脚本实现对数据的计算并将结果返回给报表数据集，在报表中新增数据集，数据集类型使用脚本数据集，脚本如下：
2.1 A1 格：单元格做了数据源的连接，然后 A6 单元格在执行完毕后关闭连接。
2.2 A2：从客户销售表中取数，此处根据客户名称做了汇总，并且按照销售额降序排序。
2.3 A3：对销售额进行求和操作并处以 2，取出总金额的一半，用于判断大客户。B3 设置初始值为 0，用于做销售额累加操作
2.4 A4：对销售额进行累加，取出累加金额大于 A3 中对应的 A2 的序号
2.5 A5：根据序号取 A2 中对应的值，并做为结果集返回给报表
3、 设计报表模板
脚本数据集中返回的数据就是大客户的数据信息，所以制作报表就相当简单, 设置如下：：
3.1、 A2、B2 单元格直接取数据集中的数据
3.2、 B3、B4 单元格直接用数据集汇总函数，对数据集计数取出大客户数量，对销售额用 avg 函数取出平均销售额。
3.3、 设置边框、金额显示格式。
运行结果：
完成后点评
1、 0.5 小时，使用脚本数据集中内置的语法，几个单元格的处理就能快速实现需求。
2、 可以使用脚本数据集，内置丰富语法规则，能快速处理各种复杂的数据统计要求。
3、 报表设计简单，并且不需要辅助单元格，报表计算效率高。
制作过程:
1、 配置并连接数据源。
2、 设置数据集
增加数据库查询，SQL 为： SELECT 客户,sum(销售额) 销售额 FROM DEMO. 客户销售表 group by 客户 order by 销售额 desc
3、 设计报表模板
3.1 B2 单元格取总销售额的一半，用于判断大客户，单元格内对销售额求和，并设置自定义显示公式 $$$/2，帆软对字段汇总后如果再进行计算，无法直接在单元格里手动加表达式，只能设置自定义显示公式。
3.2 A3、B3 单元格取数数据集中的客户和销售额字段。
3.3 C3: 求累计金额，=B3+C3[A3:-1]，增加条件属性，对非大客户的数据进行隐藏。
3.4 B4: 大客户数量，=count(B3[!0;!0]{C3 <= B2 || (C3[A3:-1] < B2 && C3 >= B2)})，用 count 函数统计累计销售大于总销售额一般的数量。
3.5 平均销售额，=sum(B3[!0;!0]{C3 <= B2 || (C3[A3:-1] < B2 && C3 >= B2)}) / B4，设置单元格的格式保留两位小数。
3.6 将不需要展现的行列设置隐藏。
报表结果
完成后点评
1、 用时 1 小时。
2、 累计实现可以通过内置的层次函数 LAYERTOTAL(B1, C1, D1) 实现，也可以通过层次坐标, 本例使用的是层次做表方式，内置功能比较丰富。
3、 可以根据条件公式动态设置报表行列的动态隐藏。
4、 需要增加辅助单元格，会增加额外资源的耗用，尤其是客户数量多时，报表中是取出了所有客户信息（小客户数量常常多得多），然后再判断隐藏，数据量大时报表计算性能会有影响。
制作过程
1、 配置并连接数据源。
2、 设置数据集
采用原生 SQL 数据集，直接通过 SQL 语句取数就行：
select 客户,sum(销售额) 金额 from 客户销售表 group by 客户 order by 金额 desc。
3、 设计报表模板
报表的设计在 excel 内完成，借助丰富的 excel 函数，smartbi 解决这种格间运算也不是太麻烦。如几个关键计算：
3.1 D1：所有客户的销售总金额，D2 中写入表达式 =D1/2, 取总销售额的一半。
3.2 D3 格：累积金额，=SSR_GetCell(D3,B3,-1)+C3。
3.3 E3：=IF(SSR_GetSubCells(D3,B3)>D2,1+SSR_GetCell(E3,B3,-1),0)，根据累积金额，标识当出现累积金额大于总金额一半的时候 标识为 1，后面累计值依然大于总金额一半，数值累计为 2/3/4（用于隐藏后面不要的数据）等
3.4 C4 格：=MATCH(1,SSR_GetSubCells(E3,A3),0)，根据 E3 的标识，查找第一个出现的 1 所在的序号，该序号即标识大客户数。
3.5 C5 格：平均销售额相对简单，=D4/C4，并设置报表中数据显示格式
3.6 隐藏行设置，需要隐藏掉非大客户数据，不支持表格内定义隐藏表达式，这里根据 E3 值大于 1 的就隐藏，要借助平台给电子表格设置宏（需要写 js 代码）实现：
运行结果
完成后点评：
1、 用时：1.5 小时。
2、 完全在 excel 中操作，容易上手，操作起来比较方便。excel 函数丰富，这个报表主要是其查找类函数的使用。
3、 隐藏不需要（除大客户外）的客户数据时，不支持表格内定义隐藏表达式，需要 js 代码才能实现，难度较大。
4、 需要增加辅助单元格，会增加额外资源的耗用，尤其是客户数量多时，报表中是取出了所有客户信息（小客户数量常常多得多），然后再判断隐藏，数据量大时报表计算性能会有影响。
制作过程：
1、 配置并连接数据源。
2、 设置数据集
使用 SQL 语句数据集：select 客户,sum(销售额) 金额 from 客户销售表 group by 客户 order by 金额 desc
3、 设计报表模板
3.1 第一列增加了个按销售额取数，实际中发现数据集中虽然按照销售额排序了，但是直接取出客户名称字段时，即使不排序，也不会按照数据集原有数据排序，所以前边增加了个销售额字段，并且设置降序排序。
3.2 第四列中取出了总销售额以及对应的累计销售额，累计销售额直接拖拽，设置下格间计算未累计就行
3.3 大客户数量单元格使用格间计算，里边表达式为：
var a=0;
for(var i=1;i<=ridx-1;i++){
if(cell(i,3)>=cell(0,3)/2){
a=i;
break;
}
}
通过 js 语法，对行列循环，判断累计值是否大于销售额总计的一半，返回对应行数也就是数量。
3.4 大客户平均销售额同样的做法：
var a=0;
for(var i=1;i<=ridx-2;i++){
if(cell(i,3)>=cell(0,3)/2){
a=cell(i,3)/i;
break;
}
}
3.5 第一列和第四列是辅助列，右侧有个隐藏列设置，将这两列隐藏掉
3.6 隐藏行设置，永洪不支持单元格隐藏行表达式，如果要隐藏掉非大客户数据，需要通过 js 语句实现，可以在 js 中获取到报表计算后的行属性，根据 3.3 中获取的大客户数量动态设置非大客户（报表计算后行号超过大客户数量的行）行高为 0，来达到隐藏的效果，这个和 Smartbi 有些类似，这里就不具体实现了。
运行结果
完成后点评
1、 用时 2 小时，格间计算那几个单元格处理时间较长，比较考验开发能力。
2、 格间计算中可以用 js 语法进行数据的计算，适合开发人员操作，比较灵活, 但同时内置函数少，比较考验开发人员能力。
3、 累计设置比较方便，直接用内置的语法就行。
4、 排序设置有问题，数据集中设置了数据的排序，但是报表里默认不会按照相应的顺序排序（也可能没找到方法）。
5、 需要增加辅助列来实现，比如累计列，如果数据量大会额外占用内存空间。
6、 可以设置隐藏列，但是设置后设计界面也就无法看到该列，无法还原，而且如果数据集变动而隐藏列里引用了变动前的字段，很难更改。
7、 需要增加辅助单元格，会增加额外资源的耗用，尤其是客户数量多时，报表中是取出了所有客户信息（小客户数量常常多得多），然后再判断隐藏，数据量大时报表计算性能会有影响。并且隐藏掉非大客户信息时，需要写复杂的 js，难度较大。
制作过程
1、 配置并链接数据源
2、 设置数据集
直接通过 SQL 语句取数就行：SQL 语句为：select customer,sum(sales) as sales from sales group by customer order by sum(sales) desc
3、 设计报表模板
3.1 A1：=sum(TEST.sales)/2 计算所有客户的销售总金额的一半，并在右侧属性里设置隐藏属性
3.2 A3：=TEST.customer 浮动维字段，同时在右侧设置排序的依据是 B3。
3.3 C3：=self.leftcell.value+self.upcell.value 实现金额累计计算
3.4 D3：=if(GRID1.C3.upcell.value
3.5 B4：=sum(GRID1.D3$)，根据 D3 的标识统计出来大客户数量（D3 中大客户返回 1，对 1 求和就是大客户数量）
3.6 B5：=GRID1.D3$.select(@.value=1).select(true,@.leftcell(2).value).avg() 先使用数组的方式过滤出来对应的数据，然后再进行求平均的计算
3.7 选中第 3 行设置显示表达式为：<#=if(GRID1.D3=1,1,0)#>，D3 单元格标识出了本条数据是否是大客户，这里根据 D3 单元格值控制本行是否显示（如果为 1 则返回 1 表示显示，否则返回 0 不显示）
运行结果
完成后点评：
1、 制作用时：1.5 小时
2、 内置函数比较丰富，本表中常用操作基本上都是通过内置函数实现。
3、 可以根据条件公式动态设置报表行列的动态隐藏，比较方便。
4、 需要增加辅助单元格，会增加额外资源的耗用，尤其是客户数量多时，报表中是取出了所有客户信息（小客户数量常常多得多），然后再判断隐藏，数据量大时报表计算性能会有影响。
这个例子报表结果格式相对比较简单，各个报表工具基本上都能实现。
对于本例中考查的过程计算，帆软、Smartbi、永洪、亿信这四款工具实现方法基本一致，都是通过辅助行列取出销售额总计、累计销售额，然后进行数据判断来确定哪些客户是大客户并对数据统计，最后将辅助行列再隐藏掉。整个过程有一定的难度和繁琐度，这是因为报表只能执行状态式计算，用来处理过程计算就只能采取这种迂回的办法。
这几款产品之间来看，帆软、Smartbi、亿信基本上一致，都是使用内置函数或者方法实现，隐藏行列也比较方便。而永洪累计实现方便，直接通过鼠标设置就行，但是做数据统计时就要写复杂的 javascript 语法来统计，这块实现难度比较大，而且隐藏行列设置也不太方便。永洪的复杂报表功能相比其它三款要更弱一点，帆软在这四款中相对更好，这和之前测试其它案例的结论也一致。
润乾事实上也可以采用隐藏格手段实现，但我们在这里则提供了一种与众不同的方法。润乾增加了一个计算层，使用脚本数据集，可以更方便地实现过程式计算，返回给报表就是处理后的数据结果，报表中不再做特别处理，整体过程更为简单，而且消耗资源也比使用辅助格要少得多，性能会更优。仍然是和之前测试案例的结论一致，润乾是这几款产品中拥有最强计算能力的产品，计算层明显拉开了和其它产品的差距，这对于高效开发复杂报表是至关重要的。
我们再通过一个例子在看计算层的意义，查询股票最长连续上涨的天数：
数据源是一个文本，记录各支股票的每天的收盘价：
这个报表的样式也很简单，但计算过程要更为繁琐：需要先将按日期过滤后的数据集按股票代码分组，再计算出每组股票连续上涨的天数，然后再过滤出超过 5 天的股票。
这种复杂的过程，如果没有计算层的协助，使用隐藏格就是个非常麻烦的过程，大体描述如下：
1、 取出文本数据集，放入单元格中
2、 设置排序，按股票代码和日期排序
3、 按股票代码分组，做成两层报表
4、 在明细行增加辅助格计算连续上涨的天数
5、 在分组行计算最大连续上涨的天数
6、 将明细行隐藏
7、 将非指定日期和不满足条件的分组隐藏。
这个过程，对于帆软这种格间计算能力较强的报表工具，还只是繁琐；而 Smartbi、永洪和亿信这些就不只是繁琐的问题，中间的计算也很难表达。并且，无论是哪一款工具，都会导致整个报表臃肿，有大量的隐藏格（远远多于呈现出来的格子）。
要避免这些，实际使用的办法常常会借助自定义数据源，用 Java 读出数据把结果集计算好，或者把数据导入数据库用 SQL 来算。无论哪种方法，都依然非常繁琐，严重影响开发效率。
但是，如果像润乾报表一样有个可解释执行的计算层的话，那就会很简单，实现这个逻辑也不需要几行代码：
A1 :=file(“F:/ 股票信息.txt”).import@t().select(left(string(Date),7)==rq), 读取股票信息中的数据，并根据报表中传入的 rq 参数进行数据过滤，取某月数据
A2：=A1.sort(Date).group(SID)，按照日期排序，并根据 SID 字段分组
A3：go=A2.new(SID, ~.group@i(Closing>Closing\[-1\]).max(~.len()):ts)，Closing[-1] 取当前记录上一条的收盘价，A3 中根据同一只股票当天收盘大于前一天收盘价来取最大连续上涨天数。
A4：return A3.select(ts>=5)，取出 A3 中最大连续上涨天数大于 5 的数据，返回给报表数据集使用。
由于脚本数据集中已经处理好了数据，报表中制作就相当简单了，就不用具体解释了。
我们用了三篇文章对比了这五款以复杂报表能力为宣传点的报表工具:
润乾的优势非常明显，即使不考虑它特有的计算层，其复杂报表能力也是这五款产品中最强的。再加上计算层，可以说是遥遥领先，和其它几款产品相比已经差出档次了。
帆软居次，如果抛开润乾特有的计算层，帆软的复杂报表能力和润乾相比还是会弱，但相差不算很大了，而且帆软友好的界面能够再加分，可以认为润乾和帆软是第一档的产品，把复杂报表作为宣传点都算是名符其实的。
Smartbi 就要弱一些，模型基本实现了，但表达式细节和润乾帆软差得不少，可以算作是第二档的产品。把复杂报表作为宣传点，在 BI 领域中也还勉强说得过去。
永洪和亿信，严格地说，其复杂报表能力还处于没入门的阶段，和其它几款产品相比的差距非常大。只能算作第三档甚至不入档的产品了，其实这两款产品本来是 BI 色彩很强的产品，把复杂报表作为宣传点就有点名不符实了。
总结
进一步的例子
复杂报表最后的结论
总体结论如下：
