报表工具的动态数据源实现

  有时候我们需要用参数动态指定数据源，或将多数据源连接为单数据源，或向子报表、table控件动态传入数据源名。对于此类需求，报表工具经常要借助高级语言实现或牺牲安全性以降低复杂度，尤其是BIRT、Jasper等单源报表。

  使用免费的集算器可以弥补这一不足。集算器封装了丰富的结构化计算函数，支持动态解析表达式，支持多数据源混合计算，书写简单脚本就能实现动态数据源。集算器还提供了简单易用的JDBC接口，报表工具可将集算器脚本文件当做数据库存储过程执行，传入参数并用JDBC获得返回结果。

  集算器与报表工具的集成结构如下：

 

  下面举例说明集算器根据参数动态切换数据源的基本过程：

  数据源myDB1和oraDB分别指向不同的数据库，两库中有相同结构的表sOrder，报表需要根据参数动态连接数据源，查询并展现sOrder中金额大于1000的订单。

  myDB1中sOrder部分数据如下：

 

  oraDB中sOrder部分数据如下：

 

  集算器代码：=${pSource}.query(“select * from sOrder where Amount>?”,pAmount)

  pSource、pAmount都是报表参数，其中pSource代表数据源名，${…}表示将字符串或字符串变量解析为表达式，pAmount代表订单金额。

  当pSource=”myDB1”时，A1的计算结果如下：

 

  当pSource=”oraDB”时，A1的计算结果如下

 

  报表工具可用JDBC的方式调用集算器脚本，就像调用普通数据库中的存储过程，形如：call 脚本文件名(参数1…参数N)。集算器的返回结果会以普通数据集的形式参与报表计算。具体用法可参考集算器集成与应用之JasperReport集成 和集算器集成与应用之BIRT集成 。
  作为专业的报表数据源工具，集算器还可以实现更多的计算，下面分别举例。

多源数据join后计算

  Sales是mysql数据库中的表，存储着多名销售员每天的多个订单，其中字段SellerId是销售员编号。emp是mssql数据库中的表，存储着销售员信息，其中字段EId是销售员编号。现在需要在报表中展现：订单编号、日期、金额、销售员名字、部门名称，条件是：订单日期在最近N天（比如30天）或者订单属于某几个受关注的部门（比如Markeding和Finance）。部分源数据如下：

  库表sales

 

  库表emp

 

  集算器代码：

 

  A1,A2:查询数据库，myDB1和myDB2分别直向mysql和mssql。

  A3：将A1中的SellerId字段替换成A2中对应的记录，关联字段为EId。A3的计算结果如下（蓝色字体表示该数据项包含下级成员）：

 

  当A2中找不到对应的记录时，函数switch默认保留A1中记录，对应的SellerId显示为空，效果类似于左连接。如果想进行内连接，应当使用选项@i，形如：A1.switch@i(SellerId,A2:EId) 。

  A4：对关联结果进行过滤，第1个条件是：订单日期在最近N天（对应参数days），表达式为OrderDate>=after(date(now()),days*-1)。第2个条件是：订单属于某几个受关注的部门（对应参数depts），表达式是depts.array().pos(SellerId.Dept)。运算符||表示逻辑关系“或”。

  函数after可以算出相对时间，函数array可以按分隔符将字符串拆分为集合。函数pos可以找出成员在集合中的位置。SellerId.Dept表示SellerId字段对应的记录的Dept字段。

  Days和depts都是来自于报表的参数，如果分别输入30、”Marketing,Finance”，则A4的结果如下：

 

  A5：从A4中取得报表需要的字段，最终计算结果如下：

 

结果集union

  结果集ds1和ds2结构相同，分别来自mySQL和文本文件，需要将ds1和ds2纵向拼接起来再呈现为交叉表。源数据如下：

 

  集算器代码：

 

  A3：纵向拼接两个数据集。报表工具只需呈现基于单数据集的简单交叉表。

