第三章 创建一个分组报表
数据模型用于定义一个报表中使用的数据及数据结构,这些数据可以是数据库表中的原始数据,也可以是经过加工后的数据,如工资的总和等。从数据库中检索出的数据是否在报表输出中显示,也是在数据模型中定义的。
数据模型由五部分对象组成:查询(Queries)、组(Groups)、列(Columns)、连接(Links)和参数(Parameter)。
1) 建立查询
查询实际上就是一条SQL SELECT语句,决定了从数据库中指定的表或视图中取哪些行和列。查询所取得的数据可被用于计算、排序、集合运算等,并可以在报表输出时显示。
Ø 报表中的查询可分为两种:内部查询和外部查询。两者的区别在于外部查询可以被其他报表多次引用,而内部查询不能被其他报表参考引用,只能被其所属的报表引用。
Ø 根据报表中查询的个数可以将报表分为单查询报表和多查询报表。
单查询报表:只有一个查询的报表称为单查询报表。单查询报表的常见格式有列表式(Tabular)、标签式(Mailing Label)、信封式(Form Letter)以及分组式。单查询报表虽然只有一个查询,但可以多次显示查询数据,并且可以用不同的格式显示。
多查询报表:含有多个查询的报表称为多查询报表。多个查询相互之单可以有关联,称为相关的多查询报表;也可以没有关联,称为不相关的多查询报表。
l 相关的多查询报表:多个查询之间相互关联,一般用于主从式报表,也就是说,报表的一部分数据决定了报表的另一部分数据。取出前一部分数据的查询称为主查询,取出后一部分的查询称为从查询,主查询的每一条记录查询都将引起从查询的执行,从查询只检索与主查询匹配的那些记录。两个查询之间通过定义数据连接产生联系。
l 不相关的多查询报表:多个查询之间没有关联,Oracle Reports将独立地为每一个查询获取数据,显示出来的是一系列不相关的数据。
查询之间运行的先后次序由数据模型中的查询次序决定。在数据模型中,查询次序是先上后下,先左后右,所以放在左上角的查询最先执行。
示例:为避免数据冗余,我们这里将订单头和行分别建立两个查询:
a) 在对象管理器中选中报表,右击打开“报表编辑器”,然后点击打开“数据模型”。
b) 点击SQL查询创建两个查询。
c) 修改Q1->Q_header,G_order_number->G_header;
Q2->Q_line,G_line_num->G_line
2) 建立数据连接
数据连接用于建立两个查询数据之间的关系。在数据模型编辑器中,两个查询之间的连线即表示了一个数据连接。
工作原理:数据连接通过主键和外部键定义了两个查询之间的主/从关系。主/从关系中父查询的每一条记录的产生都会引起子查询的执行,子查询只检索满足在连接中指定的条件的记录。
通常情况下,两个查询之间的连接是等值连接,但也可以采用SQL语句(如WHERE、HAVING、START WITH等)建立非等值连接。
当一个带有数据连接的报表执行时,该数据连接就被转换成一个SQL子句,并添加到子查询的后面。子查询根据父查询的主链值执行查询。
建立方法:一种是手动建立的数据连接,一种是自动建立的数据连接。
手动连接的步骤:在数据模型编辑器中选择:“数据连接”小图标;
单击父查询的主链列或外部键列,并拖动鼠标到子查询的主链或外部键列,使它们之间建立一条连线。注意,连线总是从父查询到子查询的;
自动连接的步骤:在数据模型编辑器中选择:“数据连接”小图标;
单击父查询,并拖动鼠标到子查询上,系统会自动显示父查询和子查询之间是依据哪个列建立连接的;
示例:点击数据连接,创建从Q_header (header_id)到Q_line(header_id1)的数据连接。
3) 建立组
组对象主要有两个功能:
Ø 将一个查询的数据分成几个集合,每个集合称为组。例如,将“EMP”表按“DEPTNO”分组,结果显示如下:
Ø 过滤查询数据,通过组可以对查询的结果进行过滤。
在Oracle Reports中将组分为缺省组和用户定义组两类:
Ø 在缺省的情况下,Oracle Reports为数据模型中的每一个查询产生一个组,取名取成查询的名称。查询名称以“Q_”开头,而组名以“G_”开头。例如,查询名称为“Q_header”,则Oracle Reports为其产生的缺省组名为“Q_header”。
Ø 用户定义组可用于以下三种情况:分组报表;矩阵报表;子汇总。
组过滤器允许有条件的选择由查询检索出来的数据,通过组过滤器过滤查询结果。
有两种类型的组过滤器:Oracle Reports封装过滤器和用户定义过滤器。
Ø Oracle Reports封装过滤器允许指定在一个组中希望检索的记录数,在报表开发过程中可以使用封装过滤器来限制数据。Oracle Reports软件包中有两个封装过滤器:First Filter,只显示组内前n条记录;Last Filter,只显示组内后n条记录。
Ø 用户定义过滤器:用户可以定义自己的过滤器来限制在特定条件下的记录的检索。用户定义的过滤器是通过编写PL/SQL代码建立的。
数据模型中组的层次决定了在缺省布局对话框中组的次序,同时也决定了在缺省布局中组的次序,但这不是最终的报表格式,因为可以对缺省的显示格式进行修改。
在数据模型编辑器中若没有数据连接,则组的层次是由组在数据模型编辑器中的位置从左至右,从上至下决定的。在数据模型编辑器中若有数据连接,则组的层次可以由数据连接描述。通过对象导航器可以清晰地看到组的层次:
4) 定义列
报表的列表示报表的数据。在定义了从数据库检索数据的查询后,可能需要进一步调整报表,增加更多的列,而这些列可以用作报表的数据,执行总计汇总等功能。
Oracle Reports中的列可以分为两类,缺省列和用户定义列。
Ø 缺省列
Oracle Reports为查询SELECT语句中的每一项都建立一个列,因为它们直接对应从数据库中检索出来的各个列,所以缺省列也叫数据库列。
Oracle Reports除了支持典型的数据类型的列(如字符型、数值型、日期型)以外,还支持图型列。图型列的值可能是:
直接存储在数据库中的图型:这种列的数据类型通常是RAW或LONG RAW。在这种情况下,需在列的属性选项板中指明图形格式。
文件名或者是图型的URL:这种列的数据类型通常是REF,列中的值对应于用指针指向一个存在操作系统中的文件,报表输出时会根据指针将文件内容输出,在这种情况下,需在列的属性选项板中将“Read from File”设置为“Yes”并输入文件名。文件名前可以带有路径,若文件名前没有带有路径,则Oracle Reports会按照其路径搜索顺序查找文件。可以通过Report Builder的环境变量REPORT30_PATH来设置搜索路径。注意,若列的属性选项板中“Read from File”被置为“No”并且输入了文件名,则报表最后输出的只会是文件名,而不会是文件内容。
Oracle Reports支持多种图形格式,包括BMP、CALS、CGM、GIF、JFIF、PCD、PCX、PICT、RAS、TIFF等。
Ø 用户定义列
用户可在特定的组内或报表一级建立列。在特定的组内建立的列称为组一级的列,该列与组内的其他列有相同的显示频度。而报表一级的列只在整个报表中显示一次。用户定义列分为三种类型:公式列、占位列和汇总列。
a) 公式列,公式列是对其他列执行一个用户定义的计算。建立公式列的方法是用PL/SQL语句写一个函数,从一个或多个列中计算结果,该列的缺省名为“CF_n”。
示例:在G_line组中定义一个的公式列newAmount,来显示Amount *1.06的值。
在数据模型编辑器中单击:“公式列”小图标。如果要在组中建一个公式列,则在画布的组区域内单击一下鼠标左键,如果要建一个报表一级的公式列,则在画布的空白区域内单击一下鼠标左键,会生成一个公式列对象;
双击该公式列对象,弹出其属性选项板;
点击属性选项板中的“PL/SQL公式”项,弹出“程序单元编辑器”;
在“程序单元编辑器”中为公式项用PL/SQL语句书写一个计算公式并编译通过。
b) 占位列,占位列的值和数据类型是通过PL/SQL语句设置的,该列的缺省名为“CP_n”。当需要有选择地设置一个列的值时(例如,在每次出现第n个记录时,或每次出现包含有特殊值的记录时)占位列非常有用。
可以在以下几个地方设置占位列的值:
在Before Reports的报表触发器中(如果占位列是一个报表级的列);
在报表级的公式列中(如果占位列是一个报表级的列);
在占位列组或占位列组下面的公式中(为组中的每一条记录设一次值)。
占位列的用途:
在公式列的PL/SQL代码中计算出的一些值可以分配给不同的占位列;
利用占位列保存中间值。例如,存储当前检索出的最高工资对应的记录。
c)