循序渐进使用cellReport报表1.docx
- 文档编号:11564768
- 上传时间:2023-03-19
- 格式:DOCX
- 页数:48
- 大小:1.06MB
循序渐进使用cellReport报表1.docx
《循序渐进使用cellReport报表1.docx》由会员分享,可在线阅读,更多相关《循序渐进使用cellReport报表1.docx(48页珍藏版)》请在冰豆网上搜索。
循序渐进使用cellReport报表1
循序渐进使用CellReport报表(不断完善中)
循序渐进使用CellReport报表(不断完善中)1
第一章概述2
一cellReport的由来2
二基本功能简介:
5
第二章各种报表的制作6
第一节基本概念6
一单元格6
合并单元格6
单元格属性的属性值和表达式6
二数据源7
三数据集7
命名参数和使用参数的两种方式7
1、使用#xxxx#类型的参数绑定:
8
2、使用$xxxx$类型的参数绑定:
8
在#xxx#和$xxxx$中使用表达式8
四宏8
第二节清单型报表9
进阶,动态sql片段的使用:
12
第三节分组报表12
第四节交叉表15
第五节多数据集归并的使用17
第六节数据缺失,但要补齐的报表20
第七节行列互换、明细数据竖排的报表22
第八节需要排名的报表23
第九节占比、同比、环比25
第十节一个数据集从多数据源取数的报表27
第十一节数据集之间的引用28
第十二节动态列28
第十三节动态划分28
第三章其他报表制作相关28
一值表达式和显示值表达式28
二报表的钻取、跳转、排序28
三固定表头29
第二节控制报表展现29
第三节成组报表的数据源设置和权限控制29
第四章报表基本概念30
第五章常用函数说明30
第一节数据集函数30
一集合函数30
group30
select31
scope33
二取值函数33
sum33
avg33
max33
min33
select133
第二节集合运算函数33
union_set并集33
intersection_set交集34
subtract_set以第一个分组为主集合,减去其他各集合的元素34
第三节单元格常用函数34
if34
case34
List35
fromto35
query35
range_count35
sum35
avg35
max35
min35
第一章概述
CellReport是一套专门面向程序开发人员制作复杂报表的工具系统,CellReport成功解决了复杂中国式报表的格式任意性及关系数据库的关联规律性之间的矛盾,使得用户能够方便简捷地制作自动化的任意报表,实现了报与表的完美结合。
cellReport采用了.net技术,设计器采用c#开发,引擎采用j#开发,可以运行在任何Windows操作系统下,支持所有ODBC及sqlserver本地连接的关系数据库。
cellReport提供报表运算的.net的API调用,可最紧密地和应用程序无缝结合,程序员可自定义参数输入界面与运算引擎结合。
一cellReport的由来
本报表工具是作者结合多年制作报表实践,结合《非线性报表理论》中提出的层次坐标和、java框架中的ORM工具ibatis中的sql语法以及其他报表的设计思想,发展而来的一个适合制作中国式复杂报表的软件工具。
作者多年从事保险公司的软件开发和维护,深知制作报表的艰辛。
作者所工作的单位,最初的统计报表完全就是填报、校验、审核、汇总而成。
但这种方式有很多不利于数据的准确和应用的方面:
一方面,由于要填充多张报表,而多张报表往往需要使用同一个数据,但填报的方案只能是由用户自己输入,然后校验,不能保证同一数据的一次录入,随处使用;
另一方面,不能做到对数据的重复使用。
因为这些数据存放到数据库或文件中后,已经是完全没有明确业务含义的数据,如果想利用这些数据重新制作新的报表,基本上是不可能的,唯一的办法就是手工制作报表。
这极大的限制了填报方案的应用。
一个简单的例子,单位需要的统计月报,一般都需要完成比例,而这就需要计划数,填报的方案要求每次制作报表都录入计划数,显然这是不合理的。
鉴于此,在2000年前后,同事开发了一个能够录入基础统计数据的软件,作者本人开发了一个在unix下的统计报表生成软件,可以根据这些基础数据生成上级公司需要的报表。
这样,极大的提高了报表填写工作的效率。
随着时间的推移,公司的各个业务系统已经全面铺开,各种统计需要的基础数据现在都可以从数据库中取得。
但公司的业务系统很多,即使一个简单的查询业务员的业务,也需要到很多数据库中去取数,这就是为什么cellReport制作之初就要求对多数据库支持的由来。
期间,作者开始制作公司的统计报表。
众所周知,统计报表是典型的中国式报表,表头复杂,取数逻辑复杂,一个统计报表很少有一个sql语句能完成的,通常情况下,需要用多个sql语句取数,然后在展现报表的时候,对这些数据集做适当的数据归并,重新计算才能得出正确的报表,而现在大部分的报表工具都不支持在展现报表的时候,做数据的归并。
比如常见的统计报表,都包含本期、累计、完成比例等,数据库中的数据通常是按天汇总后存放的,所以去本期和累计都是同一个表,一个sql肯定搞不定。
如果使用类似水晶、reporttingservice等报表工具,因为这些工具只能支持展现的时候使用一个数据集,所以必须要自己先用程序或存储过程加工到一个临时表中,然后才能用这些报表工具展现,。
这种方式也可以制作,但工作量十分巨大,可维护性基本为零,因为维护人员如果想修改该报表,必须通读程序,理解原作者的意思才能修改。
那么为什么不制作一个可以支持多数据集归并的报表呢,通过一个函数调用,就能做到,这样多好?
仅此一条,至少能够节省报表设计人员80%的工作量,何乐而不为呢?
这就是为什么cellReport要求支持多数据集取数和数据集归并的由来。
因此,作者在2004年开始在以前的工作基础上,制作java上的报表生成工具。
首先需要的就是ORM工具。
Java上的ORM工具很多,最有名的现在非hibernate莫属了。
但在作者本人看来,hibernate并不适合已经成功运行的系统,hibernate更适合一个全新系统设计之初就开始使用。
而象保险公司这种大型企业,每个数据库都是近千个表,即使是维护人员也不会对这些表的关系全部了解,更不用说建立起这些表的关系了。
最好作者选择了轻量级的ORM工具ibaits,一个半自动化的ORM工具。
它灵活的sql写法,非常适合遗留系统做查询使用。
通过使用类似#xxx#的方式调用用户的参数,完全实现了命名参数的功能,再也不用使用问号(?
)做参数,然后一个个绑定了,用问号绑定太累人了;或$xxx$的方式动态注入自己的sql片段,以及在sql中根据参数做各种判断,动态绑定特定的sql片段,这些都是非常方便的功能,为用户书写sql语句提供了最大化的自由性和可操控性。
这就是为什么cellReport要支持类似ibaits书写sql语句的由来。
有了这些最基本的要求,作者制作了一个工具包,在该工具包中,允许用户在制作报表中,连接任意多的数据库取数,允许任意多的sql查询,然后根据条件归并,可以在指定列上指定各个数据集间以及各列间的相互运算逻辑。
该软件在运行了两年后,由于一些原因,作者将该java上的报表软件迁移到了.net平台。
同年,接触到《润乾报表》作者写的《非线性报表理论》,其中对层次坐标的讲解使用非常实用,因此也被引进到了本软件中。
2007年8月,报表设计器的工作基本完成。
2008年,成功应用到了本公司的报表设计中,取得了极大的成功,报表制作效率和之前使用其他报表相比,至少提高90%。
尤其是对多数据集的支持和数据集归并,成了制作统计报表不可或缺的利器。
为什么要使用cellReport?
使用cellReport可以为用户带来什么样的好处?
凡是制作复杂统计报表的报表设计人员,看了以上讲解,应该能够明白该报表的特点是不是适用与自己。
二基本功能简介:
1、支持从多数据库以及.net中的DataSet、DataTable取数,以及自己组织数据
2、支持多数据集,以及独有的相关多数据集根据关键字求并集、交集、子集的运算。
为制作各种复杂的中国式报表提供了坚实的基础运行环境。
3、数据集之间的相互引用
4、支持一个sql数据集直接从多个数据库取数。
5、支持动态参数的sql语句,以及动态组合生成的sql(两种方式:
用户参数提供的sql片段和镶嵌可执行代码)
6、参数依赖计算
7、报表分片
8、单元格引用,自动判断循环引用
9、跨行组计算,支持累加、占比、移动平均等功能
10、支持所有的基本报表:
清单式、一次分组、多次分组、交叉表等
11、支持dashborad,同一页面显示多张相关或不相关报表
12、支持表格式报表和柱状图、线图等
13、任意单元格合并
14、可动态计算单元格、行、列及报表的基本属性
15、支持计算报表时,动态调用sql生成结果
16、通过URL连接方式,支持报表下钻、排序等
17、内嵌脚本,也可以用c#,java,等写自定义函数脚本。
第二章各种报表的制作
第一节基本概念
一单元格
我们运行ReportDesign.exe编辑器,类似excel。
点击新建报表菜单,即可出现如下界面:
新建的报表由行列整齐的格子组成,这些格子我们称为单元格,通过所有单元格的值和边框排列在一起,组成了报表。
合并单元格
我们选中一片连续的单元格区域,点击鼠标右键,会弹出一个快捷菜单,点合并单元格,就把这些被选中的单元格合并成了一个单元格。
单元格属性的属性值和表达式
在单元格的属性列表中,大部分属性都是可以定义表达式,在显示报表的时候,动态计算属性的具体值。
可计算的表达式都是以等号开始的字符串。
例如:
在单元格的背景色的属性值中定义为red,那么报表运行时,它就是红色,不会被运算,不会被改变。
如果定义为=iif(@value>100,’red’,’blue’),那么在显示报表的时候,如果当前值大于100,背景色将为red,否则为blue。
在属性表达式中可以用“@value”来指代当前单元格的数据值。
二数据源
数据源是与数据库的ODBC连接或SqlClient连接,用于在报表设计器中创建数据集时选择数据表、字段等,以及预览设计好的报表时从数据库中读取数据。
在菜单项:
数据集—》数据源定义中。
见下图:
可以同时定义多个数据源。
三数据集
数据集就是定义报表数据来源的一个SQL语句。
可以同时定义多个数据集。
每个数据集都可以指定自己的数据源。
Sql语句中,用类似#xxxx#代表参数,用$xxxx$代表注入的sql片段。
Sql语句支持动态的sql语句。
我们在查询时,往往会发生这种情况:
参数param为某个值时,用一个sql查询,为另一个值时,使用类似的sql,但又有部分不同。
更常见的情况是,某个参数没有输入值或为空是,查询全部,如果有输入值时,查询特定的数据。
实现是这样做的:
Selecta,bfromtable1where1=1
这个sql就会根据parm参数是否为空,执行不同的sql。
param为空时,执行:
Selecta,bfromtable1where1=1
param不为空时,执行:
Selecta,bfromtable1where1=1anda=#param#
命名参数和使用参数的两种方式
参数有以下几个特征:
1、参数有数据类型
2、参数有缺省值
3、可以定义显示查询界面时使用的可选择数据,比如某个项目代码作为参数,在定义该参数的可选参数时,可以直接指定可选数据,也可以选择从sql语句中选择显示值和实际值。
4、参数可以定义缺省值的公式,可以引用已定义的参数,要避免参数的循环引用。
参数类型:
内部。
内部参数都是延迟计算的。
内部参数是不显示在查询条件中的,一般都是做为中间变量使用的。
为程序控制添加了强有力的中间控制环节。
在报表中所有的参数也是作为一个数据集对待,该数据集叫param,每个参数都是作为该数据集的一个字段属性出现。
这样在报表单元格中引用的时候,直接使用param.xxx即可。
如果是在sql数据集中引用参数,有两种方式:
#xxx#或xxxx$,区别是:
使用#xxxx#时,sql语句先做预处理,然后将该命名参数绑定到sql上;使用$xxxx$时,是讲$xxxx$所代表的字符串直接和sql语句合并,然后在提交数据库处理。
例子:
假如现在定义了一个参数交curDate,假定用户输入的值为2008-02-02
1、使用#xxxx#类型的参数绑定:
selecta,bfromtable1wherecurdate=#CurDate#
处理机制为:
先将“selecta,bfromtable1wherecurdate=?
”提交数据库引擎做预处理,然后将参数CurDate的实际值绑定到该语句,提交数据查询。
2、使用$xxxx$类型的参数绑定:
selecta,bfromtable1wherecurdate=`$CurDate$`
处理机制为,先将$CurDate$绑定到sql,这样sql就变成了:
selecta,bfromtable1wherecurdate=`2008-02-02`,然后将该sql提交数据库查询。
这样做的好处是:
可以由用户动态指定sql片段,然后执行。
这样做,会存在sql注入的问题,我们可以通过其他方式来防止这样的事情发生。
定义一个用户可输入的参数,再定义一个内部参数,该内部参数根据用户输入的值,选择不同的sql片段,然后再sql语句使用的时候,使用该内部参数。
第二节清单型报表
本节将讲述怎样制作一张清单型报表,以及怎么使用参数,和动态sql语句片段的应用。
下面我们来制作一张最简单的报表——雇员信息表:
要求列出所有员工的信息。
输入员工的ID,然后生成该员工的信息表。
新建数据源,然后建立数据集,数据集名称为ds1,sql语句为:
SELECT雇员.雇员ID,雇员.姓氏,雇员.名字,雇员.职务,雇员.尊称,雇员.出生日期,雇员.家庭电话FROM雇员,如下图所示:
下一步选菜单,工具-》简单报表向导,数据集选刚定义的ds,字段选除了rowno外的全部字段,点明细旁边的按钮>>,全都加入到明细中。
如下图:
点击确定,现在可以看到报表设计页面上,已经生成了一个报表,如下图所示:
点html预览界面如下:
如果想按雇员ID查数据,我们需要修改sql语句为:
SELECT雇员.雇员ID,雇员.姓氏,雇员.名字,雇员.职务,雇员.尊称,雇员.出生日期,雇员.家庭电话FROM雇员where雇员ID=#雇员ID#
然后选菜单,参数定义,点按钮:
装入已使用参数,这时候可以看到刚才在sql中使用的命名参数:
雇员ID已经在参数列表中出现。
选参数:
雇员ID,数据类型选int。
点html预览,弹出输入参数的画面,您定义的参数,会根据数据类型、可选值等动态添加到该画面上,让用户输入可选值。
如下图:
参数输入2,点提交,结果如下:
进阶,动态sql片段的使用:
实际使用中,常常有这种需求:
如果不输入某个条件,就当这个条件不存在,查全部的数据;如果输入条件存在,就只查满足某个条件的数据。
比如我们现在的报表,现在已经加上了一个雇员ID的参数,如果用户不输入雇员ID,那么就查全部的数据,如果输入,就仅仅查该雇员的数据,这种需求,需要如下修改sql语句:
SELECT雇员.雇员ID,雇员.姓氏,雇员.名字,雇员.职务,雇员.尊称,雇员.出生日期,雇员.家庭电话
FROM雇员where1=1
$if(isEmpty(param.雇员ID)==false){$
And雇员ID=#雇员ID#
$}$
通过在两个$间加入设当的编程语句,可以帮助sql语句的生成。
第三节分组报表
本节重点:
讲解怎么制作分组报表,怎么计算分组内的统计数据等。
我们即将制作一张关于销售产品的销售额的分组报表。
数据集名:
数据集1
对应Sql:
SELECT[类别].[类别名称],[产品].[产品名称],Sum(CCur([订单明细].[单价]*[数量]*(1-[折扣])/100)*100)AS产品销售额,DatePart('q',[发货日期])&'季度'AS发货季度
FROM类别INNERJOIN(订单INNERJOIN(产品INNERJOIN订单明细ON[产品].[产品ID]=[订单明细].[产品ID])ON[订单].[订单ID]=[订单明细].[订单ID])ON[类别].[类别ID]=[产品].[类别ID]
WHERE((([订单].[发货日期])Between#起始日期#And#end_date#))
GROUPBY[类别].[类别名称],[产品].[产品名称],DatePart('q',[发货日期])&'季度';
参数定义中,装入参数,设置两个参数都为日期类型,参数起始日期的缺省参数值为1997-01-01,end_date的缺省值为1997-12-31.
选菜单:
工具-》简单报表向导,数据集选数据集1,将类别名称、产品名称都加入到行分组中,将产品销售额加入到明细中,右键选明细中的产品销售额,选求和。
点确定,这样一样基本的二次分组就制作完毕。
如下图:
公式:
a2:
=数据集1.group(数据集1.类别名称)行扩展
b2:
=数据集1.group(数据集1.产品名称)行扩展
c2:
=数据集1.sum(数据集1.产品销售额)
html预览(部分结果):
下面加上小计、合计等统计数据。
选单元格a2、a3,合并单元格,复制c2单元格的公式,粘贴到c3中,这就是类别的小计,在c4中也粘贴进该公式。
如图:
c3,c4公式:
=数据集1.sum(数据集1.产品销售额)
html预览:
这是直接从数据集中汇总数,也可以对单元格汇总,这时候的公式为:
c3公式为:
=sum(c2{})
c4公式:
=sum(c2{})或=sum(c3{})
为什么不同地方的单元格公式一样,但求值不一样呢?
原理以后讲。
第四节交叉表
仍以分组报表中的sql语句为例。
选菜单:
工具-》简易报表向导。
如下图:
结果页面:
b1:
=数据集1.group(数据集1.发货季度)扩展方向:
列扩展
a3:
=数据集1.group(数据集1.类别名称)扩展方向:
行扩展
b3:
=数据集1.sum(数据集1.产品销售额)
html预览:
下面加上合计
新加公式:
c3,c4,b4公式:
=数据集1.sum(数据集1.产品销售额)
c4值为总合计
html预览:
行分组和列分组上都可以有任意多个分组。
一个行分组可以和任意多个平行的列分组交叉。
反之亦然。
举例:
数据集1对应的sql:
SELECT类别.类别名称,雇员.姓氏+雇员.名字AS雇员名字,运货商.公司名称AS运货商名称,订单.订购日期,CCur([订单明细].[单价]*[数量]*(1-[折扣])/100)*100AS金额
FROM运货商INNERJOIN(类别INNERJOIN(雇员INNERJOIN(订单INNERJOIN(产品INNERJOIN订单明细ON产品.产品ID=订单明细.产品ID)ON订单.订单ID=订单明细.订单ID)ON雇员.雇员ID=订单.雇员ID)ON类别.类别ID=产品.类别ID)ON运货商.运货商ID=订单.运货商
公式:
b3:
=数据集1.group(数据集1.类别名称)列扩展
d3:
=数据集1.group(数据集1.运货商名称)列扩展
a4:
=数据集1.group(数据集1.雇员名字)行扩展
b4,c4,d4,e4:
=数据集1.sum(数据集1.金额)
html预览(略)
第五节多数据集归并的使用
制作统计报表,往往不是一个sql语句能搞定的。
●有些报表需要从多个数据库取数;
●有些虽然是从一个数据库取数,但数据是分散在数据库的很多表中,需要多个sql语句组织有关数据,然后按照某些关系归并在一起;
●有些报表虽然是一个基础表,但对该表中的数据要经过不同的加工,一句sql语句不能将所有的报表要展现的数据组织完毕。
比如统计报表中常见的本期、累计、完成比例。
在数据库中,基础数据是按照一天天的发生数存在表中,本期为月初到查询日期的累计发生数数,累计为从年初到查询日期的累计发生数,完成比例为累计除上计划数,计划数肯定要在另一个表中单独存放。
本期和累计虽然都是一个表,很明显因为条件的不同,一句sql不可能做到同时取出本期和累计。
这样的统计报表最少需要3个sql取数才能将报表上所需要的数据全部取出,然后按照特定的条件(比如按机构)将三个数据集的归并,之后才能展现出正确的报表。
传统的报表工具(如水晶为代表的国外报表工具),都是以单一数据集为基础的报表生成工具,如果你需要用到很多sql才能产生出一张统计报表,那么,你必须通过建临时表,通过写存储过程等办法,将分散在数据中的数据组织到一个数据库表中,然后通过报表工具展现出来。
这样的做法,也能解决中国式报表的生成问题,但报表的编写和维护量将非常大,而且报表逻辑不直观,一旦客户需要更改报表格式和逻辑,将是一件非常痛苦的事情。
对于这样的报表工具而言,做报表就是写程序,报表工具沦落为最普通的展现工具。
现在国内有些报表工具也有很多号称支持多数据集,但在做这样的统计报表时,一般都是单元格将各个数据集联系起来,无论是在报表生成效率和直观易用方面,都比较差。
在cellReport中,建设性的加入了关于集合的运算功能,可以对各个数据集按照指定的条件分组归并,从而极大的减低开发统计报表的难度。
本报表软件的集合的运算包括:
并集、交集、以及集合的减法。
最常用的就是求集合的并集。
下面以northWind数据集中的订单数据,制作一张中国人常用的统计报表。
本报表将会用到报表参数的计算。
数据集:
本期:
SELECT[类别].[类别名称],[产品].[产品名称],Sum(CCur([订单明细].[单价]*[数量]*(1-[折扣])/100)*100)AS产品销售额
FROM类别INNERJOIN(订单INNERJOIN(产品INNERJOIN订单明细ON[产品].[产品ID]=[订单明细].[产品ID])ON[订单].[订单ID]=[订单明细].[订单ID])ON[类别].[类别ID]=[产品].[类别ID]
WHERE((([订单].[发货日期])Between#month_begin_date#And#end_date#))
GROUPBY[类别].[类别名称],[产品].[产品名称]
累计:
SELECT[类别].[类别名称],[产品].[产品名称],Sum(CCur([订单明细].[单价]*[数量]*(1-[折扣])/100)*100)AS产品销售额
FROM类别INNERJOIN(订单INNERJOIN(产品INNERJOIN订单明细ON[产品].[产品ID]=[订单明细].[产品ID])ON[订单].[订单ID]=[订单明细].[订单ID])ON[类别].[类别ID
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 循序渐进 使用 cellReport 报表
![提示](https://static.bdocx.com/images/bang_tan.gif)