主子报表多数据源
  对于单数据源报表，如果同一张报表中的主表和子表使用不同的数据源，就需要显式传递数据库URL，或用JAVA类合并不同的数据源，前者安全性较低，后者代码复杂。使用集算器可以方便地解决此类问题，下面用例子来说明。

  设计一张主子报表，按薪酬范围显示每位员工的订单信息，主报表数据来自表emp(MySQL数据库)，子报表数据来自表sales（MSSQL数据库）。部分源数据如下：

  集算器代码：

  empEsProc.dfx（该脚本文件用于主报表）

 

  A1：按薪酬范围查询MYSQL数据库的表emp。

  salesEsProc.dfx（该脚本文件用于子报表）

 

  A1：按员工ID从MSSQL的sales表查找相应的订单。如果eid=1，则A1的计算结果如下：

 

  可以看到，不同的数据源可被集算器合并为单一数据源，主子报表只需调用不同的集算器文件即可实现本需求。

  有些报表系统会存在多种数据源，各报表使用的数据源不同且经常发生变更，管理起来难度较大，使用集算器单一数据源可以降低管理难度。
类似地，单一数据源还可以解决子报表多数据源的问题，即：报表中存在多个子报表（或table控件），每个子报表使用不同的数据源。

主子表动态关联

  主表相关的子表分布在多个数据库中，要求报表呈现这些数据源动态关联的结果。此类需求可以用集算器简单实现，例如：

主表org在数据源Master中，org里每条记录对应的子表在不同的数据源中，比如org.org_id=”ORG_S”时，这条记录对应的子表是数据源S_odaURL的User表，org.org_id=”ORG_T”时，这条记录对应的子表是数据源T_odaURL中的User表。子表不止两个，名字都是User，需要和主表动态关联再呈现在报表中。逻辑上的关系如下：

 

  集算器代码如下：

 

  A1:执行SQL，从数据源Master的org表取数据。arg1是来自报表的参数，arg1=”ORG”时A1的计算结果如下：

 

  A2:依次循环A1中的记录，每次动态关联一个子表，并将关联结果合并在B2中。集算器用自然缩进来表示循环语句的作用范围，即B2-B7，循环体中可用A2来引用循环变量，可用#A2来引用循环计数。

  B2：根据当前记录的org_id字段计算出对应子表的数据源名。第一次循环时，B2的计算结果为” S_odaURL”。

  B3：根据名字显式连接到数据源。

  B4：按条件查询user表中的数据。

  B5：在子表B4中新增三列，数据来自主表。比如第一次循环时，B5计算结果如下：

 

  B6:将B5的计算结果合并到B1，运算符”|”等价于函数union。循环结束后B1会存储报表需要的完整数据，如下：

 

  B7：显式关闭数据源连接。

  A8：将B1显式返回给报表工具（默认会返回最后一个单元格）

根据参数显示时间精度不同的数据

  报表需要用折线图展现近期的销售额变化。unitType是报表参数，代表时间精度，当unitType等于”hour”时，需要展现近1小时内每五分钟的销售额，当unitType=”day”时，需要展现近1天内每小时的销售额，当unitType=”week”时，需要展现近1周内每天的销售额。数据来源于orders表，字段Amount是订单金额，t是订单发生时间。

  集算器代码：

 

  A1:空结果集，用来存储B2-B4产生的时间序列。

  A2-B2:根据报表参数unitType生成不同的时间序列，B2可生成近1小时内的12个时间点，每个时间点间隔5分钟，B3生成近1天内的时间点,B4生成近1周的时间点。

  A5:循环A1，每次统计一个时间段内的销售额，”~”表示A1的当前成员，”~[-1] “表示上一个成员。当unitType=”day”，会生成12条记录的单字段结果集。

  A6:将A5通过JDBC传给报表。之后就可以按普通统计图的方法去呈现。