历史版本26 :通用查询取数(适用于ECC6以上版本) 返回文档
编辑时间:
内容长度:图片数:目录数:
修改原因:
1. 概述编辑
通用查询比 简单通用查询 增加了表间关联的能力,且支持多表查询。但操作起来会复杂一些,完全通过 open SQL 语句实现。
需要在 ECC6 以上的版本,并在 SAP 系统中添加一个帆软提供的 ABAP Function。
2. 添加 ABAP Function编辑
我方的 ABAP Function 的名称为「EXTRACT_TABLE_DATA」,不可改变,将其添加到 SAP 系统中,函数内容如下:
FUNCTION EXTRACT_TABLE_DATA.*"----------------------------------------------------------------------*"*"Local Interface:*" TABLES*" FIELDS STRUCTURE ZSQL_CLAUSE_ELEMENTS*" FROMCLAUSE STRUCTURE ZSQL_CLAUSE_ELEMENTS*" WHERECLAUSE STRUCTURE ZSQL_CLAUSE_ELEMENTS*" DATA STRUCTURE ZTABLEROWS*"----------------------------------------------------------------------*"----------------------------------------------------------------------*" Copy selected fields from QUERY_TABLE to DATA_STRUCTURE*"---------------------------------------------------------------------- TYPE-POOLS: abap. DATA: columnName TYPE SO_TEXT, fieldDataDescrRef TYPE REF TO abap_componentdescr, numberFields TYPE i, fieldDescr TYPE abap_componentdescr, fieldname TYPE string, fieldDescrTab TYPE abap_component_tab, rowStructDescr TYPE REF TO cl_abap_structdescr, rowReference TYPE REF TO data, returnRowString TYPE string, dataFieldString TYPE string, dataline LIKE data, fromClauseRow TYPE ZALBUS_STRUCT_WHERECLAUSE, fromClauseString TYPE string, whereClauseRow TYPE ZALBUS_STRUCT_WHERECLAUSE, whereClauseString TYPE string, fieldsRow TYPE ZALBUS_STRUCT_WHERECLAUSE. FIELD-SYMBOLS: <datarow> TYPE ANY, <datafield> TYPE ANY. * CREATE DataStructure with field names * Datatypes are read from fieldnames of FIELDS input table DESCRIBE TABLE FIELDS LINES numberFields. LOOP AT FIELDS INTO fieldsRow. fieldname = SY-TABIX.* names need to be unique and must start with a char CONCATENATE 'string' fieldname INTO fieldname. CONDENSE fieldname. fieldDescr-name = fieldname.* for dictionary lookup we need to change columnnames from Open SQL* to dictionary notation columnName = fieldsRow-TEXT. REPLACE FIRST OCCURRENCE OF SUBSTRING '~' IN columnName WITH '-' RESPECTING CASE. fieldDescr-type ?= cl_abap_typedescr=>describe_by_name( columnName ). APPEND fieldDescr TO fieldDescrTab. ENDLOOP. rowStructDescr = cl_abap_structdescr=>create( fieldDescrTab ).* now we create the actual data structure in memory create data rowReference type HANDLE rowStructDescr.* finally we assign it to the Field-symbol used by the select statement ASSIGN rowReference->* TO <datarow>.* End Create DataStructure* to simplify calls we concatenate from and whereclause into strings* this way caller doesn't need to check word wrappings fromClauseString = ''. LOOP AT FROMCLAUSE INTO fromClauseRow. CONCATENATE fromClauseString fromClauseRow-TEXT INTO fromClauseString. ENDLOOP. whereClauseString = ''. LOOP AT WHERECLAUSE INTO whereClauseRow. CONCATENATE whereClauseString whereClauseRow-TEXT INTO whereClauseString. ENDLOOP.* Now start actual select operation SELECT (FIELDS) FROM (fromClauseString) INTO <datarow> WHERE (whereClauseString).* we read all fields of the current row, cast it to string and* concatenate it into a dataline with division chars. CLEAR: returnRowString. DO numberFields TIMES. ASSIGN component sy-index of structure <datarow> to <datafield>. dataFieldString = <datafield>. CONCATENATE returnRowString '^' datafieldstring INTO returnRowString. ENDDO. dataline = returnRowString.* finally dataline is added to the return table. INSERT dataline INTO TABLE data. ENDSELECT.ENDFUNCTION.
注1:若出现多个字段到一列的情况,需要将代码中的一行:CONCATENATE returnRowString '^' datafieldstring INTO returnRowString. 替换成 CONCATENATE returnRowString '|' datafieldstring INTO returnRowString.
注2:sap 数据集支持结构型参数。
3. 示例编辑
3.1 添加 SAP 数据集
1)添加 SAP 数据集,如下图所示:
2)选择数据连接,并将 Function 改为「简单通用查询」,如下图所示:
Select:放的是要查的表以及字段,格式是:表~字段名(如果同一个表中多个字段,则用多个表~字段名形式写,逗号隔开)
From:放的是表名以及表与表之间的关联关系;
Where:放筛选条件;
3.2 输入查询语句
按照上面的格式做一个简单示例,数据集配置如下图所示:
简单示例中的查询语句如下:
语句 | 解释 | |
---|---|---|
Select | T001K~MANDT,T001W~BWKEY,T001K~XEFRE | 选择 表T001K 的「MANDT」和「XEFRE」列和 表T001W 的「BWKEY」列 |
From | T001K INNER JOIN T001W on T001K~BWKEY = T001W~BWKEY | 选择表,这里选择了 表T001K 和 表T001W,并将它们做了关联 |
Where | T001K~MANDT <>'' AND T001K~XEFRE = '${a}' | 筛选出「MANDT」不为空,且「XEFRE」值等于参数值的数据 |
3.3 预览数据
点击「预览」按钮,可查看上面语句执行的结果,如下图所示:
3.4 完成添加
预览没问题后,点击「确定」,就可以将数据集添加到 FineReport 中,如下图所示:
FineReport 提供了 EXTRACT_TABLE_DATA 的源码及后续的数据集使用过程。
EXTRACT_TABLE_DATA 的创建过程及出现的问题解决请参见:通用查询取数中的函数