笔者曾参加过日照港物资MIS和生产MIS的开发工作。在实际开发过程中，利用Oracle前端开发工具之一Oracle*Reports做过许多矩阵报表。所谓矩阵报表是由一个列记录值、一个行记录值和一个cell组，这组内的每一个格包含了行与列相关的数据。
笔者认为矩阵报表实现的要害在于组织数据。
一、利用视图组织矩阵报表：
1、本例假设有一物资资金统计表，其格式如下：
项目 类别 期初 库存 本期 增加 本期减少 一公司 二公司 三公司 ... 期末库存
黑色金属
有色金属
五金制品
非金属建材
橡塑
工具
劳保
杂品
机电设备
该表横向显示“期初库存”、“本期增加”、“本期减少及消耗去向”、“期末库存”；纵向显示物资类别，是一矩阵报表。与该报表有关的数据基表有以下几个：
.RQ：存放统计日期，其数据结构为：
字段名 含义 长度 类型 备注
YF 统计月份 6 CHAR 格式：YYYYMM
.LBXX：存放物资类别信息，其数据结构为：
字段名 含义 长度 类型 备注
LBDM 类别代码 2 CHAR 取值范围01～09
LBMC 类别名称 20 CHAR
.DWDM：存放领料单位代码，其数据结构为：
字段名 含义 长度 类型 备注
DWDM 单位代码 4 CHAR 取值范围0101～0199
DWMC 单位名称 20 CHAR
.YDKCXX：存放库存月度备份信息，其数据结构如下：
字段名 含义 长度 类型
YDH 月份 6 CHAR
LBDM 类别代码 2 CHAR
KCJE 月底库存 12，2 NUMBER
YRJE 月入金额 12，2 NUMBER
YCJE 月出金额 12，2 NUMBER
.XHQX：存放资金消耗去向信息，其数据结构如下：
字段名 含义 长度 类型 备注
RQ 领料日期 10 DATE
WZDM 物资代码 9 CHAR 前两位代表物资 所属大类
DWDM 单位代码 4 CHAR
XHJE 消耗金额 12，2 NUMBER
2、利用视图组织矩阵报表的过程：
CREATE VIEW V_ZHBB1
AS SELECT LBDM,(KCJE-YRJE+YCJE)"QCJE" FROM YDKCXX,RQ WHERE YDH=YF;
CREATE VIEW V_ZHBB2
AS SELECT LBDM,YRJE"BQZJ" FROM YDKCXX,RQ WHERE YDH=YF;
CREATE VIEW V_ZHBB3
AS SELECT LBDM,YCJE"BQJS" FROM YDKCXX,RQ WHERE YDH=YF;
CREATE VIEW V_ZHBB4
AS SELECT LBDM,KCJE"QMJE" FROM YDKCXX,RQ WHERE YDH=YF;
CREATE VIEW V_ZHBB5
AS SELECT DWDM,SUBSTR(WZDM,1,2)"LBDM",SUM(XHJE)"LLJE"
FROM XHQX,RQ
WHERE TO_CHAR(RQ,’YYYYMM’)=YF
GROUP BY DWDM,SUBSTR(WZDM,1,2);
CREATE VIEW V_ZHBB AS
SELECT 0.1"DWDM",'期初金额'"DWMC",V_ZHBB1.LBDM,LBMC,SUM(QCJE)"JE"
FROM V_ZHBB1,LBXX WHERE V_ZHBB1.LBDM=LBXX.LBDM
GROUP BY V_ZHBB1.LBDM,LBMC
UNION
SELECT 0.2"DWDM",'本期增加'"DWMC",V_ZHBB2.LBDM,LBMC,SUM(BQZJ)"JE"
FROM V_ZHBB2,LBXX WHERE V_ZHBB2.LBDM=LBXX.LBDM
GROUP BY V_ZHBB2.LBDM,LBMC
UNION
SELECT 0.3"DWDM",'本期减少'"DWMC",V_ZHBB3.LBDM,LBMC,SUM(BQJS)"JE"
FROM V_ZHBB3,LBXX WHERE V_ZHBB3.LBDM=LBXX.LBDM
GROUP BY V_ZHBB3.LBDM,LBMC
UNION
SELECT TO_NUMBER(V_ZHBB5.DWDM)"DWDM",DWMC,V_ZHBB5.LBDM,LBMC,LLJE"JE"
FROM V_ZHBB5,DWDM,LBXX
WHERE V_ZHBB5.DWDM=DWDM.DWDM AND V_ZHBB5.LBDM=LBXX.LBDM
UNION
SELECT 200"DWDM",'期末金额'"DWMC",V_ZHBB4.LBDM,LBMC,SUM(QMJE)"JE"
FROM V_ZHBB4,LBXX WHERE V_ZHBB4.LBDM=LBXX.LBDM
GROUP BY V_ZHBB4.LBDM,LBMC;
至此该矩阵报表的数据组织完毕，建立矩阵报表查询时直接引用视图V_ZHBB即可。
3、需要注重的问题：
（1） 由于UNION操作当有两行完全相同的记录时，只取其中的一行，这样就造成汇总数据不准确，所以设计视图时必须避免这种情况的发生。其方法是：在同一查询中，可加入主键字段加以区分；在不同查询中，可加入一个不同值的常量来区分。
（2） 当所建视图的级数或UNION次数太多时，系统会出现“打开游标数太多”的错误。这时就需要用一个中间表将某一级视图数据转储一下，接下来所建视图再引用中间表的数据。
（3） 在每次生成报表之前，必须先删除表RQ中的原有日期，将新的统计日期追加进去并提交，再删除中间表的内容，然后将需转储的视图数据追加进去并提交。
2、 结论：
利用视图组织矩阵报表，其思路是将报表中的列利用视图旋转成行，然后再用并操作(UNION)将这些行连接起来。对于列值来自两个以上的数据表或列数不确定(如上例中每月资金消耗去向)的报表，这种方法非常有效，并且在维护报表时不需要修改程序。
以上只是一个比较简单的例子.
|
