让 Birt 报表脚本数据源变得既简单又强大

概述:运行在 JVM 上的 SQL 函数和存储过程

众所周知,有些数据库没有强大的分析函数(eg. Mysql), 有些数据库没有存储过程(eg. Vertica),当遇到复杂的数据计算,往往只能通过 Python,R 等外部脚本来实现,但这些脚本语言和主流工程语言(Java)集成性不好,如果直接用工程语言实现类似 SQL 函数和存储过程的功能,经常只是针对某个计算需求编写冗长的代码,代码几乎不可复用。

另外,即便拥有强大的分析函数,实现稍复杂的逻辑其实也不算容易,比如下面这种常见的业务计算,找出“销售额占到一半的前 n 个客户,并按销售额从大到小排序”,在 Oracle 中 SQL 实现如下:

with A as
(selectCUSTOM,SALESAMOUNT,row_number() over (order by SALESAMOUNT) RANKING
from SALES)
select CUSTOM,SALESAMOUNT
from (select CUSTOM,SALESAMOUNT,sum(SALESAMOUNT) over (order by RANKING) AccumulativeAmount
from A)
where AccumulativeAmount>(select sum(SALESAMOUNT)/2 from SALES)
order by SALESAMOUNT desc

说明:按照销售额累计值从小到大排序,通过累计值大于“一半销售额”的条件,逆向找出占到销售额一半的客户。为了避免窗口函数在计算累计值时对销售额相同的值处理出现错误,用子查询先计算了排名。

下面是用集算器实现相同逻辑的代码:
d1png
从上述代码我们可以看到,集算器利用一套简洁的语法取代了需嵌套 SQL+ 窗口函数才能实现的逻辑,并且具有通用一致性(任何数据源代码一致)。

集算器是一套运行在 JVM 上专门处理结构化数据的脚本语言,类似用 SQL 函数和存储过程,与 Java 集成可以创建可移植、功能强大和与数据库无关的计算逻辑,运行于中间层的计算逻辑和运行于数据库层的数据逻辑之间的分离,提高了应用程序的可扩展性、灵活性和可维护性。

应用场景:报表数据准备

应用结构

d4png

集成后,集算器嵌入报表应用层,相当于本地的逻辑数据库(不需要单独服务器部署),在报表与数据源间作为报表数据准备层,完成各种复杂的计算任务。

如何集成

下面以 Vertica 为数据源,Birt 为报表工具,描述怎样集成 esProc 作为数据准备层。

(一) Birt 开发环境

1、 基础 jar 集成

集算器 JDBC 需要三个基础 jar 包,都可以在 [esProc 安装目录]\esProc\lib 目录下找到,分别为:

esproc-bin-xxxx.jar 集算器计算引擎及JDBC驱动包
jdom-1.1.3.jar 解析配置文件
icu4j-60.3.jar 处理国际化

除了基础包外,还有一些为完成特定功能的 jar 包。如,要在集算器 JDBC 用其它数据库作为集算器的数据源,那么还需要相应数据库的驱动 jar 包,本文涉及到 vertica,所以要同时增加其 JDBC 驱动包(以 vertica9.1。0 为例)

vertica-jdbc-9.1.0-0.jar vertica 官网即可下载

获取到以上 Jar 后,将其拷贝至 Birt 开发环境 [安装目录]\plugins\org.eclipse.birt.report.data.oda.jdbc_4.6.0.v20160607212 下。

注意:标红部分,不同 birt 版本略有不同

2、 配置文件集成

raqsoftConfig.xml,主要包含脚本文件路径、其他作为集算器数据源的连接配置等。

可在 [esProc 安装目录]\esProc\config 下找到,需复制后放置在类路径下,同样将其拷贝至 Birt 开发环境 [安装目录]\plugins\org.eclipse.birt.report.data.oda.jdbc_4.6.0.v20160607212 下。

注意:配置文件名不能改变

(二) Birt 应用环境

1、 将(一)中的所有 jar 拷贝至应用的 WEB-INF\lib 下

2、 将 raqsoftConfig.xml 拷贝至应用的 WEB-INF\classes 下

例一:常规调用

