查询引擎.ppt

eHR-查询引擎,HBU事业部,用友软件股份有限公司张英方2010年01月日,目录,查询引擎整体介绍查询引擎基础知识SQL基础知识e-HR查询引擎常用表查询引擎基本函数查询引擎报表制作应用技巧常见问题解答,查询引擎整体介绍篇,什么是查询引擎？中专门的报表模式化开发工具，能够为特定的客户开发特殊需求的报表，自身屏蔽了复杂的后台数据库结构，为非开发类人员进行报表开发提供简单、易用的操作方式。查询引擎是报表的开发工具，不是报表的管理系统。功能定位：一个面向高级实施人员和专业开发人员的查询建模产品，可以全面支持复杂查询的设计和个性化的报表展现。面向对象：实施顾问、技术顾问、开发人员、客户方具有IT背景的人员,查询引擎整体应用架构,查询引擎使用流程,查询引擎整体界面,查询引擎界面相关名词解释,1、创建1)创建文件夹：建立一个便于对象分类管理的目录；2)创建对象：视当前操作节点类别建立查询对象或界面对象。对象的编码是唯一标识，使用字母开头、字母、数字、下划线组成、非SQL保留字的字符串，一经确定不再建议修改编码。对象的名称是对象的显示名，在同一目录下对象名称不能重复。查询对象的数据源是指执行该查询的目标数据源。2、编辑1)修改：改变对象的编码、名称等属性；2)删除：从对象树和数据库删除对象；3)保存：保存对象的编码、名称等属性；4)取消：取消本次修改的内容，恢复到上次保存的定义；5)复制/剪切/粘贴：把某个目录或对象节点拷贝或移动到另一位置。复制一次后只能粘贴一次；6)导入/导出：将对象以XML方式导出到客户端，或从客户端将XML文件导入为对象。,查询引擎界面相关名词解释,查询模型参数：参数是查询模型中代表动态信息的元素。在SQL设计、SQL整理和数据加工中，均可以根据参数的不同，对查询的定义和执行进行调整。应用方式如下：（1）参数定义：用于定义本查询内部引用的所有参数，其中参数名要保证互不相同。数据类型包括自由录入、枚举、参照几种，当类型为枚举时，枚举项为用分隔的枚举值，或者是一个单字段的查询SQL；当类型为参照时，枚举项为基础参照名，或者是用尖括号括起的自定义参照的类名。参数的操作符通常只起显示提示的作用，具体的操作符在参数引用时指定。（2）参数引用：参数可在SQL设计的筛选条件页签引用，也可在SQL整理或数据加工的嵌入代码中利用编码向导引用。（3）参数设置：运行态要求用户首先对参数进行设置，这与查询模板的使用类似。当报表引用了多个查询时，参数将通过多页签设置，每个页签代表一个查询。,查询引擎界面相关名词解释,SQL向导设计：向导设计参考典型SQL的构成规则，充分利用数据字典的功能，引导用户一步步完成SQL的定义，因此是推荐使用的方式。它由以下环节构成：（1）表定义：即SQL语句中的from部分，可以利用数据字典选择库里的数据表，也可以选择查询对象，从而实现查询的集成（基于查询结果定义新的查询）。（2）连接定义：即SQL语句中的join部分（表间连接关系），支持单键连接和多键连接两种模式。（3）字段定义：即SQL语句中的select部分，可以定义任何SQLSERVER语法支持的查询表达式，但要保证字段别名不重复且使用规范名称（以字母开头、由字母、数字、下划线组成的字符串，且不能使用SQL保留字）。如果使用了聚集函数，则由系统自动完成groupby部分。（4）筛选条件定义：即SQL语句中的where部分，可定义确定条件和待定条件，后者可以通过双击右操作数编辑框弹出参数界面并作引用。（5）排序字段定义：即SQL语句中的orderby部分。,查询引擎界面相关名词解释,（6）数据交叉属性：交叉是一种常用的多维数据分析手段，查询引擎提供了以下两种交叉模式：(6.1)投影交叉：假定有一个查询SQL，其查询结果是单行单列的汇总值，另外还有两组WHERE条件的序列，分别为横向序列（WhereX1、WhereX2、WhereXn）和纵向序列（WhereY1、WhereY2、WhereYn），则数据矩阵A（aij）对应的结果集称为投影交叉，如果aij是WhereXi和WhereYj同时作用在上述SQL上获得的结果。(6.2)旋转交叉：此类交叉用于显示表中某几个字段（称为交叉值字段）的统计值，并将其分组放置在查询结果中，一组列放在数据表的左侧（称为交叉行字段），一组列（称为交叉列字段）旋转到数据表的上部成为列表头，统计值则放在对应的交叉单元。,查询引擎界面相关名词解释,SQL手工设计：手工设计是支持自由SQL的方式，由于复杂SQL在解析、适配、执行等方面的不稳定性较大，因此不推荐普通用户使用。手工设计包括SQL录入和字段显示名设置两个环节。SQL整理：指在SQL查询之前，对SQL所作的调整。通常会根据参数的不同，对查询的表、字段或条件等信息进行增补或修改。本功能仅推荐由高级实施人员或程序员使用。数据加工：指某个（或某几个）结果集在经过一系列加工算法的变换后得到另一个结果集，而后者通常是业务上要求的展现结果。相关的算法又称为加工滤镜，滤镜通常支持输入多个结果集和输出一个结果集，并可以嵌套使用。本功能仅推荐由高级实施人员或程序员使用。合并查询：将多个查询结果合并拼装成一个固定行列格式的结果，本功能仅推荐由高级实施人员或程序员使用。穿透规则：指从某个查询结果的区域切换到另一个查询结果的区域的规则，通常反映了一种潜在的业务联系。本功能仅推荐由高级实施人员或程序员使用。,查询引擎界面相关名词解释,引用查询：引用查询是指将界面模型所要展现的查询结果集对应的查询定义引用到模型中来。由于同一查询定义根据不同参数设置也可以得到不同结果集，所以为了区分这种绑定基于同一查询定义的不同结果集时，我们对每一个查询结果集起一个单一的别名来标志。对于数据集之间存在的某种一对多的关系，我们还可以根据需要建立主从连动规则。格式设计：界面模型的格式设计通过可视化的设计界面，建立格式模型。它包括了控件拖放，编辑控件属性及数据绑定属性，定义列格式，列表头，行表头等行列格式以及数据预处理等几部分。（1）控件拖放：从控件工具栏选取控件，放到界面上，通过一定的展现方式组织成完整的界面。（2）编辑属性：双击界面上的控件，打开控件属性编辑器，可以对当前选中控件的属性进行编辑。可以编辑的属性有控件属性和数据绑定属性两种。控件属性是展现和外观上的属性，数据绑定属性指定了控件绑定到哪个数据集，和数据集中的数据保持同步。（3）列格式和表头定义：这是针对表控件的。我们可以设计特殊的表头，在表控件的属性中引用它们；也可以定义列显示格式，在表控件的列格式属性中引用。,查询引擎界面相关名词解释,辅助选项a)删除临时表：用于删除用户建立的物化临时表；b)引用结构：显示某查询引用到的数据表或其它查询；c)数据字典：显示当前查询对象执行数据源的数据字典；d)查询代价：查询数据查询的时间、行数、列数等信息e）查询建表：f）批量修改执行数据源：在建立新的数据源的时候，支持批量修改报表所对应的数据源,查询引擎界面相关名词解释,工具向导设计a)查询设计向导：原需点击多个菜单来完成的步骤，现被集成在一个向导面板中，通过“上一步”，“下一步”等操作来进行格式设计。操作步骤是：选择菜单中的工具化查询设计向导,进入“参数设计”；按“下一步”进入“SQL设计”；按“下一步”进入“SQL整理设计”；按“下一步”进入“数据加工设计”；按“下一步”进入“穿透规则设计”；上述所有操作完成后，按“保存”。b)格式设计向导：原需点击多个菜单来完成的步骤，现被集成在一个向导面板中，通过“上一步”，“下一步”等操作来进行格式设计。操作步骤是：选择菜单中的工具化格式设计向导，进入“引用查询”对话框；按下一步进入“预置报表格式”对话框，可选择报表类型；按“下一步”进入“格式设计”；上述所有操作完成后，按“保存”。c)报表节点挂接：可挂接3种类型的节点：挂接“报表独立节点”：按菜单“报表节点向导”进入“报表节点类型”对话框，选择生成“报表独立节点”类型的报表节点（共有报表独立节点、报表管理节点、报表模板节点三种类型），可选择输入挂接节点编码、挂接节点名称；按“下一步”，进入“格式设计”对话框后，选择格式设计对象节点，按保存。挂接“报表管理节点”：按菜单“报表节点向导”进入“报表节点类型”对话框，选择生成“报表管理节点”类型的报表节点（共有报表独立节点、报表管理节点、报表模板节点三种类型），可选择输入挂接节点编码、挂接节点名称；按“下一步”，进入“格式设计”对话框后，选择格式设计对象节点，按保存。挂接“报表模板节点”：按菜单“报表节点向导”进入“报表节点类型”对话框，选择生成“报表模板节点”类型的报表节点（共有报表独立节点、报表管理节点、报表模板节点三种类型），可选择输入挂接节点编码、挂接节点名称；按“下一步”，进入“查询设计”对话框后，选择查询设计对象节点，选择模板生成项，可勾选“生成查询模板”，“生成报表模板”，“生成打印模板”，按保存完成所有操作。,目录,查询引擎整体介绍查询引擎基础知识介绍SQL基础知识e-HR查询引擎常用表查询引擎基本函数查询引擎报表制作应用技巧常见问题解答,SQL介绍-1,SQL(StructuredQueryLanguage，结构查询语言)是一个功能强大的数据库语言。SQL通常使用于数据库的通讯。ANSI（美国国家标准学会）声称，SQL是关系数据库管理系统的标准语言。SQL语句通常用于完成一些数据库的操作任务，比如在数据库中更新数据，或者从数据库中检索数据。使用SQL的常见关系数据库管理系统有：Oracle、DB2、MicrosoftSQLServer等等。虽然绝大多数的数据库系统使用SQL，但是它们同样有它们自立另外的专有扩展功能用于它们的系统。但是，标准的SQL命令，比如“Select”、“Insert”、“Update”、“Delete”“Create”和“Drop”常常被用于完成绝大多数数据库的操作。SQL功能强大，但是概括起来，它可以分成以下几组：DML（DataManipulationLanguage，数据操作语言）：用于检索或者修改数据；DDL（DataDefinitionLanguage，数据定义语言）：用于定义数据的结构，比如创建、修改或者删除数据库对象；DCL（DataControlLanguage，数据控制语言）：用于定义数据库用户的权限。,SQL介绍-2,DML数据操作可以细分为以下的几个语句SELECT：用于检索数据；INSERT：用于增加数据到数据库；UPDATE：用于从数据库中修改现存的数据DELETE：用于从数据库中删除数据。DDL数据定义语句可以用于创建用户和重建数据库对象CREATETABLE-创建一个数据库表DROPTABLE-从数据库中删除表ALTERTABLE-修改数据库表结构CREATEVIEW-创建一个视图DROPVIEW-从数据库中删除视图CREATEINDEX-为数据库表创建一个索引DROPINDEX-从数据库中删除索引CREATEPROCEDURE-创建一个存储过程DROPPROCEDURE-从数据库中删除存储过程CREATETRIGGER-创建一个触发器DROPTRIGGER-从数据库中删除触发器DCL数据控制用于创建关系用户访问以及授权的对象GRANT-授予用户访问权限DENY-拒绝用户访问REVOKE-解除用户访问权限,在正式学习SQL语言之前，首先让我们对数据库结构有一个基本认识:数据库的数据体系结构基本上是三级结构，但使用术语与传统关系模型术语不同。在SQL中，关系模式(模式)称为“基本表”(basetable)；存储模式(内模式)称为“存储文件”(storedfile)；子模式(外模式)称为“视图”(view)；元组称为“行”(row)；属性称为“列”(column)。1:表由行集构成，一行是列的序列(集合)，每列与行对应一个数据项。2:视图是由若干基本表或其他视图构成的表的定义。3:一个基本表可以跨一个或多个存储文件，一个存储文件也可存放一个或多个基本表。每个存储文件与外部存储上一个物理文件对应4:用户可以用SQL语句对视图和基本表进行查询等操作。在用户角度来看，视图和基本表是一样的，没有区别，都是关系(表格)。,SQL介绍-3,执行SQL的工具,Oraclesqlpluspl_sqlToad,SQLServer查询分析器,DB2命令行中心QuestCentral,检索数据,SQL的基本语法无条件查询有条件查询比较运算符算术运算符逻辑运算符子查询多表连接查询排序查询计算查询,检索数据-基本语法,Select基本语法:SELECTselect_listFROMtable_sourceWHEREsearch_conditionGROUPBYgroup_by_expressionHAVINGsearch_conditionORDERBYorder_expressionASC|DESCSELECT子句:指定由查询返回的列。FROM子句:Selec语句中使用的表、视图WHERE子句:指定用于限制返回的行的搜索条件。GROUPBY子句:指定用来放置输出行的组，并且如果SELECT子句中包含聚合函数，则计算每组的汇总值。指定GROUPBY时，选择列表中任一非聚合表达式内的所有列都应包含在GROUPBY列表中，或者GROUPBY表达式必须与选择列表表达式完全匹配。HAVING子句:指定组或聚合的搜索条件。HAVING通常与GROUPBY子句一起使用。ORDERBY子句:指定结果集的排序。,检索数据-无条件查询,检索整张表:select*fromtable*号表示选择表中所有列检索表中某些列:selectpk_corp,unitcode,unitnamefrombd_corp从公司基本档案中选择pk_corp,公司编码,公司名称取消重复值:selectdistinctecotypefrombd_corp将bd_corp中重复ecotype值在结果中消除掉.DISTINCT关键字可从SELECT语句的结果中除去重复的行。如果没有指定DISTINCT，那么将返回所有行，包括重复的行。,无条件查询示例,无条件查询返回结果集,检索数据-有条件查询,有条件查询就是WHERE子句,指定用于限制返回的行的搜索条件。算术运算符:算术运算符在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何数据类型。+（加）。-（减）。*（乘）。/（除）。%（模）模的含义是除法的整数余数如:selectchinese+englishasallscorefromscorewherename=a将a的语文成绩和英语成绩相加得到总分.其中as是用于为结果集列指定别名比较运算符:比较运算符测试两个表达式是否相同,可以用于数值,字符串,日期等=（等于）（大于）=（大于或等于）（不等于）如:selectnamefromscorewherechinese，而高级模式可以自由设置连接表达式，支持多键连接。,典型模式,高级模式,报表的制作流程,第四步：选择字段,选择字段：即SQL语句中的select部分，可以定义任何SQLSERVER语法支持的查询表达式，但要保证字段别名不重复且使用规范名称（以字母开头、由字母、数字、下划线组成的字符串，且不能使用SQL保留字）。如果使用了聚集函数，则由系统自动完成groupby部分。,报表的制作流程,第五步：设置筛选条件,筛选条件定义：即SQL语句中的where部分，可定义确定条件和待定条件。如果是待定条件，在值编辑框获得焦点时双击编辑框可弹出参数界面并作引用（或者在值编辑框获得焦点时按F12键或者双击弹出参数引用框）；如果是固定条件，在值编辑框获得焦点时按F11键或者双击可弹出环境变量引用框，按F12键可弹出所引用的基础数据表的参照。,双击,报表的制作流程,第六步：设置排序字段,排序字段定义：即SQL语句中的orderby部分,报表的制作过程,第七步：报表的发布在完成以上步骤后，报表的制作基本结束，但是查询引擎设置好后还需要注册到节点否则分公司人员无法使用，节点注册是通过业务向导中的报表节点向导设置。,选择报表模板节点并录新增功能节点编码及名称,录入新的节点名称,根据客户的实际需求生成相关的模板,注意：在完成报表节点生成以后，一定要对新生成的节点进行权限分配，否则相关用户还是无法查看到该报表,报表节点发布效果展示,报表发布三种模式说明,报表可以通过以下三种途径进行发布：,1、报表独立节点：是对格式设置完成后的报表作为一个单独的节点进行发布2、报表管理节点：是对格式设置完成后的一个报表文件夹进行发布3、报表模板节点：是讲查询设置中的报表生成查询模板、打印等模板后进行发布,报表制作整体流程查询设计格式设计查询类报表明细报表汇总报表动态SQL报表交叉类报表投影交叉旋转交叉穿透报表连动报表,格式设计,格式模型（又称界面模型），我们可以设计新的界面模型，也可以对已有的界面模型进行修改。模型的设计包括引用查询，界面可视化设计，绑定数据集，数据预处理等部分。框架如下所示：,格式设计,格式设计态和运行态的基本流程如下图所示（设计态的操作并没有严格意义上的顺序限制）,格式设计-引用查询,引用查询是指将界面模型所要展现的查询结果集对应的查询定义引用到模型中来。由于同一查询定义根据不同参数设置也可以得到不同结果集，所以为了区分这种绑定基于同一查询定义的不同结果集时，我们对每一个查询结果集起一个单一的别名来标志。,格式设计-引用查询,对于数据集之间存在的某种一对多的关系，我们还可以根据需要建立主从关系（即主子表）。查询引擎支持两个数据集之间按照多列关联建立主从关系。,多在联动表中使用,格式设计-格式模型,界面模型的格式设计包括了控件拖放，编辑控件属性及数据绑定属性，定义列格式，列表头，行表头等行列格式以及数据预处理等几部分。分别介绍如下。,格式模型基本控件及布局管理,设计器中提供了几种常用控件，如按钮，标签，单选框等，其中表格和图表最为常用。表体控件，表头控件，面板控件Border布局，Flow布局，Grid布局,格式设计-控件编辑,双击界面上的控件就可以打开属性编辑器，可以编辑控件的相关属性。属性编辑器上边是当前界面的控件列表，下面列出了当前选中控件的属性列表。我们可以从控件列表中选中控件，也可以在界面上双击控件来选中控件进行属性编辑。,报表制作整体流程查询设计格式设计查询类报表明细报表汇总报表动态SQL报表交叉类报表投影交叉旋转交叉穿透报表连动报表,查询类报表,查询类报表指通过一个结果形成的二维数据展现出来的报表，由于SQL或多个SQL查询的语法特点，此类报表通常具有固定的列结构和动态的行结构查询类报表又细分为明细报表、汇总报表及动态SQL制作的报表。,明细报表,明细报表用于展现最基本维度上的数据，即最细粒度的数据。通常明细总是相对于汇总而言的，如果我们把某部门当年的总薪资情况做成汇总表，那么该部门每位员工当年的薪资情况就可以做成明细表。查询类报表中的明细表通常是指一些不含聚合函数的SQL语句直接能够查询出的数据形成的报表。,明细报表-人事信息表设计思路,本张报表主要涉及到的是组织机构的基本信息、人员的基础信息及人员学历子集信息，以人员工作信息作为主表来连接其他的相关人事信息表。由于人员做了跨单位调配之后会在原单位、现单位各生成一条工作信息，在原单位该人员的人员类别对应的归属范围是“调离”，在现单位人员类别的归属范围是“在职”或“其他”，因此在使用该花名册参数设置是应当注意只查询在职人员或离职人员，否则如选择全体人员类别查询的话会出现重复人员。从上表中我们需要从如下几张表中取数：1、公司信息表2、人员工作信息表3、部门信息表4、人员信息学历子集表5、岗位信息表6、人员类别表,参数定义,通过上面的分析，我们需要按照不同的人员类别来查看相关的人员的明细信息；注意在制作报表时，需要根据客户不同的需求来定义参数；,如果打上勾则在查询时，就一定要选择该参数,参数名词解释,参数是查询模型中代表动态信息的元素。在SQL设计、SQL整理和数据加工中，均可以根据参数的不同，对查询的定义和执行进行调整。过滤型参数：如果创建某个参数的作用是根据其取值的不同改变WHERE条件的设置，则这类参数成为过滤型参数，这是最常用的参数功能。参数定义：用于定义本查询内部引用的所有参数，其中参数名要保证互不相同。数据类型包括自由录入、枚举、参照几种，当类型为枚举时，枚举项为用“”分隔的枚举值，或者是一个单字段的查询SQL；当类型为参照时，枚举项为基础参照名，或者是用尖括号括起的自定义参照的类名。参数引用：参数可在SQL设计的筛选条件页签引用，也可在SQL整理或数据加工的嵌入代码中利用编码向导引用。这里当筛选条件是待定条件的时候，双击“值”，文本框，就可以弹出参数选择对话框，点击选择对应的参数即可。参数设置：运行态要求用户首先对参数进行设置，这与查询模板的使用类似。当报表引用了多个查询时，参数将通过多页签设置，每个页签代表一个查询。,参数定义,参数引用,参数设置,查询设计,根据表样，我们需要从后台数据库当中选择如下常用表,注意：所有的参照项在数据库后台都是以主键形式存在的，为了在查询引擎前台显示为名称，我们必须选择一张自定义项档案表。如在本表的制作中我们就选择了一张自定义档案表来对应人员学历及学位中的学历字段；,选择我们需要的字段，对于学历及人员类别等参照项，我们也可以这样进行制作,进行筛选条件的设置，与第一步的参照设置进行引用,根据实际情况进行排序字段的设置,查询结果浏览,选择符合条件的人员类别,明细报表总结,明细报表在查询引擎中适合做如下几类报表：1、花名册2、人员详细信息的基础查询3、薪资福利明细台帐4、劳动合同台帐每个员工在明细报表中有可能会出现多条信息,报表制作整体流程查询设计格式设计查询类报表明细报表汇总报表动态SQL报表交叉类报表投影交叉旋转交叉连动报表穿透报表,汇总报表,汇总数据通常与明细数据相对，是指在明细数据基础上指定若干个有分类意义的字符型列进行分组，并对另外若干个有统计意义的数值型列进行求和（或求平均、最值、计数等）获得的数据。展现汇总数据的报表称为汇总报表。汇总报表又可以细分为以下两种应用：1、静态汇总设置；查询数据的SQL中指定了分组列和汇总列，此设置在报表浏览状态不再改变。要求提供待定条件设置、栏位设置、排序、过滤、定位、小计合计及输出等功能。2、动态汇总设置：事先对数据设定某种缺省的汇总设置，此设置在浏览态可做更改，并重新计算汇总数据。同时具备上述功能需求。,汇总报表表样,此类表一般是按照某一个或几个维度来统计相应的数据。相关的统计维护一般对应着HR的相关参照内容。,薪资应发合计汇总表,部门人员类别分类汇总表,汇总报表-部门人数统计表设计思路,我们以部门人员类别分类汇总表为例，从表样中我们可以看出，该表是以部门及人员类别做为基础维度来统计人数的，所以我们需要从下列表中取得我们所需要的数据。1、部门档案表（bd_deptdoc）2、人员类别表（bd_psncl）3、人员工作信息表（bd_psndoc）该表是统计人数的汇总的，所以在制作的过程中我们还需要用到count函数。该表是以部门作为人数统计的一个维度，所以我们应该加上公司做为该表的参数。,人数统计汇总报表演示,汇总报表的制作过程中我们需要以下几点，别的制作过程同明细表的制作过程是一致的，这里就不再做介绍。,count函数在汇总表统计人数的用法,汇总报表结果演示,汇总报表总结,汇总报表在人力资源日常的工作中会经常使用到，在查询引擎中汇总报表适合做如下几类报表：1、公司或是部门人数统计表2、公司或是部门薪资总额的统计表3、合同签订或合同签订类型的统计表,报表制作整体流程查询设计格式设计查询类报表明细报表汇总报表动态SQL报表交叉类报表投影交叉旋转交叉连动报表穿透报表,动态SQL设计,手工设计是支持自由SQL的方式，由于复杂SQL在解析、适配、执行等方面的不稳定性较大，因此不推荐普通用户使用。手工设计分为以下三个环节：1）SQL录入：用户可以在数据字典的辅助下录入SQL语句，要求遵循SQLSERVER语法，注意，我们在SQL定义里用到了一个替换型参数（#pk_corp#），这里替换型参数也必须在前面参数定义中定义，并且参数名要以#开头并且以#结尾。2）SQL翻译：实际上最终到数据库中执行的SQL语句都是经过翻译的，NC系统有专门的SQL翻译器对各种SQL方言进行翻译，在手工设计中可以选择是否启用翻译器这个功能，通常建议启用。3）字段显示名设置：系统通过对用户录入SQL的解析获得字段信息，并向用户提供输入字段显示名的机会，显示名的默认值与查询字段的物理名相同。在完成以上步骤后，最终展示的效果与SQL向导设计出来的结果是相同的,报表制作整体流程查询设计格式设计查询类报表明细报表汇总报表动态SQL报表交叉类报表投影交叉旋转交叉连动报表穿透报表,交叉类报表,数据库当中的表往往是象下面这样存储的：但是客户要求的报表确是像下面这样的：,交叉类报表,类似这样，把原始数据中一列（或几列，通常有分组意义）数据的不同取值维持在表的左侧不动（比如上表中的姓名），把另外一列（或几列，通常有分类意义）数据的不同取值旋转到行的方向成为列标题（比如上表中的月份），再把另外一列（或几列，通常有统计意义）数据对应汇总到旋转出来的列标题下面（比如上表中的业绩），就形成了交叉表。在上述过程中，我们根据交叉规则把原始数据中的列分成了三种，第一种称为交叉行（其数据交叉后出现在行上），第二种称为交叉列（其数据交叉后出现在列头上），第三种称为交叉值（交叉前行、列、值的数据出现在一行上，交叉后值的数据出现在行、列的交叉点上）。,数据交叉,什么叫做数据交叉？在怎样的场景下需要用到数据交叉？让我们来看两个应用场景：我们从数据库的原始数据表中经过连接得到这样一个结果集，该表共有三行三列，现在我们想得到部门的员工民族统计表，,下图是我们想要得到的最终结果：,我们知道这样的查询实际上是不能通过SQL语句来实现的，因为通过SQL语句查询出来的列中不可能存在像汉族，满族，蒙古族这样的列，这些数值是原始数据库表中某列中的值。但是这个功能我们可以通过投影交叉来实现。,数据交叉,还是刚才那个例子，我们现在想要得到的结果需要按照民族列中的民族数动态扩展，也就是说，不能只有汉族，满族，蒙古族这些固定好的值，如果添加了一笔数据，民族的值为苗族，我们最终的报表能够动态的呈现出这样的变化，理想中的结果如下图所示：,实际上这样的例子可以用旋转交叉来实现，下面我们要分别介绍这两种交叉功能。,投影交叉,投影交叉(第一类交叉)：假定有一个查询SQL，其查询结果是单行单列的汇总值，另外还有两组WHERE条件的序列，分别为横向序列（WhereX1、WhereX2、WhereXn）和纵向序列（WhereY1、WhereY2、WhereYn），则数据矩阵A（aij）对应的结果集成为投影交叉，如果aij是WhereXi和WhereYj同时作用在上述SQL上获得的结果。数据模型图所示：,投影交叉,比如，查询某公司下各部门各民族的人数，选择字段为：那么我们查询到的结果如下：,该图显示的数据位原始无交叉数据,投影交叉,现在我们把查询字段中只保留人数count(bd_psndoc.psncode)一项，然后进行如下的投影交叉设置：,设置行与列的条件,行列条件支持复制功能,投影交叉效果展示,投影交叉总结,投影类报表，就是固定行固定列的报表。所谓投影是指其每个单元格内部存放的内容与该单元的坐标（行列位置）存在直接的关系通过投影交叉，我们可以完成纯粹的SQL语句无法完成的功能把行中的数值放置在列头，但是，这类交叉也有不足之处，例如，我们新增了一笔数据，这笔数据中人员的民族属性是苗族，由于我们在交叉定义中列头没有苗族，所以如果我们想要增加一列，则必须修改原先的查询定义，如果增加的数据非常多，那么我们需要重新修改的列头将会变的纷繁复杂，大大降低了效率，如果能够动态扩展列，不必再去修改原始的查询定义，这样会给我们带来极大的方便。但是旋转交叉为我们提供了这样的功能。,报表制作整体流程查询设计格式设计查询类报表明细报表汇总报表动态SQL报表交叉类报表投影交叉旋转交叉连动报表穿透报表,旋转交叉,旋转交叉(第二类交叉)：此类交叉用于显示表中某几个字段（称为交叉值字段）的统计值，并将其分组放置在查询结果中，一组列在数据表的左侧（称为交叉行字段），一组列（称为交叉列字段）旋转到数据表的上部成为列表头，统计值则放在对应的交叉单元。旋转交叉数据模型图如图所示：,旋转交叉,我们继续引用投影交叉的示例来进行讲解，假定我们对民族分析表进行了如下交叉设置,这里交叉砝码的意思是指交叉值列在旋转后的位置。则交叉后的结果如下表所示：,旋转交叉使用技巧,在旋转交叉设置界面上单击行，列，值标签（在下图用蓝色框标示的位置），可弹出交叉行列复合维度设置和是否小计的设置框。,使用复合维度效果展示,旋转交叉总结,旋转交叉的行和列是动态的，具有不固定的特定点，它是根据系统中的数据动态确定的。,旋转交叉投影交叉,如何理解旋转交叉报表和投影交叉报表？1.对于两类交叉表：其中的“交叉”可以理解成“汇总”2.旋转交叉报表：就是对明细表根据行、列作为其纬度（条件），对其值进行汇总。主要的是理解行、列、值的概念，可以完成函数sum的作用。旋转交叉的行和列是不固定的。3.投影交叉报表：表体数据由所在单元向行、列投影得到的限制条件叠加确定。主要是理解行条件和列条件的概念。投影交叉的行和列是固定的。旋转交叉与投影交叉的区别：两类交叉的最大差别在于：第一类交叉属于查询行为，适用于行列设置都已在表样中固定的报表，其交叉结果严格遵循表样格式，其行列结构不会受查询数据的影响；第二类交叉属于数据加工行为，其交叉后的行列结构在查询出数据之前不可预料，多用于完全真实地对原始数据进行动态处理。,报表制作整体流程查询设计格式设计查询类报表明细报表汇总报表动态SQL报表交叉类报表投影交叉旋转交叉连动报表穿透报表,连动报表,如果两张表之间建立了主从关系，则分别绑定到这两个数据集的控件之间就形成了主从连动的关系。如下午展现的就是两组建立了主从连动关系的查询控件，当主表选中某行时，子表则相应的展现出与主表记录对应的所有子表记录。,连动报表,连动报表最重要的就是要求两张查询表之间用有一个共同变化的主键来控制主表与子表之间的数据变动我们以下图为例来讲解连动报表的制作,通过主表部门表的选择，带动子表人员信息的变化,连动表,分析按照表样制作2张报表，选择主表部门带动子表人员信息的变化故2张表加入部门档案主键做为连动的链接条件,连动表,在格式设计中加建立对象，并点击引用查询，选择刚才制作好的2张报表,点击主从设置相关连接条件,设计主查询及子查询及连接字段,设置完成后点击格式设计,按照实际需求选择报表类型,通过选择主表部门来带动子表数据的变化,报表制作整体流程查询设计格式设计查询类报表明细报表汇总报表动态SQL报表交叉类报表投影交叉旋转交叉连动报表穿透报表,穿透报表,数据穿透：假定报表R1上存在某个区域A1，A1内部有若干业务数据，如果以A1为出发点能够切换到另一张报表R2的上的区域A2，而且A1和A2的内部数据存在着某种内在联系，则称可以从R1穿透到R2。当这种穿透行为传递下去，我们就可以了解某张报表的数据明细，甚至追溯到它的数据来源。通常我们会把上述的区域选为一个表格行或者一个表单元，而上述的内在联系就称为穿透规则。一个查询上可以定义多个穿透规则，每个规则需要指定穿透的目标查询，同时目标查询上要创建一个过滤参数，用于接收穿透区域的取值。如我们现在定义了两个查询：公司部门和部门人员，如下图所示。我们希望在浏览公司部门人数的时候选中某个部门能穿透到部门人员查询查看这个部门下的所有员工。这首先需要我们在公司部门的查询列和部门人员的查询参数之间建立一个关联关系。这里我们选择公司部门的部门编码（deptcode）列；在部门人员查询里加入一个查询参数部门编码（deptcode，源查询列和目标查询参数名字不一定要相同，但是含义要一致），并在查询定义里引用该参数作为过滤条件。,穿透,穿透报表,1、首先，我们必须制作2张报表，一张为部门人数表，一张为人员信息明细表，报表展示如下：,人员信息明细表,部门人数查询表,穿透报表,制作完以上2张报表以后，我们开始对2张报表定义穿透规则。1、人员信息明细表：,注意该参数要与部门人数查询表中的主键相对应,在人员明细表中定义好查询的参数，为了穿透查询名使用,2、部门人数查询表,定义部门档案主键与人员信息表中的参数对应,定义穿透规则,定义部门人数的穿透规则，选择查询模型穿透规则,输入名称后点击编辑,部门人数中查询的字段,需要穿透的查询表也就是人员信息明细表,人员信息明细表中的参数,穿透规则代码,穿透报表结果展示,从上图结果展示中，我们可以看到穿透到的人员明细信息全部都是办公室的人员,也可以通过回退，进行部门人员明细信息重新查询,目录,查询引擎整体介绍查询引擎基础知识介绍SQL基础知识e-HR查询引擎常用表查询引擎基本函数查询引擎报表制作应用技巧常见问题解答,参照模型,1、参照模型,nc.ui.hi.ref.CurrentCorpsRefModel主键参照，可显示当前登录公司以及所有下级所有公司，可利用它来返回公司主键nc.ui.hi.ref.CorpInnerCodeRefModel代码参照，可显示当前登录公司以及所有下级所有公司，可利用它来返回选择公司的级次编码。可用在许多按照公司进行汇总的查询分析上。,系统参数,2、系统预置替换型参数,登录帐套主键：#LoginAccount#登录公司主键：#LoginCorp#登录用户主键：#LoginUser#登录日期：#LoginDate#登录年月：#LoginYearMonth#登录年：#LoginYear#登录月：#LoginMonth#,分析步骤简析,收集并整理客户现有报表；分析样表查询参数。分析样表中各信息项取数来源(与用户沟通确认)；分析样表中各信息项查询、统计条件(与用户沟通确认)；书写报表文档。文档示例：参考制作报表。验证测试报表。特别注意：A.人员报表是否按时间点查询统计；(与用户沟通确认)B.样表的查询统计范围是什么？全集团？登录公司？登录公司及下级公司？。(与用户沟通确认),调试方法简析一,不同类型的报表调试方法不尽相同，一般报表制完成后，常出现以下情况：1.记录数不对，要么多，要么少。例如，员工花名册查询出来有48人，实际有45人；03月份薪资发放明细表。这类表常见于报表分类-1基本表、报表分类-2连动表等。2.统计数不对。常见于报表分类-2汇总表、报表分类-4统计表、报表分类-5投影交叉、报表分类-7旋转交叉等。,调试方法简析二-记录数不对,1.针对记录数不对，常用的调试方法：A。检查查询参数设置是否正确；检查查询参数是否在筛选条件中被正确引用。B。检查表连接。注意左连接与右连接的效果是不一样的。有些情况下以是由于没有正确使用左右连接造成的。另：特别注意，在表连接页签，如果用的是高级连级模式，要特别检查手工书写的连接条件。C。检查筛选条件。有时，筛选条件中的参数不经意间设错了或筛选条件根本没有设置。,调试方法简析二-统计数不对,2.针对统计数不对，常用的调试方法：A。检查查询参数设置是否正确；检查查询参数是否在筛选条件中被正确引用。B。检查表连接。注意左连接与右连接的效果是不一样的。有些情况下以是由于没有正确使用左右连接造成的。另：特别注意，在表连接页签，如果用的是高级连级模式，要特别检查手工书写的连接条件。C。检查筛选条件。有时，筛选条件中的参数不经意见设错了或筛选条件根本没有设置。D。检查列（单元格）条件。有时，单元格条件中常用casewhen,casewhen中通常都会有一系列相关条件，这些条件也是影响数据准确性的原因之一。,调试方法简析二-统计数不对,2.针对统计数不对，常用的调试方法：A。B。C。D。思考：经过以上步骤检查完成后呢？,统计数值不准，我们常常需要把明细数据查询出来进行核对。那如何查询某一单元格的明细数据与统计数值进行核对呢？,调试方法简析三技术层面外的工作,1.报表制作完成并经测试验证之后，下一步重要的工作就是对整理文档，对用户进行培训！这一步，我们常常忽视了，但其很重要！2.不要指望所有的报表，系统均能搞定。即使都能搞定，我们也要注意沟通技巧，”挑选相关报表”来制作。这主要看与用户谈判沟通的情况。记住：多做一张报表，就会多给我们带来工作量，也会多产生一些麻烦,尤其对于复杂的报表！,原则：1.报表制作验证完成后，一定要有相关文档，且必须对用户进行培训！2.尽可能少的答应客户能够实现！,调试方