U8C服务小锦囊--查询引擎—查询引擎基本操作说明——U8C实施与服务支持部闫静目录第一部分：基本操作..2（一）分配查询引擎管理权限.2（二）创建查询模型.2（三）生成格式设计.33（四）发布报表.45第二部分：自由报表设计.49（一）生成数据集.50（二）创建自由报表.51（三）发布自由报表.58第三部分：手工sql设计报表..601（一）常规应用.60（二）其他应用.64第四部分：各模块单据后台数据表关联关系..692业务场景：查询引擎是面向高级实施人员和专业开发人员的查询建模工具，可以全面支持复杂查询的设计和个性化的报表展现，可能项目在实际应用中，系统预置的报表不满足项目上的需求，那么就可以使用查询引擎自定义报表查询展示相关数据，现进行简单的介绍。第一部分：基本操作（一）分配查询引擎管理权限首先，登录到公司（或集团），在查询引擎权限控制节点，给登录用户分配查询引擎管理的权限，分为全部权限、全部浏览权限以及浏览权限，一般选择全部权限，然后打开查询引擎管理节点，如下图：（二）创建查询模型分配完权限之后，在查询引擎管理节点下创建对象，进行查询模型的设计；34以查询凭证信息为例进行演示；1.添加查询参数参数主要与筛选条件结合使用，查询账表时根据输入的参数值，进行查询展示数据；（注：涉及到根据日期进行筛选的，一般建议期间设置为区间的形式“>=”“<=”）项目说明：数据类型主要支持字符、数值、字符枚举、数值枚举、主键参照、编码参照以及名称参照，使用说明如下：5字符：查询账表时，输入汉字、英文等字符串进行筛选；数值：查询账表时，输入数字进行筛选；字符枚举、数值枚举：在枚举项中，字符与数值之间用@符号相隔，在查询时下拉框选择筛选值，如下：主键参照、编码参照以及名称参照：参照类型，需要在枚举项中参照预置的基本档案，在查询时选择具体的档案值进行查询；参数其他应用：有项目上想要在根据日期查询时，可以在参数中自动带出当前系统登录日期6或者当前系统登录月的第一天的日期，那么只要在参数默认值中设置参数即可，说明如下：第一步：在自定义宏变量中添加以下参数当前登录月的第一天：#orderstarttime#：nc.ui.scm.qe.FirstDayOfCurMonProcessor@当前登录日期：#LoginDate#当前登录月的前一个月的最后一天：#LastDayOfLastMonth#：nc.ui.so.so032.LastDayOfLastMonProcessor@当前登录月前一个月的第一天：#FirstDayOfLastMonth#：nc.ui.so.so032.FirstDayOfLastMonProcessor@第二步：在参数中设置默认值把以上宏变量的参数编码复制到参数的默认值中，如下：72.添加数据表并进行关联在SQL设计，选择表中，有三种添加数据表的方式，数据表、元数据以及元查询。数据表：直接从数据字典中选取进行添加；元数据：直接选择数据字典中的字段，根据选择的字段自动建立关联关系；元查询：添加已经设计好的查询模型作为取数基础；本例以添加数据表的方式举例说明，查询凭证信息，添加以下数据表并进行连接：8连接方式有两种，典型模式和高级模式，典型模式仅支持两张数据表之间每次只关联一个字段，如果想要关联多个字段的话，需要再次添加相同的数据表进行关联（需要注意的是，相同的数据表只能出现在连接表1的列中，连接表2列不可以相同的数据表出现两次），高级模式下可以自行编写sql语句，支持两张表之间关联多个字段信息，按需选择，连接方式包括内连接、左连接、右连接以及全连接，本例使用典型模式进行说明：9数据表连接方式说明：内连接：以两张数据表中相同的字段值进行连接查询，相当于取两张数据表的交集，如下图所示；左连接：以连接语句中左边数据表的数据为基础，查询数据，如下图所示；右连接：以连接语句中右边数据表的数据为基础，查询数据，如下图所示；10全连接：以两张表的所有字段为基础，查询数据，相当于取数据表的并集，如下图所示；3.选择字段在字段表达式中可以写sql语句处理字段展示形式，例如数值类字段保留小数位、字段拼凑显示，都支持使用sql语句进行处理。常用字段处理公式：11a.字段小数位数处理：cast(round(数据表名.字段名,需要保留的小数位数)asdecimal(38,需要保留的小数位数))例如：cast(round(gl_detail.localcreditamount,2)asdecimal(38,2))b.isnull函数：函数形式为isnull(数据表名.字段名,0)例如：isnull(gl_detail.localcreditamount,0)，含义为当字段值为空时，显示为0，建议在字段有四则运算的情况下，都先用该函数处理字段之后，再四则运算。c.casewhenthenelseend函数：此函数主要用在当查询结果为数值时，通过该函数将数值显示为中文，例如生产订单数据表订单类型字段，查询时0、1、2，想要将0、1、2显示为具体的中文，就可以使用该函数处理字段，如下：casewhenmm_mo.ddlx='0'then'普通'whenmm_mo.ddlx='1'then'返修'whenmm_mo.ddlx='2'then'委外'else'0'end最终查询结果显示为普通、返修、委外。d.getColValue公式取值：在字段表达式中也支持使用getColValue公式取对应字段的值，关于此公式如何使用，这里不再赘述，需要注意的是，通过getColValue公式取数的字段，需要生成格式设计之后，才能显示出具体的值。4.筛选条件添加字段之后，再添加筛选条件，同样，筛选条件也支持典型模式和高级模式两种，本例以典型模式进行说明；分为待定筛选和固定筛选，待定筛选主要根据输入的参数查询数据，固定筛选按照设置的固定筛选条件中的“值”进行查询数据：125.排序字段可以对报表的字段进行排序；136.交叉属性系统支持投影交叉和旋转交叉两种方式，现以部门人员信息表为例来说明这两种投影方式。投影交叉：根据行和列的信息，统计满足该行列信息下的数据个数例如以下报表，想要统计在每个部门下有几个员工，就可以使用投影交叉实现；第一步：先设置字段信息，按照姓名进行计数，就需要给姓名字段使用count函数进行处理第二步：设置投影交叉14设置好之后，保存浏览报表，效果如下：旋转交叉：可以针对所有查询结果自动分组显示，还是以以上数据表为例说明第一步：设置旋转交叉，规定行列值以及交叉结果值添加以下字段信息：15浏览报表：16如果“值”统计的是金额的话，可右键，设置交叉值的合计值；浏览报表：177.报表穿透支持直接穿透到具体的单据节点、查询间穿透以及交叉投影穿透。穿透到节点：根据自定义报表的浏览结果直接联查相关单据信息操作如下：第一步：首先需要将单据主键以及单据类型字段显示出来，并将其字段别名设置为LINK_BILLID、LINK_BILLTYPE作为穿透依据，如下（以库存管理的单据为例进行说明）：18第二步：设置穿透条件在穿透规则设计中，增加穿透规则，目标节点选择具体穿透单据的功能节点编码，如不清楚的话，可以在功能注册节点查看单据节点的功能编码，选择对应节点即可，如下：19第三步：浏览报表进行穿透查询20报表主键和单据类型不想要显示的话，可以在生成格式设计之后将字段隐藏，进行穿透查询，如下：21查询间穿透：主要用于多个自定义报表之间进行穿透查询，参与穿透的自定义报表之间必须有相同数据的字段。主要以公司部门表穿透到部门人员表为例进行说明（联查该公司部门下的人员档案）；公司部门表：穿透字段：部门名称22部门人员表：目的穿透字段：部门名称可以实现的效果是通过公司部门表的财务部字段，穿透到部门人员表，展示所有公司下所有财务部的人员，操作如下：第一步：设置公司部门表穿透字段的别名为LINK_BILL+字段名称的形式别名的前缀格式必须为LINK_BILL+字段名称的形式，例如本例中部门名称的别名设置为LINK_BILLDEPTNAME23第二步：设置部门人员表的查询参数第三步：设置查询间穿透规则24在公司部门表设置穿透规则；会自动生成以下代码：25含义是LINK_BILLDEPTNAME选中的具体的值，作为参数传递到目的穿透表的参数bm中，作为过滤筛选条件查询数据。保存之后进行穿透查询：26第四步：其他应用（多条件穿透查询）如果想要将联查结果更加明细，只想联查某一公司财务部的人员，例如本例中只想联查到生产制造公司财务部的人员，操作说明如下：a.设置公司部门表中穿透字段公司名称的别名LINK_BILLUNITNAMEb.在部门人员表中增加相关公司过滤的参数以及筛选条件；27b.手工修改穿透的代码c.穿透查询数据28投影交叉穿透：此功能是投影交叉和穿透联合使用的功能，以上介绍过投影交叉可实现的效果是统计某公司某部门下的人员个数，那么投影交叉穿透可以实现的功能是根据个数直接联查到具体的人员信息，操作如下：目前通过投影交叉，可以得到以下信息：人数统计表：29可以实现的效果是，通过统计的人员个数，直接联查其人员具体信息；人员信息表：第一步：设置人数统计表的投影交叉穿透条件生成以下代码：30第二步：设置人员信息表的查询参数以及筛选条件第三步：设置人员信息表的追加参数信息31在SQL整理设计中，输入以下代码：addWhere((getValue("where")==null)?"1=1":getValue("where").toString());第四步：进行投影交叉穿透查询328.按照基础档案的权限查询自定义报表例如部门档案根据角色设置了资源权限控制，想要在查询时参数中只显示出来有权限的档案进行查询，相关操作说明如下：第一步：先登录集团，设置资源权限以及分配具体的档案权限第二步：查询模型中勾选是否控制数据权限33第三步：查询数据目前系统支持的按权限查询的档案有以下档案：3435（三）生成格式设计设计好查询模型之后，在格式设计下对报表格式进行美化，主要包括对报表字段的处理以及对报表展示格式的美化，现说明如下：1.查询模型生成报表格式；在格式设计下增加对象，引用设计好的查询模型；362.对报表字段显示形式进行处理；点击确定之后，报表格式已经生成，然后再打开格式设计，对字段类型进行定义，包括数值保留小数位数、显示千分符、居中居左显示等，操作如下：第一步：在行列格式按钮下选择列格式设计；37第二步：增加列格式，例如我们想要金额字段保留三位小数，并且字符类数据居中显示；38设置好之后，保存格式；393.对报表整体展示形式进行定义；我们可以先浏览一下设计好的报表，如下：可以看到报表是将所有的字段信息都平铺进行显示了，如果说想要给相同类型的字段信息进行分组显示的话，可以设计列表头格式，现说明如下：第一步：增加列表头设计；4041第二步：选择设计好的查询模型；第三步：对字段进行加工；4243第四步：引用列表头格式；保存格式后浏览报表，最终效果如下：444.增加小计合计在格式设计下，右击表头，选择小计合计，对字段进行处理；455.增加公式列如果想要在格式设计中再增加一列显示数据的话，可以通过公式列的形式实现，比如说统计借贷发生额的差额，操作如下：4647（四）发布报表格式设计完之后，可以对报表进行挂接，发布到具体的业务模块的节点下使用，操作如下：支持发布为独立报表节点、报表管理节点、报表模板节点；独立报表节点显示的是格式设计中的报表样式；报表管理节点，会连同格式设计的文件夹一起进行发布；报表模板节点，在发布的同时可以生成查询模板、账表模板以及账表模板等。48然后登录到集团，在权限分配节点下，给角色分配刚发布的报表的权限；49登录到公司即可看到发布完成的报表；下图是发布为报表管理节点和报表模板节点的效果：报表模板节点：50支持调整查询模板，账表模板以及打印模板：报表管理节点：51第二部分：自由报表设计有项目在格式设计中的账表展示格式不能完全满足项目上的实际需求，就可以使用自由报表进行设计，以下主要以工资报表为例简单介绍数据集以及自由报表格式的设计。（一）生成数据集第一步：使用预先制作的查询模型生成数据集（这里对于查询模型的设计不再赘述，主要介绍数据集以及自由报表的相关格式的设计）52下一步，直到保存；53（二）创建自由报表第一步：生成自由报表54第二步：设计自由报表的展示模式55在自由报表中，使用最多的就是数据集的交叉，以下主要介绍数据交叉以及相关行列合计怎样设计；右击公司名称单元格，管理扩展区域；5657添加报表行列名称，并居中显示；58再次增大扩展区域，添加合计行显示，如下：59行合计：统计字段：需要合计的字段值；统计类型：支持合计、计数、平均、最大、最小来统计；小计范围：可以选择按照具体的字段进行小计；小计范围行合计选择的维度是横向展开的字段；列合计：60统计字段：需要合计的字段值；统计类型：支持合计、计数、平均、最大、最小来统计；小计范围：可以选择按照具体的字段进行小计；小计范围列合计选择的维度是纵向展开的字段；（三）发布自由报表设计好自由报表之后，选择发布为CELL节点，进行发布；61分配自由报表的权限；登录公司查询报表；62第三部分：手工sql设计报表（一）常规应用系统也支持手工录入sql语句设计报表，关于手工sql设计的报表，主要介绍一下怎样在手工sql中添加参数筛选数据。项目上在设计自定义报表时，使用手工录入sql语句的方式进行查询，想要增加参数进行筛选数据，但是在实际使用时发现查询时筛选条件不输入任何值时，查询不出数据，只能在浏览时输入筛选条件具体的值才可以查询，想要实现的效果是当不输入任何筛选条件进行查询时，默认查询所有数据，以下以查询存货单据信息为例主要介绍应该怎样引用参数并查询报表。第一步：在查询模型下增加替换型参数63这里需要增加替换型参数，形如#参数名#，如下：第二步：在查询模型中引用参数我们可以看到在使用向导设计时，增加筛选条件，sql语句会将where条件转换成1=1的形式，如下：64在这种形式下就可以实现当筛选条件不输入任何值时，默认查询出所有符合查询条件的数据，那么只需要在手工设计的sql语句中构造一个类似1=1的筛选条件，就可以实现上述需求。首先，增加完替换型参数之后，在where条件后使用如下形式的条件进行筛选：(1=(casewhen参数isnullor参数=''then1end)or字段名=参数)，例如此报表中筛选单据日期和单据类型名称两个字段，如下：单据日期：因为单据日期是区间的形式，所以设置为(1=(casewhen#djrqksy#isnullor#djrqksy#=''then1end)ordbilldate>=#djrqksy#)and(1=(casewhen#djrqjsy#isnullor#djrqjsy#=''then1end)ordbilldate<=#djrqjsy#)65单据类型名称：(1=(casewhen#djlxmc#isnullor#djlxmc#=''then1end)orbilltypename=#djlxmc#)第三步：进行浏览报表输入条件查询：66不输入条件查询：67（二）其他应用1.使用casewhen函数处理的字段作为筛选条件进行查询数据项目上有可能存在在查询数据时，字段显示为数字，不能显示具体的文字，就会使用68casewhen函数将数字转换为文字进行显示，如下：那么在查询数据时，想要用该字段内容进行筛选，因为数据库中存储的字段值为数值型，但是需要用字符型进行筛选，所以不能像以上参数进行增加，先增加替换型参数然后将语句修改为以下形式：69进行查询数据：702.多选参数值进行筛选数据如果想要多选参数值进行查询数据，例如多选公司查询，实现方式如下：参数设置操作符为in，枚举项为公司目录，并且将该参数设置为必选，本例以多选公司主键为例说明：然后在查询模型中增加公司主键的筛选条件，如下：71查询：72第四部分：数据表关联关系因为在设计自定义报表的过程中，最重要的也是最难的一个部分，就是要知道想要查询的数据是来自于后台的哪张数据表，以及各数据表之间的关联关系，需要先建立关联关系之后，才能准确的查询数据，以下分享一个查询方式，可参考：例如，想要查询采购订单相关信息，可以在查询时录制spr日志，在spr日志中根据from进行定位，找到相关的数据表添加到查询模型中进行关联即可。但是用什么字段关联，怎样找关联关系，以下方法可参考，以一个较简单的采购流程为例说明：73例如查询该流程中请购单后续单据的相关信息；第一步：随便找一张该流程的请购单，按照编码查询请购单，录制spr日志，找到请购单关联的数据表74由以上信息，可以看出请购单存储在po_praybill和po_praybill_b中；第二步：在数据库中根据单据编号查询主表主键和子表主键查询主表主键：selectcpraybillid,*frompo_praybillwherevpraycode=’QG2210150001’，然后记录下cpraybillid值；查询子表主键：selectcpraybill_bid,*frompo_praybill_bwherecpraybillid=’上一步查询出来的主键值’，然后记录下cpraybill_bid值；第三步：按照第二步相同的方法，查询下游单据采购订单的主子表主键进行记录，不再赘述；第四步：根据采购订单主表主键查询子表信息，在子表信息中找与请购单主子表主键，以及请购单编码相同信息的字段，用该字段进行数据表之间的关联即可，所有上下游单据的关联关系都可以用此方式。联系方式：本文档未尽事宜可邮件联系：yanjing17@yonyou.com75
