如何让Birt报表脚本数据源变得既简单又强大
<p><strong>概述：运行在 jvm 上的 sql 函数和存储过程</strong></p>
<p>
总所周知，有些数据库没有强大的分析函数（eg. mysql）, 有些数据库没有存储过程（eg. vertica），当遇到复杂的数据计算，往往只能通过 python,r 等外部脚本来实现，但这些脚本语言和主流工程语言（java）集成性不好，如果直接用工程语言实现类似 sql 函数和存储过程的功能，经常只是针对某个计算需求编写冗长的代码，代码几乎不可复用。</p>
<p>
另外，即便拥有强大的分析函数，实现稍复杂的逻辑其实也不算容易，比如下面这种常见的业务计算，找出“销售额占到一半的前 n 个客户，并按销售额从大到小排序”，在 oracle 中 sql 实现如下：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_927234">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">with</code> <code class="sql plain">a </code><code class="sql keyword">as</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">(selectcustom,salesamount,row_number() over (</code><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">salesamount) ranking</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">from</code> <code class="sql plain">sales)</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">select</code> <code class="sql plain">custom,salesamount</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">from</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">custom,salesamount,</code><code class="sql color2">sum</code><code class="sql plain">(salesamount) over (</code><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">ranking) accumulativeamount</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">from</code> <code class="sql plain">a)</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql keyword">where</code> <code class="sql plain">accumulativeamount>(</code><code class="sql keyword">select</code> <code class="sql color2">sum</code><code class="sql plain">(salesamount)/2 </code><code class="sql keyword">from</code> <code class="sql plain">sales)</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">salesamount </code><code class="sql keyword">desc</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
说明：按照销售额累计值从小到大排序，通过累计值大于“一半销售额”的条件，逆向找出占到销售额一半的客户。为了避免窗口函数在计算累计值时对销售额相同的值处理出现错误，用子查询先计算了排名。</p>
<p>
<strong>下面是用集算器实现相同逻辑的代码：</strong></p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/f988dc1f9ecce51047a205b679e3ca9d.jpg"></p>
<p>
从上述代码我们可以看到，集算器利用一套简洁的语法取代了需嵌套 sql+ 窗口函数才能实现的逻辑，并且具有通用一致性（任何数据源代码一致）。</p>
<p>
集算器是一套运行在 jvm 上专门处理结构化数据的脚本语言，类似用 sql 函数和存储过程，与 java 集成可以创建可移植、功能强大和与数据库无关的计算逻辑，运行于中间层的计算逻辑和运行于数据库层的数据逻辑之间的分离，提高了应用程序的可扩展性、灵活性和可维护性。</p>
<p>
<span><strong>应用场景：报表数据准备</strong></span></p>
<p>
应用结构</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/2c53bdb010bf28ef0f9d9a6d180d3f27.jpg"></p>
<p>
集成后，集算器嵌入报表应用层，相当于本地的逻辑数据库（不需要单独服务器部署），在报表与数据源间作为报表数据准备层，完成各种复杂的计算任务。</p>
<p>
<span><strong>如何集成</strong></span></p>
<p>
下面以 vertica 为数据源，birt 为报表工具，描述怎样集成 esproc 作为数据准备层。</p>
<p>
<strong>（一） birt 开发环境</strong></p>
<p>
1、 基础 jar 集成</p>
<p>
集算器 jdbc 需要三个基础 jar 包，都可以在 \esproc\lib 目录下找到，分别为：</p>
<p>
dm.jar 集算器计算引擎及jdbc驱动包</p>
<p>
jdom.jar 解析配置文件</p>
<p>
icu4j\_3\_4_5.jar 处理国际化</p>
<p>
除了基础包外，还有一些为完成特定功能的 jar 包。如，要在集算器 jdbc 用其它数据库作为集算器的数据源，那么还需要相应数据库的驱动 jar 包，本文涉及到 vertica，所以要同时增加其 jdbc 驱动包（以 vertica9.1。0 为例）</p>
<p>
vertica-jdbc-9.1.0-0.jar vertica 官网即可下载</p>
<p>
获取到以上 jar 后，将其拷贝至 birt 开发环境 [安装目录]\plugins\org.eclipse.birt.report.data.oda.jdbc_4.6.0.v20160607212 下。</p>
<p>
注意：标红部分，不同 birt 版本略有不同</p>
<p>
2、 配置文件集成</p>
<p>
raqsoftconfig. ，主要包含集算器授权、脚本文件路径、其他作为集算器数据源的连接配置等。</p>
<p>
可在 \esproc\config 下找到，需复制后放置在类路径下，同样将其拷贝至 birt 开发环境 [安装目录]\plugins\org.eclipse.birt.report.data.oda.jdbc_4.6.0.v20160607212 下。</p>
<p>
注意：配置文件名不能改变</p>
<p>
<strong>（二） birt 应用环境</strong></p>
<p>
1、 将（一）中的所有 jar 拷贝至应用的 web-inf\lib 下</p>
<p>
2、 将 raqsoftconfig. 拷贝至应用的 web-inf\classes 下</p>
<p>
例一：常规调用</p>
<p>
1、 vertica 内 sales 表字段及数据说明（通过 vsql 查询，本测试库有 2013/14/15 年数据）</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/3f97470806cfcf5b3b6236af90e851e7.jpg"></p>
<p>
2、 编写并部署 esproc 脚本</p>
<p>
（1） esproc 设计器添加 vertica jdbc 驱动包</p>
<p>
vertica 官网下载 jdbc 驱动包（如，vertica-jdbc-9.1.0-0.jar），放至【esproc 安装目录】\common\jdbc 下</p>
<p>
（2） 新增 vertica 数据源</p>
<p>
打开设计器，tool—datasource connection 新增 jdbc 方式连接</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/c3a08f40042722582642074e87e21bdb.jpg"></p>
<p>
点 ok 保存，再点 connect 连接</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/5ce5308900eff7287f2d0e08e99c06ae.jpg"></p>
<p>
数据源名称变为粉色即表示连接成功。</p>
<p>
（3） 编写算法脚本 (文件：verticaexternalprocedures.dfx)</p>
<p>
file – new</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/75c754b27d020b8f4eb42a0ef0b3a9fb.jpg"></p>
<p>
（4） 部署脚本</p>
<p>
将脚本文件部署到 raqsoftconfig. 配置的脚本文件主目录下。</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/7e3a877502004f0c487b1d90e8fee1dd.jpg"></p>
<p>
3、 esproc 配置文件 raqsoftconfig. 内增加 vertica 数据源连接配置</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_154381">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain"><db </code><code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">"vertica "</code><code class="sql plain">></code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain"><property </code><code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">"url"</code> <code class="sql plain">value=</code><code class="sql string">"jdbc:vertica://192.168.10.10:5433/foresproctestdb"</code><code class="sql plain">/></code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain"><property </code><code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">"driver"</code> <code class="sql plain">value=</code><code class="sql string">"com.vertica.jdbc.driver"</code><code class="sql plain">/></code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain"><property </code><code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">"type"</code> <code class="sql plain">value=</code><code class="sql string">"0"</code><code class="sql plain">/></code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain"><property </code><code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">"user"</code> <code class="sql plain">value=</code><code class="sql string">"dbadmin"</code><code class="sql plain">/></code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain"><property </code><code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">"password"</code> <code class="sql plain">value=</code><code class="sql string">"runqian"</code><code class="sql plain">/></code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain"><property </code><code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">"batchsize"</code> <code class="sql plain">value=</code><code class="sql string">"0"</code><code class="sql plain">/></code>
</div>
<div class="line number8 index7 alt1">
<code class="sql plain"><property </code><code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">"autoconnect"</code> <code class="sql plain">value=</code><code class="sql string">"false"</code><code class="sql plain">/></code>
</div>
<div class="line number9 index8 alt2">
<code class="sql plain"><property </code><code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">"useschema"</code> <code class="sql plain">value=</code><code class="sql string">"false"</code><code class="sql plain">/></code>
</div>
<div class="line number10 index9 alt1">
<code class="sql plain"><property </code><code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">"addtilde"</code> <code class="sql plain">value=</code><code class="sql string">"false"</code><code class="sql plain">/></code>
</div>
<div class="line number11 index10 alt2">
<code class="sql plain"><property </code><code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">"needtranscontent"</code> <code class="sql plain">value=</code><code class="sql string">"false"</code><code class="sql plain">/></code>
</div>
<div class="line number12 index11 alt1">
<code class="sql plain"><property </code><code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">"needtranssentence"</code> <code class="sql plain">value=</code><code class="sql string">"false"</code><code class="sql plain">/></code>
</div>
<div class="line number13 index12 alt2">
<code class="sql plain"><property </code><code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">"casesentence"</code> <code class="sql plain">value=</code><code class="sql string">"false"</code><code class="sql plain">/></code>
</div>
<div class="line number14 index13 alt1">
<code class="sql plain"></db></code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
4、 birt 开发工具内新建报表，并增加 esproc 数据源“esprocconnection”</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/3841248f516304d057dc1cd471d4c04b.jpg"></p>
<p>
driver class 为”com.esproc.jdbc.internaldriver(v1.0)”，会用到 dm.jar 等</p>
<p>
data url 为”jdbc:esproc:local://"</p>
<p>
5、 birt 调用 vertica 外部存储过程（esproc 数据集）</p>
<p>
新建“data sets”，选择配置的集算器数据源（esprocconnection），数据集类型选择存储过程（sql stored procedure query）</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/c628523d43e539a0e7e84201339279bc.jpg"></p>
<p>
next，查询脚本（query-query text）输入：{call verticaexternalprocedures()}</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/1ca4a03a81bd860aa6a1e43c9cd65513.jpg"></p>
<p>
其中：verticaexternalprocedures 为 esproc 脚本文件名</p>
<p>
finish，预览数据（preview results）</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/963e73982b2691db3634fc5503d13c88.jpg"></p>
<p>
此时，便看到了把 esproc 脚本作为 vertica 外部存储过程取数辅助计</p>
<p>
算的过程。</p>
<p>
<strong>6、 birt web 端呈现</strong></p>
<p>
以简单的网格报表为例</p>
<p>
报表设计如下</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/aff147238c36816c4d82b20307089764.jpg"></p>
<p>
web 发布预览</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/5ee681e4bc7a04031f38c98e258d33a2.jpg"></p>
<p>
例二：参数调用</p>
<p>
这里把“找出订单销售额占到一半的前 n 个客户，并按销售额从大到小排序”，改为要求按年查询，即“按年度查询订单销售额占到一半的前 n 个客户，并按销售额从大到小排序”，因此增加了参数过滤功能。</p>
<p>
<span><strong>下面看下具体的改法：</strong></span></p>
<p>
1、 esproc 脚本增加按年度参数及过滤功能</p>
<p>
打开 esproc 设计器，program – parameter – add</p>
<p>
参数名为“qyear”（可与报表参数名不同）</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/00b4055621eede1f680ef63e5b98875a.jpg"></p>
<p>
脚本改动：</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/e171d63555c68e9f185308119cb72cd2.jpg"></p>
<p>
注：a2 增加条件过滤</p>
<p>
2、 报表内增加年度参数</p>
<p>
报表增加按年查询的入口参数，参数名为“qyear”</p>
<p>
开发工具打开报表，data explorer – report parameter – new patameter</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/ebfd73a326ed6a802e0a47c51d97a292.jpg"></p>
<p>
default value 为 qyear 的默认值。</p>
<p>
3、 报表数据集增加数据集参数并与报表参数绑定</p>
<p>
data set 内编辑 verticaexternalprocedures 数据集</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/6f8f8f39b7a872f386ca50605fc6c92e.jpg"></p>
<p>
query text 改为“{call verticaexternalprocedures(?)}”，? 为入口参数年</p>
<p>
度，此处用占位符设置。</p>
<p>
选 parameters，增加数据集参数 qyear 并与报表参数 qyear 绑定。</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/24463307bf91dc29843539eb9724e77a.jpg"></p>
<p>
preview results, 这里根据 qyear 默认值仅有查询 2013 年数据</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/963e73982b2691db3634fc5503d13c88.jpg"></p>
<p>
改为“2015”后</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/6becc28c05676187ba825bbdbec8fb8f.jpg"></p>
<p>
4、 web 端预览</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/c95c48863361c384a803bae48b095f38.jpg"></p>
<p>
查询“2015”年数据</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/5ee681e4bc7a04031f38c98e258d33a2.jpg"></p>
<p>
修改或 url 传入 qyear 为“2013”年后</p>
<p>
<img title="如何让Birt报表脚本数据源变得既简单又强大" alt="如何让Birt报表脚本数据源变得既简单又强大" src="https://zhuji.jb51.net/uploads/img/202305/b904f03f19c7b78352b42ca04d963d3c.jpg"></p>
<p>
更多 "birt 整合方案" 详见： http://c.raqsoft.com.cn/tag/report</p>
<p>
<span><strong>总结</strong></span></p>
<p>
以上所述是小编给大家介绍的如何让Birt报表脚本数据源变得既简单又强大，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！</p>
頁:
[1]