1、 Vertica 内 Sales 表字段及数据说明(通过 vsql 查询,本测试库有 2013/14/15 年数据)
3png
2、 编写并部署 esProc 脚本

(1) esProc 设计器添加 vertica JDBC 驱动包

vertica 官网下载 jdbc 驱动包(如,vertica-jdbc-9.1.0-0.jar),放至【esProc 安装目录】\common\jdbc 下

(2) 新增 vertica 数据源

打开设计器,Tool—Datasource connection 新增 JDBC 方式连接

4png

点 ok 保存,再点 connect 连接

5png

数据源名称变为粉色即表示连接成功。

(3) 编写算法脚本 (文件:VerticaExternalProcedures.dfx)

File – New

d2png

(4) 部署脚本

将脚本文件部署到 raqsoftConfig.xml 配置的脚本文件主目录下。

birtpng

3、 esProc 配置文件 raqsoftConfig.xml 内增加 verticaLink 数据源连接配置

<DB name="verticaLink">
<property name="url" value="jdbc:vertica://192.168.10.10:5433/ForEsprocTestDB"/>
<property name="driver" value="com.vertica.jdbc.Driver"/>
<property name="type" value="0"/>
<property name="user" value="dbadmin"/>
<property name="password" value="runqian"/>
<property name="batchSize" value="0"/>
<property name="autoConnect" value="false"/>
<property name="useSchema" value="false"/>
<property name="addTilde" value="false"/>
<property name="needTransContent" value="false"/>
<property name="needTransSentence" value="false"/>
<property name="caseSentence" value="false"/>
</DB>

4、 Birt 开发工具内新建报表,并增加 esProc 数据源“esProcConnection”

7png

Driver class 为”com.esproc.jdbc.InternalDriver(v1.0)”,会用到 esproc-bin-xxxx.jar 等
Database URL 为”jdbc:esproc:local://”

5、 Birt 调用 Vertica 外部存储过程(esProc 数据集)

新建“Data Sets”,选择配置的集算器数据源(esProcConnection),数据集类型选择存储过程(SQL Stored Procedure Query)
8png
Next,查询脚本(Query-Query Text)输入:{call VerticaExternalProcedures()}

9png

其中:VerticaExternalProcedures 为 esProc 脚本文件名

Finish,预览数据(Preview Results)

10png

此时,便看到了把 esProc 脚本作为 Vertica 外部存储过程取数辅助计

算的过程。

6、 Birt Web 端呈现

以简单的网格报表为例

报表设计如下

11png

Web 发布预览

12png

例二:参数调用

这里把“找出订单销售额占到一半的前 n 个客户,并按销售额从大到小排序”,改为要求按年查询,即“按年度查询订单销售额占到一半的前 n 个客户,并按销售额从大到小排序”,因此增加了参数过滤功能。

下面看下具体的改法:

1、 esProc 脚本增加按年度参数及过滤功能

打开 esProc 设计器,Program – Parameter – Add

参数名为“qyear”(可与报表参数名不同)

13png

脚本改动:

d3png

注:A2 增加条件过滤

2、 报表内增加年度参数

报表增加按年查询的入口参数,参数名为“qyear”

开发工具打开报表,Data Explorer – Report Parameter – new patameter

14png

Default value 为 qyear 的默认值。

3、 报表数据集增加数据集参数并与报表参数绑定

Data set 内编辑 VerticaExternalProcedures 数据集

15png

Query Text 改为“{call VerticaExternalProcedures(?)}”,? 为入口参数年

度,此处用占位符设置。

选 Parameters,增加数据集参数 qyear 并与报表参数 qyear 绑定。

16png

Preview Results, 这里根据 qyear 默认值仅有查询 2013 年数据

17png

改为“2015”后

18png

4、 Web 端预览

19png

查询“2015”年数据

20png

修改或 url 传入 qyear 为“2013”年后

21png

更多 "birt 整合方案" 详见:https://c.raqsoft.com.cn/tag/Report

更多数据源相关问题请查看:数据源相关问题分类导航
* 报表如何从 mongodb 中取数
* Json 数据怎么和数据库数据一起做运算
* 润乾报表设计器中使用 spring 框架实现自定义数据集
* 数据库密码中含有特殊字符润乾应用部署时 xml 怎么处理