NCV5-查询引擎技术红皮书.doc

查询引擎技术红皮书NC-UAP 5.0用友NC-UAP2019-11-191第 页目 录第一章前言11.背景12.入门23.小结8第二章对象管理91.对象树91.1对象编辑91.2对象导入导出102.数据源113.首选项144.资源权限15第三章查询模型161.参数控制162.SQL设计192.1数据字典192.2向导设计202.3手工设计253.数据交叉273.1投影交叉283.2旋转交叉314.代码嵌入364.1SQL整理364.2数据加工384.3穿透规则425.查询执行486.查询集成506.1复合查询506.2物化查询516.3合并查询537.数据权限53第四章格式模型551.引用查询562.格式设计572.1控件管理572.2属性绑定642.3行列格式设计662.4预定义数据处理693.报表浏览763.1界面初始化763.2数据处理783.3控件连动823.4报表打印83第五章报表发布861.发布为功能节点861.1 手动挂接861.2 向导发布872.轻量化报表展现89第六章插件开发941生成插件942设计插件1003管理插件1004开发小结101附录1021.查询引擎FAQ之参数篇1022.交叉表的使用样例1033.查询与格式对象安装盘预置方案1094.查询引擎V5新特性1095.查询引擎报表联查业务节点应用方案1106.查询引擎与三大模板集成方案1157.基于QE底层的查询个性化设计方案1238.个性化控制类（JAVADOC）128第 130 页第一章 前言查询引擎（Query Engine，简称QE）的功能定位是，一个面向高级实施人员和专业开发人员的查询建模产品，可以全面支持复杂查询的设计和个性化的报表展现。1. 背景查询引擎在功能流程和查询模型方面参考了其前身产品“自定义查询”的理念，在界面设计方面汲取了Form Design的思路，同时融入了DW的技术，并依赖于通用的NC数据字典。（1） 自定义查询：NC于2000年开始研发的一个面向中级实施人员和二次开发人员的查询报表工具，由查询管理中心、报表格式设计、报表浏览、报表权限控制和数据加工厂几个模块构成，此工具首次提出了基于数据字典来管理数据并建立查询对象的思路，能够提供比较丰富的查询分析、数据加工和报表展现功能。（2） Form Design：NC于2003年开始研发的一种界面开发模式，定位于支持功能建模。（3） 数据字典：一种管理数据库逻辑结构的机制，存在于物理数据库和用户接口之间。NC于2002年开始研发数据字典，最开始只用于自定义查询，后来经过功能强化和逐步推广，成为现在的DDC（Data Dictionary Control）。其功能包括数据库对象的管理与编辑、数据字典的解析生成等。（4） 数据仓库：NC的一个独立产品，能够按照指定的入仓规则从多个业务数据库抽取数据，并通过入仓服务定期把数据加载到数据仓库。产品的需求背景如下所述。（1） 复杂查询报表的需求：在实际的业务查询中，开发人员经常会遇到以下一些棘手的问题复杂的SQL，从不同的查询条件得到完全不同的查询结果，查询结果的复合，业务逻辑代码和大量数据加工算法的介入，各种各样的数据交叉和数据处理手段等等；在展现格式方面，通常要求灵活、友好、可视化、个性化的设计界面，丰富的展现控件和统计图表，依据数据对单元格、列表头和行表头的格式进行设置，表单控件之间的连动等等。这一切都对早期的查询报表工具提出了艰巨的要求。（2） 二次开发的需求：随着NC对行业用户的拓展，由业务组程序员在通用软件中针对每个查询报表需求进行开发的方案已经变得不太现实，一线人员越来越频繁的面临来自用户的个性化报表实施压力。实施团队迫切要求一个强大而且易用的二次开发工具，尽管功能的强大和易用往往存在着一些矛盾。（3） 工具整合的需求：自定义查询在推出后，在实施中得到了一定的应用，但局限于出复杂程度不太高的查询报表。IUFO是公司的传统报表产品，它更偏重于报表的管理方面，比如报表的发布、审核、校验等等。数据仓库使查询的应用由单数据源拓展到了多数据源，并且提供定时同步业务数据的功能。为了有效地实现资源共享，结合发挥各个工具的优势，NC决策层一直在推动相关产品的整合。（4） 查询分析软件发展潮流的需求：近几年来，查询分析、数据挖掘、商业智能的研究不论在企业应用还是在学术领域都成为热点，ERP软件的数据建模和查询建模功能已经成为代表产品水平的重要指标。2. 入门现在让我们来看一个最基本的查询样例，作为入门，我们知道在任何ERP系统中都存在公司和用户的概念，下面我们做一个查询某公司下用户的查询。1. 进入客户化自定义查询查询引擎管理界面 图 一12. 在“查询设计”树下新建一个目录，“jl测试用例”，并在这个目录下面创建一个查询对象用户查询。 图 一23. 选择参数定义，这里我们定义一个名为corp,显示名为“公司”的参数，注意这里的数据类型选择编码参照，枚举项中选择这个编码参照的类型，我们将根据这个参数选取的值来确定待选公司，并且在查询中根据这个参数选取的值来进行结果筛选。图 一34. 制作查询定义，制作一个查询定义的具体流程实际上就是为了生成一条SQL语句，查询数据源去获取数据，但是这个生成SQL语句的过程被我们用图形化界面代替了，用户只需要了解基本的SQL规则就可以了，无需亲自手动去写纷繁复杂的SQL语句，同时我们也支持手工SQL。图 一4用户可以通过增加表来增加数据字典中的数据表，也可以点击临时表引用前面做好的查询定义对象，引用查询定义对象相当于设计了一个复合查询，关于复合查询的定义参见第二章第六节查询集成。这里我们选择增加表来增加一个sm_user表，这张表里面存储的是NC系统的用户信息。图 一5在选择字段页签下选择“用户名”等字段，这里的字段表达式可以是某表的字段，也可以是字段的运算或者函数处理表达式，例如：len(sm_user.user_name)，就是代表user_name这个字段的字符串长度。显示名可以是用户自定义的字符串，将来显示在报表的列表头区域。图 一6前面我们提到这个查询必须按照公司为条件，过滤查询出来的用户，实际上就是为SQL语句生成一个where条件，我们支持两种条件，固定和待定，顾名思义固定条件写好之后就不能变化，而待定条件是跟参数设置相关的，可以根据用户输入参数变化。我们在这里选取待定条件，用户表的公司编码字段等于前面我们设置过的corp。关于查询定义的设计后面的章节将有更详细的论述，这里只介绍最基本的功能。5. 制作格式设计图 一7新建格式设计文件夹以及对象的方式与新建查询对象的方法一样，这里略去不讲。生成新的格式设计对象之后可点菜单中的界面模型引用查询，来绑定我们刚才做好的查询设计对象“用户查询”。图 一8选取刚才做好的查询之后我们在点击界面模型格式设计，这里可以选取我们预置的几种界面模型之一，例如这张表我们选取表头+表体格式。图 一9最后点菜单中的浏览，让我们浏览一下制作出的第一张报表，图 一10点浏览之后我们会看到一个选择参数的对话框，在这个参数对话框中我们可以设置自己的参数值，比如我们选取sunny_corp这个公司，点确定，则可以浏览这个公司下的所有用户，如下图。图 一11选择表头区域的公司参照，我们可以浏览不同公司下的用户。至此一个最简单的自定义查询已经制作完毕，我们可以将做好的查询作为独立节点发布到NC系统下供用户使用。3. 小结在本章中我们介绍了查询引擎的发展历史以及产品的需求背景，通过一个入门样例，让用户初步了解了查询引擎的基本功能，在后面的章节中我们将详细介绍查询引擎的各项功能，并通过一些样例使得用户能够使用本产品进行快速便捷的二次开发。下图是对查询引擎整体应用架构的一个示意。图 一12 查询建模体系第二章 对象管理1. 对象树查询引擎的管理调度功能由主界面完成，主界面左侧为一棵对象树，树上挂有代表查询对象和界面对象的节点，右侧为显示当前选中对象基本属性的属性框。图 二1 管理调度主界面 对象树（ObjectTree）：对象树是一个支持层次管理的数据结构，它维护自身的加载、存储和节点的目录管理，每个节点的信息主体存储在数据库的一个BLOB字段中。对象树上的节点通常分为目录（Folder）和对象两类，并支持快速定位（在树获得焦点时用ctrl-f快捷键可弹出查找对话框，支持按编码或名称来模糊查找）。 属性框（ObjectInspector）：一个自动显示对象的属性和取值的控件，取值部分会根据属性的数据类型采用不同的渲染器来展示，不同的编辑器来编辑。1.1 对象编辑目录的编辑操作如下：（1） 增加/删除/修改：在根节点或目录节点之下可以增加目录，删除目录时会删除目录下的所有对象。目录的可修改属性为目录名称。同一目录下的目录和对象不能重名。（2） 复制/剪切/粘贴：在查询子树或格式子树下，可以将一个目录及其下属内容从某位置拷贝或移动到另一个位置。对象分为查询对象和格式对象，其编辑操作如下：（1） 增加/删除/修改：在根节点或目录节点之下可以增加查询对象或者界面对象。前者的属性为编码、名称和数据源，后者的属性为编码和名称，其中编码一经使用，就不再建议作任何修改，因为此编码可能被其它对象引用。请注意对象的唯一标识是编码而不是显示名称，一个查询对象或是格式设计对象可以有多个显示名称但是只能有唯一的编码。（2） 复制/剪切/粘贴：在查询或格式子树下，可以将一个对象从某位置拷贝或移动到另一个位置。请注意这里的复制粘贴都是在同一棵树下的，如果复制格式设计对象到查询对象树下则会出错。1.2 对象导入导出（1） 导出：选中某个对象，可将其导出为XML文件，文件里描述了对象的所有信息；也可选中某个目录，将该目录下所有的子目录和对象都导出到指定位置（保持原有的目录级次）。（2） 导入：在选中某个目录之后，导入功能可用。导出的XML文件，可以导入到当前目录下。此功能可用于对象的跨数据库复制。导入时，我们可以选择单个文件，也可以选择一组文件或目录，导入后会保留原目录的级次结构。需要注意的是：l 在导出查询对象或界面对象时，需将其引用的查询对象一并导出才有意义；l 导入不能违反对象主键及名称的唯一性约束。下图显示了某查询对象所导出的XML文件。图 二2 导出XML文件（3） 在导入导出的过程中需要注意的一个问题是数据源的问题，在实际应用中经常出现这样的场景：实施人员在自己的环境（数据源为home）下做了一对查询模型设计和格式设计，然后导出这一对设计对象为xml文件，然后到用户的环境（数据源为customer）下执行导入，发现无法查出数据，并且提示“找不到查询所用数据源”，这是因为导出成XML文件的时候，记录在XML文件中的执行数据源是home，而客户的数据源名称是customer，与查询模型设计中的数据源不符合，这样查询模型就无法到正确的数据源下去取数，可以在弹出的提示框“是否修改执行数据源为当前数据源”，选择“是”，则该查询设计模型的数据源被修改为当前账套的数据源，这样就可以在客户的数据源中正确取数了。2. 数据源查询引擎的数据源关系如下图所示：图 二3 查询引擎数据源关系模型图l 多数据源运作机制：查询引擎支持这样的模式NC业务在数据源A下运行，查询引擎的定义放在数据源B，通过引擎定义的查询可以到数据源C去执行。注：只有数据仓库下的查询引擎有切换定义数据源和跨库查询的功能，客户化下的查询引擎只能查询当前账套数据源的数据。图 二4 切换定义数据源l 各种数据源的概念区分：业务数据源（上面说的A）通常指当前登录账套的数据源，是NC运行所需要的业务数据源。定义数据源（上面说的B）是指查询引擎自身的系统表所在的数据源，执行切换功能后，主界面上的对象树的内容会作相应改变。请注意查询引擎自身的系统表可以和当前的业务数据源不是同一个数据源，例如当前登陆账套是Account1，数据源是datasource1，而查询引擎的系统表可以不在该数据源下，而在另一个数据源datasource2下，datasource2可能是账套Account2的数据源，也可能是任何其他的数据源。但是前提是该数据源中必须含有查询引擎自身的系统表。图 二5 切换定义数据源效果执行数据源（上面所说到的C）是指该查询定义取数的数据源，我们知道查询定义设计的最终目的还是去特定的数据源查取用户所需的数据，执行数据源就是存储这些用户所需数据的数据源，查询引擎本身的系统表所在数据源可以跟这个取数数据源不同，也就是说定义数据源和查询数据源可以分离。图 二6 执行数据源3. 首选项QE数据源的配置在V5.0版本中统一放在查询引擎环境变量设置（又称首选项）这个节点里面，该节点位于客户化二次开发工具自定义查询下，界面如图2-6所示：图 二7 查询引擎首选项管理这个节点存放查询引擎运行所需要的环境变量或者首选项变量，这些变量被存储在后台queryengine.xml中，位于nchome/ierp/bin目录下，由于本文下面的章节中还有需要涉及到环境变量设置的地方，所以这里只提及数据源环境变量的设置。 有效数据源：启动中间件时的所有能够连接的合法数据源，实际上也就是prop.xml文件中的所有定义过并且能够正常连接的数据源。 查询执行可用数据源：查询引擎所能够引用的执行数据源列表，查询引擎管理中所有的查询模型的取数数据源范围就在这个列表中定义。请注意一个查询执行数据源必须是一个有效数据源。 数据加工数据源：查询引擎中高级功能“数据加工”所使用的数据源，二次开发人员需要特别注意在使用数据加工这个功能时，在获取数据源的时候有如下语句：getEnv().getDefaultDsn();这句话返回的就是在这里定义的“数据加工默认数据源”。V5版本支持多种数据加工数据源，这里定义的时在默认情况下的数据源，下表列出在各种查询引擎版本下的数据加工数据源及其含义。版本号对应数据源及其含义LOGIN查询引擎单数据源版本，数据源为当前登陆账套数据源Default默认数据源，在查询引擎环境变量设置中显式定义的数据加工默认数据源DW查询引擎的DW版本，数据源为查询引擎环境变量中定义的DW数据源BPM查询引擎的BPM版本，数据源为查询引擎环境变量中定义的BPM数据源QMD查询引擎多数据源版本，数据源为查询对象设计中查询对象的数据源表 二1首选项配置的内容还包括数据仓库应用的一些配置，以及界面操作过程中的与个性化或性能相关的设置，比如后面会提及的查询执行的最大行数限制等等。首选项对应的配置文件是%NC-HOME%ierpbinqueryengine.xml，是被全集团共享的。4. 资源权限单数据源版查询引擎支持对不同公司的不同用户授予全部权限或浏览权限。全部权限是指所有查询、格式对象的设计和浏览权限，浏览权限是指能够浏览指定格式对象的报表展现。图 二8 查询引擎权限分配如果想做更细粒度的资源权限控制，比如建立基于创建者的权限体系，或者对查询设计者的可查数据字典进行限制，则需要通过下文介绍的管理插件和设计插件技术进行开发。第三章 查询模型查询模型的定义包括参数定义、SQL设计（含交叉定义）、SQL整理、数据加工、穿透规则等几部分，其中穿透规则描述的是本查询与外部查询的通信关系，剩余部分都用于描述查询内部的属性。查询引擎的查询模型（数据模型）设计如下：图 三1 查询模型示意图查询设计态和运行态的基本流程如下图所示：图 三2 查询模型流程1. 参数控制参数是查询模型中代表动态信息的元素。在SQL设计、SQL整理和数据加工中，均可以根据参数的不同，对查询的定义和执行进行调整。过滤型参数：如果创建某个参数的作用是根据其取值的不同改变WHERE条件的设置，则这类参数成为过滤型参数，这是最常用的参数功能（参数的FAQ可以参见附录）。（1） 参数定义：用于定义本查询内部引用的所有参数，其中参数名要保证互不相同。数据类型包括自由录入、枚举、参照几种，当类型为枚举时，枚举项为用“”分隔的枚举值，或者是一个单字段的查询SQL；当类型为参照时，枚举项为基础参照名，或者是用尖括号括起的自定义参照的类名。致开发者：在V5.0中该类应该放置在client以上的包中，对于放置在private中的类，则会抛出ClassNotFoundException（2） 参数引用：参数可在SQL设计的筛选条件页签引用，也可在SQL整理或数据加工的嵌入代码中利用编码向导引用。这里当筛选条件是待定条件的时候，双击“值”，文本框，就可以弹出参数选择对话框，点击选择对应的参数即可。（3） 参数设置：运行态要求用户首先对参数进行设置，这与查询模板的使用类似。当报表引用了多个查询时，参数将通过多页签设置，每个页签代表一个查询。图 三3 参数定义图 三4 参数引用图 三5 参数设置替换型参数：是指参数名以#开头并以#结尾的参数，多用于手工SQL设计中，也可用于向导SQL的非待定条件部分。这种参数通常相当于preparestatment中的?占位符，关于替换型参数，后面的章节中还有详细介绍。在设置替换型参数的时候，不输入值将不被认为是忽略此参数对应的条件。2. SQL设计SQL设计包括向导设计方式和手工设计方式，无论采用哪种，在完成设计时系统均会对SQL进行合法性校验，如未通过校验则不能保存设计。作为向导设计的一个重要组成环节，我们有必要先介绍一下数据字典。2.1 数据字典前面已经提到过，查询对象的属性中包含数据源信息，因此在设计查询的时候需要加载相应数据源的数据字典。我们知道，NC的数据库以DDC作为数据字典，但非NC的数据库则没有DDC，此时，系统会根据执行数据源是NC、IUFO或其它数据库三种情况分别加载不同的数据字典，而且这一机制已经预留了扩展的接口，其目的就是可以面向各种类型的数据库执行取数功能。NC数据字典提供了统一的数据建模平台，支持对各种数据库对象进行管理，维护这些物理对象的逻辑属性，并向外系统提供访问数据库逻辑信息的接口。以查询引擎为例，查询对象的定义是基于数据字典进行的，而这份数据字典来自查询对象所指定的数据源。由于多数报表的列与业务数据库的字段存在某种对应关系，因此数据字典的存在有助于提高查询定义的直观性和易用性。图 三6 NC数据字典NC数据字典通常在安装产品时候由系统生成，如果想自己为某些数据库对象生成字典，可以采用数据字典管理界面提供的导入方法。导入方式包括三种（详细操作可参见数据字典导入说明）：1、 解析PDM文件（xml格式）；2、 解析NC标准建库脚本；3、 提取数据库元数据；如果需要给非NC数据库（比如用户采用的第三方数据库）生成数据字典，可以利用PowerDesigner提供的逆向工程功能将库里的物理表生成PDM文件，修改其中表和字段的中文名称，然后以xml文件格式存储，再使用上述第一种方法导入。查询引擎中用到的数据字典都以一棵对象树的形式存在，树节点包括目录、表、视图、字段、外键等信息，通过双击对象节点可以协助录入SQL语句的成分。2.2 向导设计向导设计参考典型SQL的构成规则，充分利用数据字典的功能，引导用户一步步完成SQL的设计，因此是推荐使用的方式。它由以下六个环节构成，其中最后一步“交叉属性”超出了SQL的涵义，我们在后面的专门章节中进行介绍。（1） 表定义：即SQL语句中的from部分，可以利用数据字典选择库里的数据表，也可以选择查询对象，从而实现查询的集成（基于查询结果定义新的查询）。图 三7 表定义图 三8 增加表（物理表）与临时表（2） 连接定义：即SQL语句中的join部分（表间连接关系），支持典型模式和高级模式，典型模式实际就是关系数据库中的单键连接，连接字段只有一个，并且操作符都是常见的= ，而高级模式可以自由设置连接表达式，支持多键连接。图 三9 连接定义典型模式图 三10 连接定义高级模式（3） 字段定义：即SQL语句中的select部分，可以定义任何SQLSERVER语法支持的查询表达式，但要保证字段别名不重复且使用规范名称（以字母开头、由字母、数字、下划线组成的字符串，且不能使用SQL保留字）。如果使用了聚集函数，则由系统自动完成group by部分。V5中增添了这样的功能，可以在查询字段中添加NC的公式，但是这些公式需要特定的格式，例如取得前台数据库缓存中某表的一个或多个字段，可以使用getColvalue(xx,yy,zz)，请注意单引号和大括号是必须的。这里的处理机制是先将上述公式作为一个字符串，在查出结果集之后再把公式翻译执行。所以这里的公式是基于最终结果集的。图 三11 字段定义图 三12表达式生成器（4） 筛选条件定义：即SQL语句中的where部分，可定义确定条件和待定条件。如果是待定条件，在值编辑框获得焦点时双击编辑框可弹出参数界面并作引用（或者在值编辑框获得焦点时按F12键或者双击弹出参数引用框）；如果是固定条件，在值编辑框获得焦点时按F11键或者双击可弹出环境变量引用框，按F12键可弹出所引用的基础数据表的参照。对于在查询字段里使用了聚集函数的SQL设计，筛选条件可能需要使用having子句。Having页签默认是隐藏的，可以在查询定义框的南部面板按Ctrl+Shift+鼠标左键弹出隐藏的Having页签。图 三13 筛选条件定义（典型模式参见图3-3）（5） 排序字段定义：即SQL语句中的order by部分。图 三14 排序字段定义（6） 向导设计完成后在查询定义框的南部面板鼠标双击可弹出向导生成的等效SQL（待定条件全用(1=1)代替）。该功能可以用来检查SQL语句是否有错。实际上，每次做完查询设计之后点击确定时，我们都会试执行一下您的SQL语句，但是并不查出数据来，如果您的查询设计最终生成的SQL语句有问题，那么就会有相应的出错提示。图 三15 由查询设计生成的SQL语句（7） 数据交叉属性：交叉是一种常用的多维数据分析手段，详见后文。2.3 手工设计手工设计是支持自由SQL的方式，由于复杂SQL在解析、适配、执行等方面的不稳定性较大，因此不推荐普通用户使用。手工设计分为以下两个环节：1) SQL录入：用户可以在数据字典的辅助下录入SQL语句，要求遵循SQLSERVER语法。如图3-13，注意，我们在SQL定义里用到了一个替换型参数（#pk_corp#），这里替换型参数也必须在前面参数定义中定义，并且参数名要以#开头并且以#结尾。（关于替换型参数的具体说明参见附录）； 图 三16 SQL录入2) SQL翻译：实际上最终到数据库中执行的SQL语句都是经过翻译的，NC系统有专门的SQL翻译器对各种SQL方言进行翻译，在手工设计中可以选择是否启用翻译器这个功能，通常建议启用。 图 三17 SQL录入3) 字段显示名设置：系统通过对用户录入SQL的解析获得字段信息，并向用户提供输入字段显示名的机会，显示名的默认值与查询字段的物理名相同。图 三18 字段显示名设置3. 数据交叉什么叫做数据交叉？在怎样的场景下需要用到数据交叉？让我们来看两个应用场景：1 我们从数据库的原始数据表中经过连接得到这样一个结果集，该表共有三行三列，现在我们想得到部门的员工民族统计表，图 三19下图是我们想要得到的最终结果：图 三20我们知道这样的查询实际上是不能通过SQL语句来实现的，因为通过SQL语句查询出来的列中不可能存在像汉族，满族，蒙古族这样的列，这些数值是原始数据库表中某列中的值。但是这个功能我们可以通过投影交叉来实现。2 还是刚才那个例子，我们现在想要得到的结果需要按照民族列中的民族数动态扩展，也就是说，不能只有汉族，满族，蒙古族这些固定好的值，如果添加了一笔数据，民族的值为苗族，我们最终的报表能够动态的呈现出这样的变化，理想中的结果如下图所示：图 三21实际上这样的例子可以用旋转交叉来实现。下面我们要分别介绍这两种交叉功能。3.1 投影交叉 投影交叉(第一类交叉)：假定有一个查询SQL，其查询结果是单行单列的汇总值，另外还有两组WHERE条件的序列，分别为横向序列（WhereX1、WhereX2、WhereXn）和纵向序列（WhereY1、WhereY2、WhereYn），则数据矩阵A（aij）对应的结果集成为投影交叉，如果aij是WhereXi和WhereYj同时作用在上述SQL上获得的结果。数据模型如图所示：图 三22 投影交叉数据模型比如，查询某公司下各部门各民族的人数，原始SQL（由查询向导生成的等效SQL）为：select bd_deptdoc.deptname as deptname, bd_defdoc.docname as docname, count(bd_psndoc.pk_psndoc) as psnno from bd_deptdoc left outer join bd_psndoc on bd_deptdoc.pk_deptdoc= bd_psndoc.pk_deptdoc left outer join bd_accpsndoc on bd_psndoc.pk_psndoc=bd_accpsndoc.pk_psndoc left outer join bd_defdoc on bd_accpsndoc.nationality=bd_defdoc.pk_defdocwhere bd_deptdoc.pk_corp = ?group by bd_deptdoc.deptname, bd_defdoc.docname查出的结果如下表所示：图 三23 原始数据（无交叉）现在我们把查询字段中只保留人数count(bd_psndoc.psncode)一项，然后进行如下的投影交叉设置：图 三24图 三25 投影交叉设置则交叉后的结果如下表所示：图 三26 投影交叉结果通过投影交叉，我们可以完成纯粹的SQL语句无法完成的功能把行中的数值放置在列头，但是，这类交叉也有不足之处，例如，我们新增了一笔数据，这笔数据中人员的民族属性是苗族，由于我们在交叉定义中列头没有苗族，所以如果我们想要增加一列，则必须修改原先的查询定义，如果增加的数据非常多，那么我们需要重新修改的列头将会变的纷繁复杂，大大降低了效率，如果能够动态扩展列，不必再去修改原始的查询定义，这样会给我们带来极大的方便。旋转交叉为我们提供了这样的功能。3.2 旋转交叉 旋转交叉(第二类交叉)：此类交叉用于显示表中某几个字段（称为交叉值字段）的统计值，并将其分组放置在查询结果中，一组列在数据表的左侧（称为交叉行字段），一组列（称为交叉列字段）旋转到数据表的上部成为列表头，统计值则放在对应的交叉单元。旋转交叉数据模型图如图所示：图 三27 旋转交叉数据模型图两类交叉的最大差别在于：第一类交叉属于查询行为，适用于行列设置都已在表样中固定的报表，其交叉结果严格遵循表样格式，其行列结构不会受查询数据的影响；第二类交叉属于数据加工行为，其交叉后的行列结构在查询出数据之前不可预料，多用于完全真实地对原始数据进行动态处理。沿用3.1使用的例子，假定我们进行如下的旋转交叉设置：图 三28 旋转交叉设置这里交叉砝码的意思是指交叉值列在旋转后的位置。则交叉后的结果如下表所示：图 三29 旋转交叉结果 在旋转交叉设置界面上单击行，列，值标签（在图3-18中用蓝色框标示的位置），可弹出交叉行列复合维度设置和是否小计的设置框。图 三30 交叉设置图 三31交叉复合维度设置图 三32是否小计设置 复合维度：设置复合维度是为了减少冗余数据的出现，举例说明如下图是没有设置复合图 三33 没有设置复合维度维度的交叉结果，我们把交叉行和交叉列中的每一项当作一个数据维度，则该原始表就存在三个维度，分别是部门编码、部门名称和民族，然后我们把处于交叉值的人数列中的数据“投影”到这个三维的数据空间中。如图 334图 三34这样，即使原始表中不存在部门名称为ERP事业部，部门编码为10的记录，在交叉过后也会出现多余的一条记录，如表中红框所示，如果在交叉行上设置复合维度，实际上就等于把部门编码和部门名称合起来形成了一个维度，如Error! Reference source not found.图 三35 设置复合维度后的数据空间上面描述的数据库中没有记录的就不会再被展现出来，交叉结果如下图所示：图 三36 设置复合维度后的交叉结果 交叉值合计：在点击交叉设置中的“值”字样，可以弹出如图 三32的对话框，在这里可以设置是否对交叉值进行合计，如是设置过合计的结果：图 三37旋转交叉结果（设置了合计）关于旋转交叉的更细节的内容，请参考附录的交叉表使用样例。4. 代码嵌入4.1 SQL整理 SQL整理（SQL Repair）：指在SQL查询之前，对SQL所作的调整。通常会根据参数的不同，对查询的表、字段或条件等信息进行增补或修改。SQL整理的具体内容由内嵌的代码片段描述（这与后面将介绍的数据加工和穿透规则一样），代码的录入可通过编码向导协助完成，尽管如此，这几种功能仍然推荐由高级实施人员或程序员使用。在SQL整理中把新的查询基本定义设置为整理成果的方法是：setQueryBaseDef(QueryBaseDef qbd);图 三38 SQL整理代码在图示的SQL整理代码中，表达了这样一个整理愿望：当参数showtype的取值等于“明细”时，取出所有定义的查询字段表达式，并且去除汇总函数(sum)，然后增加一个分录摘要字段，并且设置分组列为空，实际上这里不仅仅能够修改查询字段及其表达式，更可以增加或者删除查询所引用的表，连接条件，排序分组字段，甚至可以手工写一个查询定义，并且把手写的查询定义设置为主查询定义。请注意如果在这里替换了查询定义，则原先在向导中或者在手工SQL设计中的查询定义就不再生效。以下简单介绍几种SQL整理算法的编码向导使用方法：（1） 追加查询表：参数为待追加的表物理名。图 三39 追加表向导（2） 追加连接条件：与追加表同时使用，参数为连接模式、追加表物理名和追加的表间连接条件。图 三40 追加连接条件向导（3） 追加查询字段：与追加表同时使用，参数为追加字段的表达式、显示名和别名。图 三41 追加字段向导（4） 按参数追加条件：根据参数取值的不同追加不同的筛选条件，参数为参数名和筛选字段。图 三42 按参数追加条件向导4.2 数据加工 查询结果集（ResultSet）：由二维数据集合（数据体）及其列信息（元数据，包括列名、列数据类型等信息）构成的一个数据载体。查询引擎中使用的结果集为Borland公司开发的DataSet。 数据加工（Data Process）：数据加工是指某个（或某几个）结果集在经过一系列加工算法的变换后得到另一个结果集，而后者通常是业务上要求的展现结果。相关的算法又称为加工滤镜，滤镜通常支持输入多个结果集和输出一个结果集，并可以嵌套使用。数据加工与SQL整理的区别在于：前者在SQL查询之后进行，作用的对象是结果集；后者在查询之前进行，作用的对象是SQL定义。在数据加工中把新的数据集设置为加工成果的方法是：setDataSet(StorageDataSet ds);在数据加工的编码向导中，提供了多种实用加工滤镜。图示的数据加工代码把ID分别为“male”和“female”的两个结果集按照deptname字段为键进行了左外连接，并在连接结果中保留了“deptname”、“mpsnnum”、“fpsnnum”三列。图 三43 数据加工代码以下简单介绍几种数据加工算法的编码向导使用方法：（1） 由查询获得结果集：根据查询ID和参数变量获得查询结果集，这里的参数变量是指需要传给目标查询的参数哈希表，默认可以是当前查询的参数哈希表，见图3-27，不需修改。这里版本号的意思是使用查询引擎的哪种版本，默认是单数据源版本，这样取数据加工数据源实际上就是用户在查询引擎环境变量设置中设置的数据加工默认数据源，也可以选择BPM数据源和DW数据源，这两种数据源属于查询引擎高级版本应用。图 三44 获得结果集向导（2） 公式设置：参数为公式列名、返回值数据类型和公式，如果公式列名不存在则新增一列，否则将该列的内容更新为公式返回值。一个公式也可以引用另一个公式列的计算结果。图 三45 公式编辑图 三46 公式定义（3） 结果集连接：指定两个结果集的连接字段、连接方式和连接后的保留字段，得到连接后的结果集。图 三47 结果集连接向导（4） 结果集联合：指定两个列结构相兼容的结果集，得到联合（union）后的结果。图 三48 结果集联合向导理论上上结果集加工算法是无穷尽的，并且这些算法可以组合而成为一道或者多道工序，这里仅仅列举几种简单常用的算法，更多更复杂的算法需要读者在实际应用开发中按照不同情况和场景灵活使用。4.3 穿透规则 数据穿透：假定报表R1上存在某个区域A1，A1内部有若干业务数据，如果以A1为出发点能够切换到另一张报表R2的上的区域A2，而且A1和A2的内部数据存在着某种内在联系，则称可以从R1穿透到R2。当这种穿透行为传递下去，我们就可以了解某张报表的数据明细，甚至追溯到它的数据来源。通常我们会把上述的区域选为一个表格行或者一个表单元，而上述的内在联系就称为穿透规则。4.3.1 查询间穿透一个查询上可以定义多个穿透规则，每个规则需要指定穿透的目标查询，同时目标查询上要创建一个过滤参数，用于接收穿透区域的取值。如我们现在定义了两个查询：公司部门和部门人员，如图3-32。我们希望在浏览公司部门的时候选中某个部门能穿透到部门人员查询查看这个部门下的所有员工。这首先需要我们在公司部门的查询列和部门人员的查询参数之间建立一个关联关系。这里我们选择公司部门的部门编码（deptcode）列；在部门人员查询里加入一个查询参数部门编码（deptcode，源查询列和目标查询参数名字不一定要相同，但是含义要一致），并在查询定义里引用该参数作为过滤条件。下面来看一个简单的穿透用例，假定我们做好了一个查询定义，是根据公司查询该公司下的用户，另外我们还有一个查询，是查询某公司，现在的我们想要得到这样一种效果，即在浏览公司报表的时候想动态的知道公司下所有用户的信息，也就是说根据选中的公司穿透到用户查询去。可以这样定义穿透规则：图 三49 公司查询图 三50 用户查询的参数定义图 三51 参数引用图 三52 用户查询的结果 定义公司部门的穿透规则，选择查询模型穿透规则图 三53 穿透规则设置点击编辑按钮进入穿透规则代码向导，选择查询间穿透向导，设置如下图 三54 穿透规则代码向导一个查询可以设置多条穿透规则，由本查询可穿透到的目标查询需要创建一个（或多个）过滤型参数，用于接收穿透值。下图所示的穿透规则表达了以下的穿透意愿：如果用户选中了当前查询结果的某行时，允许用户根据选中行的psncode字段取值穿透到另一个查询（即目标查询，其ID为“rygzan”），而目标查询的接收参数为psncode图 三55 穿透规则定义向导图 三56 穿透规则代码下图显示了一个由人员信息穿透到薪资信息再穿透到同姓员工工资信息，最后穿透到每月明细的用例，其中第一步穿透是单列等值穿透（相当于“人员=李涤非”），第二步是非等值穿透（相当于“姓名 like 李%”），第三步是多列联合穿透（相当于“人员=李屏 and 年份=2003”）。图 三57 穿透效果示意4.3.2 穿透到节点随着产品的推广使用，不少程序员和实施人员开始利用查询引擎开发报表并发布为功能节点，从QE报表穿透到业务节点的需求应运而生。很多业务希望根据报表追溯来源数据，比如从SCM报表联查订单，从财务报表联查凭证，从HR报表联查卡片等等，都可以作为这种需求的典型应用。NCV5界面关联操作方案为上述需求提供了平台级的技术依据。通过把查询引擎报表作为调用方节点，业务节点作为被调用方节点，扩充穿透规则机制和组织传递参数结构，就能够支持基于功能节点的关联查询。原先已开发好的功能节点只需实现联查接口即可应用此功能。查询引擎V5.0版本支持从查询穿透到节点的功能，继续让我们来看前面提到的查询公司下用户的样例。在图 三53 穿透规则设置中增加一个穿透规则：图 三58 穿透到节点规则设置在目标节点参照中，可以选择穿透的目的节点，目的节点是已注册到NC系统的可执行功能节点，特别的，该节点如果实现了特定的查询接口，则可以根据当前选中行中的某字段的值执行特定的查询功能，对于一般节点，穿透仅仅是打开该节点，与用户双击打开一样。具体操作可参见附录5。5. 查询执行在建立好查询模型之后，系统提供预览查询执行结果的功能。用户首先输入执行查询所需要的参数取值（如果有参数的话），查询引擎会根据该查询对象的数据源去对应的数据库执行查询，在经过查询结果集评估（对于查询记录数过大的情况允许用户优化或放弃查询）之后，把结果集展现给用户。在展现之后，用户可以继续进行穿透、交叉等工作。图 三59 查询预览请注意V5版本提供了查询优化的设置，例如用户如果执行了一个数据量非常大的查询，这个查询本身耗费了相当长的时间，但是用户只是想浏览一下该报表的大致情况，并不想查看每条数据，我们在首选项定义中可以配置只查询有限行数返回给用户，如下图所示，用户可以选择是否评估结果集，因为评估结果集要执行一条select count(*)的sql语句，如果行数过多这条语句可能会耗时非常多，用户可以根据自己的需要选择是否评估，同时可以设置警戒行数，超过警戒行数的查询只显示警戒行数以内的数据。图 三60 查询引擎环境设置查询预览界面支持将数据导出为txt文本文件，并可进而用EXCEL打开。后面会提到，当查询定义与格式定义绑定之后可以从格式界面导出EXCEL，报表独立节点也支持导出EXCEL。随着产品的发展，数据导出的格式会逐步丰富。6. 查询集成6.1 复合查询复合查询，顾名思义就是基于已定义的查询继续做查询，从这个角度来说，那些已有查询和数据库里的物理表的地位和角色是一样的。复合查询常应用于这样的场景：报表在列向可以被划分成几个分组，每组由若干个列构成，每个列分组的数据能够通过SQL查询获得，而不同列分组的数据出现在同一行的前提是它们对应于同一个行ID（该ID通常是某个字段或某几个字段的组合）。即报表数据可以通过多个查询结果集相互连接的方式获得。图 三61 复合查询原理6.2 物化查询查询引擎可以根据预览数据直接到数据库创建物化表，同时将物化表的结构注册到数据字典（物化表会注册到DDC的 物化临时表 目录下）中，以后的SQL设计就可以基于新创建的物化表进行，从而扩充了查询集成的范围。引用了物化表的查询，用户可以在首选项配置中选择是否在执行时提示同步刷新物化临时表，如果选择了提示刷新，则会按当前的参数设置传递给产生物化表的查询，重新执行得到结果并更新物化表，再在此基础上执行当前查询；如果未选择刷新，则直接基于物化表里的已有数据执行查询。 由一个查询生成物化表，该表会创建到查询定义所在的数据源（如果是数据仓库下，可通过切换定义数据源查看当前所在数据源），而不是查询取数数据源（就是选中查询后在右边属性列表里的数据源）。如果是客户化下的查询引擎，这两者是一致的，因为客户化下的查询引擎只支持单数据源，就是当前登录账套所在数据源。图 三62 创建物化表图 三63 在定义查询时引用创建的物化表物化临时表实际上就是一张数据库中的物理表，一旦用户的业务数据发生了改变，那么刷新物化临时表是一件非常耗时的操作，如果用户的查询中使用了过多的物化临时表并且选择了每次刷新，那么效率就会降低，所以请慎重使用这种功能。只有在查询数据并非经常变化，而且查询引用关系比较复杂的情况下物化临时表才能发挥其最大用途。6.3 合并查询有这样一类固定行列结构表样，在表的不同区域有不同的数据块（这些块之间甚至可能没有什么联系），另外一些区域还放着一些人为性很强的提示性文字（可能根本无法从数据库中查出来），象