报表工具的动态数据源实现
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">有时候<span style="color: black;">咱们</span>需要用参数动态指定数据源,或将<span style="color: black;">都数</span>据源连接为单数据源,或向子报表、table控件动态传入数据源名。<span style="color: black;">针对</span>此类<span style="color: black;">需要</span>,报表工具经常要借助高级语言实现或牺牲安全性以降低<span style="color: black;">繁杂</span>度,尤其是BIRT、Jasper等单源报表。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">使 用免费的集算器<span style="color: black;">能够</span>弥补这一不足。集算器封装了丰富的结构化计算函数,支持动态解析表达式,支持<span style="color: black;">都数</span>据源混合计算,书写简单脚本就能实现动态数据源。集算 器还<span style="color: black;">供给</span>了简单易用的JDBC接口,报表工具可将集算器脚本文件当做数据库存储过程执行,传入参数并用JDBC<span style="color: black;">得到</span>返回结果。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">集算器与报表工具的集成结构如下:</p><img src="https://p3-sign.toutiaoimg.com/4873/4598585609~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=nwlQ3GMEWEXk6QcCv7AaT5Wuuhc%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">下面举例说明集算器<span style="color: black;">按照</span>参数动态切换数据源的基本过程:</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">数据源myDB1和oraDB分别指向<span style="color: black;">区别</span>的数据库,两库中有相同结构的表sOrder,报表需要<span style="color: black;">按照</span>参数动态连接数据源,<span style="color: black;">查找</span>并展现sOrder中金额大于1000的订单。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">myDB1中sOrder部分数据如下:</p><img src="https://p3-sign.toutiaoimg.com/4871/4824861859~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=fSSpgudKr2BEcpZPyX8nVwPgTqs%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">oraDB中sOrder部分数据如下:</p><img src="https://p3-sign.toutiaoimg.com/4873/4598888068~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=pduWGHGAHY3a8t2DviMtqO5AO%2Fc%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">集算器代码:</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=${pSource}.query("select * from sOrderwhere Amount>?",pAmount)</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">pSource、pAmount都是报表参数,其中pSource<span style="color: black;">表率</span>数据源名,${…}<span style="color: black;">暗示</span>将字符串或字符串变量解析为表达式,pAmount<span style="color: black;">表率</span>订单金额。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">当pSource=”myDB1”时,A1的计算结果如下:</p><img src="https://p3-sign.toutiaoimg.com/4874/3570791444~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=9315aFArt3K3BGbUHfJr14%2FVaRE%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">当pSource=”oraDB”时,A1的计算结果如下:</p><img src="https://p3-sign.toutiaoimg.com/4875/2966467451~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=0ypxwYo1fFX5Xdtsc30mKsX7W%2Fg%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">报表工具可用JDBC的方式调用集算器脚本,就像调用普通数据库中的存储过程,形如:call 脚本文件名(参数1…参数N)。集算器的返回结果会以普通数据集的形式参与报表计算。<span style="color: black;">详细</span>用法可参考下列文档:集算器集成与应用之JasperReport集成 和集算器集成与应用之BIRT集成 。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">做为</span>专业的报表数据源工具,集算器还<span style="color: black;">能够</span>实现<span style="color: black;">更加多</span>的计算,下面分别举例。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">多源数据join后计算</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">Sales是mysql数据库中的表,存储着多名<span style="color: black;">营销</span>员<span style="color: black;">每日</span>的多个订单,其中字段SellerId是<span style="color: black;">营销</span>员编号。emp是mssql数据库中的表,存储着 <span style="color: black;">营销</span>员信息,其中字段EId是<span style="color: black;">营销</span>员编号。<span style="color: black;">此刻</span>需要在报表中展现:订单编号、日期、金额、<span style="color: black;">营销</span>员名字、<span style="color: black;">分部</span>名<span style="color: black;">叫作</span>,<span style="color: black;">要求</span>是:订单日期在<span style="color: black;">近期</span>N天(<span style="color: black;">例如</span>30 天)<span style="color: black;">或</span>订单属于某几个受关注的<span style="color: black;">分部</span>(<span style="color: black;">例如</span>Markeding和Finance)。部分源数据如下:</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">库表sales</p><img src="https://p3-sign.toutiaoimg.com/4875/2966501781~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=n7DeajNNS3zarixIBepR8E55sRM%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">库表emp</p><img src="https://p3-sign.toutiaoimg.com/4872/4759727679~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=3MhySE1uUQ7D0CFbO6ArVptvUtQ%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">集算器代码:</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">A1,A2:<span style="color: black;">查找</span>数据库,myDB1和myDB2分别直向mysql和mssql。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">A3:将A1中的SellerId字段替换成A2中对应的记录,<span style="color: black;">相关</span>字段为EId。A3的计算结果如下(蓝色字体<span style="color: black;">暗示</span>该数据项<span style="color: black;">包括</span>下级成员):</p><img src="https://p3-sign.toutiaoimg.com/4874/3571115267~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=VWAHFegp3sdrOTev1itXxZOXxBc%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">当A2中找不到对应的记录时,函数switch默认<span style="color: black;">保存</span>A1中记录,对应的SellerId<span style="color: black;">表示</span>为空,效果类似于左连接。<span style="color: black;">倘若</span>想进行内连接,应当<span style="color: black;">运用</span>选项@i,形如:A1.switch@i(SellerId,A2:EId)</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">A4:对<span style="color: black;">相关</span>结果进行过滤,第1个<span style="color: black;">要求</span>是:订单日期在<span style="color: black;">近期</span>N天(对应参数days),表达式为OrderDate>=after(date(now),days*-1)。第2个<span style="color: black;">要求</span>是:订单属于某几个受关注的<span style="color: black;">分部</span>(对应参数depts),表达式是depts.array.pos(SellerId.Dept)。运算符||<span style="color: black;">暗示</span><span style="color: black;">规律</span>关系“或”。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">函数after<span style="color: black;">能够</span>算出相对时间,函数array<span style="color: black;">能够</span>按分隔符将字符串拆分为集合。函数pos<span style="color: black;">能够</span>找出成员在集合中的位置。SellerId.Dept<span style="color: black;">暗示</span>SellerId字段对应的记录的Dept字段。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">Days和depts都是来自于报表的参数,<span style="color: black;">倘若</span>分别输入30、"Marketing,Finance",则A4的结果如下:</p><img src="https://p3-sign.toutiaoimg.com/4873/4599333904~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=kOowYWf9MG5VWHePJgwIDWe1Gsc%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">A5:从A4中取得报表需要的字段,<span style="color: black;">最后</span>计算结果如下:</p><img src="https://p3-sign.toutiaoimg.com/4878/1541011406~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=UZMcpOJYEGAP6%2B0TAunEBfhJlYw%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">结果集union</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">结果集ds1和ds2结构相同,分别来自mySQL和文本文件,需要将ds1和ds2纵向拼接起来再呈现为交叉表。源数据如下:</p><img src="https://p3-sign.toutiaoimg.com/4877/2015416213~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=SbEqPpRBm96kYmeR8D2KoCA6j78%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">集算器代码:</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">A3:纵向拼接两个数据集。报表工具只需呈现基于单数据集的简单交叉表。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">主子报表<span style="color: black;">都数</span>据源</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">针对</span>单数据源报表,<span style="color: black;">倘若</span>同一张报表中的主表和子表<span style="color: black;">运用</span><span style="color: black;">区别</span>的数据源,就需要显式传递数据库URL,或用JAVA类合并<span style="color: black;">区别</span>的数据源,前者安全性较低,后者代码<span style="color: black;">繁杂</span>。<span style="color: black;">运用</span>集算器<span style="color: black;">能够</span>方便地<span style="color: black;">处理</span>此类问题,下面用例子<span style="color: black;">来讲</span>明。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">设计一张主子报表,按薪酬范围<span style="color: black;">表示</span>每位员工的订单信息,主报表数据来自表emp(MySQL数据库),子报表数据来自表sales(MSSQL数据库)。部分源数据如下:</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">集算器代码:</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">empEsProc.dfx(该脚本文件用于主报表)</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">A1:按薪酬范围<span style="color: black;">查找</span>MYSQL数据库的表emp。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">salesEsProc.dfx(该脚本文件用于子报表)</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">A1:按员工ID从MSSQL的sales表<span style="color: black;">查询</span>相应的订单。<span style="color: black;">倘若</span>eid=1,则A1的计算结果如下:</p><img src="https://p3-sign.toutiaoimg.com/4873/4599611964~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=E%2BWT6BKxQbDOBR9F9PzeRXO1gOA%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">能够</span>看到,<span style="color: black;">区别</span>的数据源可被集算器合并为单一数据源,主子报表只需调用<span style="color: black;">区别</span>的集算器文件<span style="color: black;">就可</span>实现本<span style="color: black;">需要</span>。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">有些报表系统会存在多种数据源,各报表<span style="color: black;">运用</span>的数据源<span style="color: black;">区别</span>且经常<span style="color: black;">出现</span>变更,管理起来难度<span style="color: black;">很强</span>,<span style="color: black;">运用</span>集算器单一数据源<span style="color: black;">能够</span>降低管理难度。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">类似地,单一数据源还<span style="color: black;">能够</span><span style="color: black;">处理</span><strong style="color: blue;">子报表<span style="color: black;">都数</span>据源</strong>的问题,即:报表中存在多个子报表(或table控件),<span style="color: black;">每一个</span>子报表<span style="color: black;">运用</span><span style="color: black;">区别</span>的数据源。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">主子表动态<span style="color: black;">相关</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">主表<span style="color: black;">关联</span>的子表分布在多个数据库中,<span style="color: black;">需求</span>报表呈现这些数据源动态<span style="color: black;">相关</span>的结果。此类<span style="color: black;">需要</span><span style="color: black;">能够</span>用集算器简单实现,例如:</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">主 表org在数据源Master中,org里每条记录对应的子表在不同的数据源中,<span style="color: black;">例如</span>org.org_id=”ORG_S”时,这条记录对应的子表是数据 源S_odaURL的User表,org.org_id=”ORG_T”时,这条记录对应的子表是数据源T_odaURL中的User表。子表不止两个, 名字都是User,需要和主表动态<span style="color: black;">相关</span>再呈<span style="color: black;">此刻</span>报表中。<span style="color: black;">规律</span>上的关系如下:</p><img src="https://p3-sign.toutiaoimg.com/4877/2015694143~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=FgzLXW42w4LhHHIZ5yIcpwRQ%2BsU%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">集算器代码如下:</p><img src="https://p3-sign.toutiaoimg.com/4874/3571231986~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=N1Y85pr5EKCqWZKrLsm%2BlOOSSQY%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">A1:执行SQL,从数据源Master的org表取数据。arg1是来自报表的参数,arg1=”ORG”时A1的计算结果如下:</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">A2:依次循环A1中的记录,每次动态<span style="color: black;">相关</span>一个子表,并将<span style="color: black;">相关</span>结果合并在B2中。集算器用自然缩进来<span style="color: black;">暗示</span>循环语句的<span style="color: black;">功效</span>范围,即B2-B7,循环体中可用A2来引用循环变量,可用#A2来引用循环计数。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">B2:<span style="color: black;">按照</span>当前记录的org_id字段计算出对应子表的数据源名。<span style="color: black;">第1</span>次循环时,B2的计算结果为” S_odaURL”。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">B3:<span style="color: black;">按照</span>名字显式连接到数据源。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">B4:按<span style="color: black;">要求</span><span style="color: black;">查找</span>user表中的数据。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">B5:在子表B4中新增三列,数据来自主表。<span style="color: black;">例如</span><span style="color: black;">第1</span>次循环时,B5计算结果如下:</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">B6:将B5的计算结果合并到B1,运算符”|”等价于函数union。循环结束后B1会存储报表需要的完整数据,如下:</p><img src="https://p3-sign.toutiaoimg.com/4877/2015852014~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=%2F%2B6lGtliq0cBfZE1iBdmVP%2F2N%2FE%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">B7:显式关闭数据源连接。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">A8:将B1显式返回给报表工具(默认会返回最后一个单元格)</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">按照</span>参数<span style="color: black;">表示</span>时间精度<span style="color: black;">区别</span>的数据</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">报 表需要用折线图展现近期的<span style="color: black;">营销</span>额变化。unitType是报表参数,<span style="color: black;">表率</span>时间精度,当unitType等于"hour"时,需要展现近1小时内每五分钟的 <span style="color: black;">营销</span>额,当unitType="day"时,需要展现近1天内每小时的<span style="color: black;">营销</span>额,当unitType="week"时,需要展现近1周内<span style="color: black;">每日</span>的<span style="color: black;">营销</span>额。数据 <span style="color: black;">源自</span>于orders表,字段Amount是订单金额,t是订单<span style="color: black;">出现</span>时间。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">集算器代码:</p><img src="https://p3-sign.toutiaoimg.com/4878/1541500501~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1728092049&x-signature=s%2F3M44DGe5viAHosw428qHtmn9k%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">A1:空结果集,用来存储B2-B4产生的时间序列。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">A2-B2:<span style="color: black;">按照</span>报表参数unitType生成<span style="color: black;">区别</span>的时间序列,B2可生成近1小时内的12个时间点,<span style="color: black;">每一个</span>时间点间隔5分钟,B3生成近1天内的时间点,B4生成近1周的时间点。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">A5:循环A1,每次统计一个时间段内的<span style="color: black;">营销</span>额,"~"<span style="color: black;">暗示</span>A1的当前成员,"~[-1] "<span style="color: black;">暗示</span>上一个成员。当unitType="day",会生成12条记录的单字段结果集。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">A6:将A5<span style="color: black;">经过</span>JDBC传给报表。之后就<span style="color: black;">能够</span>按普通统计图的<span style="color: black;">办法</span>去呈现。</p>
你的努力一定会被看见,相信自己,加油。
页:
[1]