记录下我的第一个RS 报表

RS报表需要的知识

RS报表的简单开发相对来说还是很容易上手的,也就比Word+Excel难上一点点,基本上有一点的SQL知识和高级语言知识就能够很快的把现有的数据格式化为你需要的报表.

数据处理-SQL部分

需求:通过工单号查询产品的维修信息,包含Keypart的更换,不良现象及维修方式等信息.

例中的表结构及关系

重要SQL介绍

可能仅适用于ORACLE
除了基本的SELECT JOIN GROUP 之外我们需要用到聚合,递归查找,为提高效率
还会用到子查询.

子查询 WITH AS

WITH … AS … 可以看作构造个临时表,以供后面复用.
如果多次扫描一个大表,成本会很高.先筛选出来后再多次复用,效率就会高很多.
用法:

--建单个临时表
with e as (select * from scott.emp e where e.empno=7499)
select * from e;
 
--建多个临时表
with
     e as (select * from scott.emp),
     d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;

递归查询 CONNECT BY 及相应伪列

CONNECT BY PRIOR 是ORACLE中提供的递归算法,用于层次化查询.

参数

START WITH: 指定起始节点的条件
CONNECT BY: 指定父子行的条件关系
PREIOR: 查询父行的限定符，格式: prior col1 = col2 或 col1 = prior col2 用来限定递归查询的当前节点条件
prior col1 = col2 即将每次查询到col1的值赋予下次查询的col2,并将col2作为查询条件.
NOCYCLE: 让数据中存在LOOP也可以查询,配合CONNECT_BY_ISCYCLE可以看到哪些行引起循环.

伪列

LEVEL
层级,ROOT为1,子层累加
CONNECT_BY_ISLEAF
The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition. Otherwise it returns 0.
简单的说就是在递归查询中当前行没有后代的就1,有后代就是0
CONNECT_BY_ISCYCLE
The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.
如果当前记录有个子节点也是自己的祖先节点时返回1,其它返回0
比如记录中张三有个子点李四,但李四又是张三的祖先,那这一行对应的伪列返回1
CONNECT_BY_ROOT
返回ROOT记录
SYS_CONNECT_BY_PATH
返回到当前纪录从ROOT下来的路径
举个例子:

CREATE GLOBAL TEMPORARY TABLE TEMP_EMPS(ID NUMBER,NAME VARCHAR(20),MANAGERID NUMBER) ON COMMIT DELETE ROWS;
INSERT INTO TEMP_EMPS VALUES (1,'老祖',10);
INSERT INTO TEMP_EMPS VALUES (2,'大高',1);
INSERT INTO TEMP_EMPS VALUES(3,'二高',1);
INSERT INTO TEMP_EMPS VALUES(4,'三高',1);
INSERT INTO TEMP_EMPS VALUES(5,'大曾',2);
INSERT INTO TEMP_EMPS VALUES(6,'二曾',2);
INSERT INTO TEMP_EMPS VALUES(7,'三曾',3);
INSERT INTO TEMP_EMPS VALUES(8,'大爺',6);
INSERT INTO TEMP_EMPS VALUES(9,'二爺',6);
INSERT INTO TEMP_EMPS VALUES(10,'三爺',7);
INSERT INTO TEMP_EMPS VALUES(11,'四爺',5);
INSERT INTO TEMP_EMPS VALUES(12,'五爺',6);
INSERT INTO TEMP_EMPS VALUES(13,'六爺',7);
INSERT INTO TEMP_EMPS VALUES(14,'大哥',8);
INSERT INTO TEMP_EMPS VALUES(15,'二哥',9);
INSERT INTO TEMP_EMPS VALUES(16,'三哥',10);
INSERT INTO TEMP_EMPS VALUES(17,'四哥',11);
INSERT INTO TEMP_EMPS VALUES(18,'五哥',12);
INSERT INTO TEMP_EMPS VALUES(19,'六哥',13);

不带NOCYCLE查询

SELECT LEVEL,NAME FROM TEMP_EMPS
START WITH NAME='老祖'
CONNECT BY PRIOR ID=MANAGERID;

因为存在循环,会有如下报错

ORA-01436: CONNECT BY loop in user data
01436. 00000 -  "CONNECT BY loop in user data"
*Cause:    
*Action:

带NOCYCLE,配合CONNECT_BY_ISCYCLE查询

SELECT LEVEL,NAME,
CONNECT_BY_ROOT NAME MANAGER,
SYS_CONNECT_BY_PATH(NAME, '->') PATH ,
CONNECT_BY_ISCYCLE ISCYCLE ,CONNECT_BY_ISLEAF ISLEAF 
FROM TEMP_EMPS
START WITH NAME='老祖'
CONNECT BY NOCYCLE PRIOR ID=MANAGERID
ORDER BY LEVEL;

分析函数&聚合函数

分析函数 OVER

