问题背景

G 电力集团需要统计各子公司采购额月度走势，即给定月份区间后，统计各子公司各月份的采购额，以交叉表的形式呈现，左表头显示为子公司，上表头动态显示月份。如果统计结果缺少某月份，上表头需补足该月份；点击某月份时，报表需按该列排序。

表样如下：

难点分析：

1.交叉表难以排序

如果不排序，仅用交叉表呈现上述数据，实现过程其实很简单：直接获取数据（形如 select 单位, 月份, 金额 from tbl）, 左表头按单位分组，上表头按月份分组，交叉值按单位和月份汇总。

难在表头排序上。

排序一般用 SQL 的“order by 字段名”实现，比如按 201601 列排序，就要写成“order by 201601”，但 201601 在交叉表的 SQL 中并非字段名，而是字段值，所以无法排序。

有些报表工具支持交叉表在浏览器中排序（脱离 SQL），按理说可以实现本功能，但也会存在设置困难、依赖具体报表工具、挑浏览器、挑数据类型等问题。如果强调交互性，那就应该使用多维分析工具或者 jquery 这种前台控件，这已经脱离了报表的范畴。

良好的报表结构是：后台完成所有计算，包括排序，前台只需呈现。

2. SQL 难以动态转置

要想在后台实现排序，就要把原 SQL 中的字段值（比如 201601）变为字段名，这就是所谓的行转列。给定列名的静态行转列很简单，用 Oracle 的 pivot 函数就能实现。

难在动态行转列。

本报表的月份区间是动态且连续的，因此列名也是动态的，而且物理表中的月份有缺失，动态列名需要补足缺失的月份。要实现这种动态转置，需要用 JAVA 或存储过程拼凑动态列名，再配合 pivot 函数，其实现过程相当繁琐。另外生成连续月份本身就很困难，尤其是跨年度的情况。

这种方法还存在挑数据库的问题，比如 MySQL 或 NoSQL 就根本不支持 pivot 函数。

解决方案

通过开源集算器 SPL 可以快速完成动态交叉报表，开发效率比原有 SQL 方案提升 3 倍以上。作为动态结构化计算语言，SPL 可以用简短的脚本生成连续月份，也可以直观进行动态转置，而且与具体的数据源无关。SPL 还提供了友好的 JDBC 接口，任意报表工具甚至 JAVA 代码都可以实现本表样。

下面是 SPL 的实现示例（非背景中报表）。

解读：

1.     本例中有 3 个参数，区间开始 begin，区间结束 end（字符串，如 2015-02），排序字段序号 order（整数，如 4）

2.     concat@c表示将 A3 中的整数序列，转为逗号分隔的字符串，即：201409,201410,201411,201412,201501,201502。${字符串 }表示将字符串作为脚本执行，这是动态语法的特点。

3.     注意 SQL 中没有 201410 的数据，而 A5 的转置结果必须补足该月份

4.     A6 也使用了动态语法。A5.sort(${"#" / order})将被解释为A5.sort(#4)执行，即按第 4 列（201411）排序。

5.     上述算法支持任意数据源，包括 mysql/json/hive/ 阿里云等等

最后将计算后结果直接输出给前端报表进行呈现，像润乾报表等工具均支持动态列呈现。以润乾报表为例，报表制作如下：

再隐藏行 1，并在 B2 的超链接表达式书写："reportJsp/showReport.jsp?rpx=sort.rpx&order="+string(B1)+"&begin="+begin+"&end="+end

即可实现动态列补足排序报表。

方案效果

采用 SPL 以后，无论从编码效率还是可维护性等方面都获得了非常大的提升。这里对比一下效果：

总结

在成熟报表工具的支持下，报表格式开发的工作量已经不大，工作量已经从呈现阶段转到数据准备阶段了，这部分开发量占比远远大于报表布局那些事。而目前大多数报表工具都没有这种能力，大家只能采用原始硬编码的方式实现，开发效率非常低下。

SPL 的出现很好地解决了这个问题。SPL 提供了丰富的计算类库可以满足各类复杂计算的需要，过程化脚本编辑使得算法实现也更简单，从而进一步提升报表开发效率。

对 SPL 感兴趣可以参考 敏捷数据计算引擎，在《SPL COOK》中有大量 SPL 敏捷计算的例子。

其它相关案例：

开源 SPL 缩短石油集团油井分析报表开发周期 10+ 倍

开源 SPL 缩短石化集团多维多层叉乘报表开发周期 120 倍

开源 SPL 缩短银行绩效考核报表开发周期 5 倍

开源 SPL 缩短制造业库龄计算报表开发周期 7.5 倍

开源 SPL 缩短电力企业风机监控报表开发周期 16 倍

开源 SPL 缩短科技公司动态科目报表开发周期 6 倍