OVER用于分组计算,通过子句PARTITION BY实现分组功能,但和GROUP BY还是有点差别.
可以这样理解,GROUP BY是分组一次取出,OVER(PARTITION BY) 是分组并一步步取出,所以OVER能实现的功能比GROUP BY会强太多.
还有很重要的一点是可以通过 OVER(PARTITION BY col ORDER BY col ) 组内排序,GROUP BY只能分组后排序.

聚合函数

这里用到了一个比较特殊的聚合函数WM_CONCAT,它的作用是把行转列用逗号连接

报表SQL

WITH 
TMPRUNCARD AS (     --取得工單信息及相應的流程卡
      SELECT R.CREATE_TIME,R.SN,R.SN_KEY,W.PART_NO,W.MODEL,K.SEGMENT01 AS CURRENTKP,R.CREATE_TIME AS BTIME,R.TIME
      FROM WIP_RUNCARD R,WIP_WO W,KP_ALL_KP_FLEX K
      WHERE (W.WO_NO='**输入参数**') AND (K.ORG_ID=70 AND K.VIEW_KEY = 43)  AND R.WO_KEY=W.WO_KEY AND K.SN_KEY=R.SN_KEY
),
TMPKP AS (     --取得相應的原始KP
SELECT SN_KEY,OLD_DATA AS ORIGINALKP FROM (
    SELECT LEVEL,SN_KEY,NEW_DATA,OLD_DATA,CONNECT_BY_ISLEAF X   
    FROM SFCS_KP_SWAP_LOG
    WHERE SN_KEY IN (SELECT SN_KEY FROM TMPRUNCARD)
    CONNECT BY PRIOR OLD_DATA=NEW_DATA
    START WITH NEW_DATA IN (SELECT CURRENTKP FROM TMPRUNCARD)
) WHERE X=1
)

SELECT  TR.CREATE_TIME AS  RMADATE,
 TR.SN AS SN,
 TR.PART_NO AS PN,
 TR.MODEL,
 NVL(TK.ORIGINALKP,TR.CURRENTKP) AS ORIGINALKP,
 TR.CURRENTKP ,
 TR.BTIME AS STARTTIME,
 TR.TIME AS COMPLETIONDATE,
 NVL(TO_CHAR(WMSYS.WM_CONCAT(CASE WHEN LT3.CODE LIKE 'C%' THEN LT3.CHINESE ELSE '' END ) OVER (PARTITION BY DF.SN_KEY)),'無客訴不良記錄') AS CSRDEF,
 NVL(TO_CHAR(DF.I_TIME,'YYYY-MM-DD HH24:MI'),'暫') RTIME,
 NVL(LT3.CHINESE,'無') AS DEFECT,
 NVL(LT2.CHINESE,'維') AS LOCATION,
 NVL(LT1.CHINESE,'修') AS CAUSE
FROM TMPRUNCARD TR 
LEFT JOIN TMPKP TK ON TK.SN_KEY=TR.SN_KEY
LEFT JOIN SFCS_DEFECTS DF ON TR.SN_KEY=DF.SN_KEY
JOIN SFCS_REASONS_VIEW RV ON DF.DEFECT_KEY=RV.DEFECT_KEY
JOIN SYS_LOOKUP_TABLE LT1 ON (LT1.ORG_ID=70 AND LT1.KIND=2 )AND RV.ROOT_CAUSE=LT1.CODE
JOIN SYS_LOOKUP_TABLE LT2 ON (LT2.ORG_ID=70 AND LT2.KIND=3 )AND RV.LOCATION=LT2.CODE
JOIN SYS_LOOKUP_TABLE LT3 ON (LT3.ORG_ID=70 AND LT3.KIND=1 )AND DF.DEFECT_CODE=LT3.CODE
ORDER BY TR.SN,RTIME

报表呈现-RS部分

Microsoft Report builder 是个很简单方便的报表生成工具,简单报表只要几步就能生成.
逻辑关系:

数据处理部分

Data Sources-数据来源

Data Sets-资料集

查询数据来源得到数据集,并将需要的数据直接使用或格式后转为栏位,供报表使用.

Parameters-参数

查询用的参数变量,可在报表界面输入,或其它查询得到的值

Images-图像

Code - 自定代码

如果内建的函数无法满足格式化报表的需求,你可以自己写代码进行数据处理.

1. 在主界面空白处右键 ,打开报表属性.
2. 在报表属性窗口的代码选项内,输入自定代码

报表呈现

一般可以用表格向导来将前面已准备好的数据报表化

向导里需要注意的是字段布局这个页面.
如果从SQL得到的数据就是你想要的报表格式,你可以直接将所有字段拖放到 ∑里面就行了.

除了在数据集的字段设定里可以格式化数据外,也可以直接在报表界面里格式化数据.
如下图,这里用了自定义的函数.

public Function FindMFGDate ( ByVal s As String)  As String
  Dim n as integer 
    For i as integer = 6 To len(s)
       n=len(s)-i
       if Not IsNumeric (mid(s,n,1)) Then
      Return mid(s,n+1,4)
          exit for
       end if 
   next i

End Function

报表预览