作者 idan lian 如需转载备注出处如果对你有帮助请点赞收藏~~~需求背景客户基于QUERY进行报表展示现需迁移到永洪报表平台query中的变量参数公式等无法直接生成视图query相对复杂不想直接在视图复现想办法取出query的执行结果落地存入ADSO。供报表平台使用解决方案通过函数执行query结果并生成动态内表将数据写入ADSO中落地query如下创建ADSO如下1.创建函数Z_GET_BW_QUERY_DATAFUNCTION Z_GET_BW_QUERY_DATA. *---------------------------------------------------------------------- **本地接口 * IMPORTING * REFERENCE(I_INFOPROVIDER) TYPE RSINFOPROV * REFERENCE(I_QUERY) TYPE RSZCOMPID * REFERENCE(I_VIEW_ID) TYPE RSZVIEWID * REFERENCE(I_T_PARAMETER) TYPE RRXW3TQUERY * EXPORTING * REFERENCE(DY_TABLE) TYPE REF TO DATA *---------------------------------------------------------------------- *Global data declarations DATA wa_parameter TYPE w3query. DATA i_parameter TYPE TABLE OF w3query . DATA wa_query_result TYPE rrws_s_cell . DATA query_result TYPE TABLE OF rrws_s_cell . DATA wa_axis_result TYPE LINE OF rrws_thx_axis_data . DATA axis_result TYPE rrws_thx_axis_data . DATA wa_tx_set_1 TYPE LINE OF rrws_tx_set. DATA tx_set_1 TYPE rrws_tx_set. DATA wa_tx_set_0 TYPE LINE OF rrws_tx_set. DATA tx_set_0 TYPE rrws_tx_set. *解决动态内表最多创建36次的问题 TYPE-POOLS:abap. DATA:ls_components TYPE abap_componentdescr, lt_components TYPE abap_component_tab, lo_elemt_type TYPE REF TO cl_abap_datadescr, lo_type TYPE REF TO cl_abap_typedescr, z_stu TYPE REF TO cl_abap_structdescr, z_tab TYPE REF TO cl_abap_tabledescr, lr_dyn_wa TYPE REF TO data. * DYN_TABLE TYPE REF TO DATA. TYPES: BEGIN OF dzgx, fieldname LIKE lvc_s_fcat-fieldname, fieldnamec(6) TYPE c, END OF dzgx. DATA: wa_mcdz TYPE dzgx, it_mcdz TYPE HASHED TABLE OF dzgx WITH UNIQUE KEY fieldname. DATA: k_num TYPE i, c_num TYPE i, lines_num TYPE i, c1_num TYPE i, it_structure TYPE lvc_t_fcat, wa_structure TYPE lvc_s_fcat, pos TYPE i, line_ps TYPE i, index TYPE i, index1 TYPE i, field_name(6) TYPE c, posc(6) TYPE c, pos1 TYPE i, lv_string TYPE string. DATA:wa_new_line TYPE REF TO data. FIELD-SYMBOLS:dyn_table TYPE STANDARD TABLE, dyn_wa TYPE any, dyn_field TYPE any. DATA:dy_line TYPE REF TO data. CALL FUNCTION RRW3_GET_QUERY_VIEW_DATA EXPORTING i_infoprovider i_infoprovider i_query i_query i_view_id i_view_id i_t_parameter i_t_parameter IMPORTING * E_AXIS_INFO e_cell_data query_result e_axis_data axis_result * E_TXT_SYMBOLS EXCEPTIONS no_applicable_data 1 invalid_variable_values 2 no_authority 3 abort 4 invalid_input 5 invalid_view 6 OTHERS 7. IF sy-subrc 0. IF query_result IS INITIAL AND axis_result IS INITIAL. RETURN. ELSE. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. ENDIF. pos 1. READ TABLE axis_result INTO wa_axis_result WITH KEY axis 001. tx_set_1[] wa_axis_result-set[] . DELETE tx_set_1 WHERE tuple_ordinal 000000. DESCRIBE TABLE tx_set_1 LINES c_num. LOOP AT tx_set_1 INTO wa_tx_set_1. posc pos. CONDENSE posc. CONCATENATE C posc INTO field_name. wa_structure-fieldname field_name. wa_structure-col_pos pos. 表数 wa_structure-inttype C. 数据类型 wa_structure-intlen 30. 长度 wa_mcdz-fieldname wa_tx_set_1-chanm. wa_mcdz-fieldnamec field_name. INSERT wa_mcdz INTO TABLE it_mcdz. pos pos 1. APPEND wa_structure TO it_structure. ENDLOOP. READ TABLE axis_result INTO wa_axis_result WITH KEY axis 000. DESCRIBE TABLE wa_axis_result-set LINES k_num. tx_set_0[] wa_axis_result-set[]. pos1 1. LOOP AT tx_set_0 INTO wa_tx_set_0. . posc pos1. CONDENSE posc. CONCATENATE V posc INTO field_name. wa_structure-fieldname field_name. wa_structure-col_pos pos. 表数 wa_structure-inttype C. 数据类型 wa_structure-intlen 30. 长度 pos pos 1. pos1 pos1 1. APPEND wa_structure TO it_structure. ENDLOOP. * 连续取36次 创建36次动态内表 就会dump * CALL METHOD CL_ALV_TABLE_CREATECREATE_DYNAMIC_TABLE * EXPORTING * IT_FIELDCATALOG IT_STRUCTURE * IMPORTING * EP_TABLE DY_TABLE. LOOP AT it_structure INTO wa_structure . ls_components-name wa_structure-fieldname. lv_string /BIC/AZT_MMD012-BWAPPLNM.”替换成现有系统长一点的数据库表字段就行 CALL METHOD cl_abap_datadescrdescribe_by_name EXPORTING p_name lv_string RECEIVING p_descr_ref lo_type EXCEPTIONS type_not_found 1 OTHERS 2. ls_components-type ? lo_type. APPEND ls_components TO lt_components. ENDLOOP. CALL METHOD cl_abap_structdescrcreate EXPORTING p_components lt_components RECEIVING p_result z_stu. CALL METHOD cl_abap_tabledescrcreate EXPORTING p_line_type z_stu RECEIVING p_result z_tab. 参照动态结构类型和动态内表类型创建内表与工作区 CREATE DATA lr_dyn_wa TYPE HANDLE z_stu. CREATE DATA dy_table TYPE HANDLE z_tab. 指定内表与工作区到字段符号 ASSIGN lr_dyn_wa-* TO dyn_wa. ASSIGN dy_table-* TO dyn_table. READ TABLE axis_result INTO wa_axis_result WITH KEY axis 001. tx_set_1[] wa_axis_result-set[] . DESCRIBE TABLE tx_set_1 LINES c1_num. lines_num c1_num / c_num. line_ps 1. WHILE line_ps lines_num. index c_num * ( line_ps - 1 ) 1. DO c_num TIMES. pos 1. WHILE pos c_num. index1 index pos - 1. READ TABLE tx_set_1 INTO wa_tx_set_1 INDEX index1 . posc pos. CONDENSE posc. CONCATENATE C posc INTO field_name. READ TABLE it_mcdz INTO wa_mcdz WITH KEY fieldname wa_tx_set_1-chanm. wa_structure-fieldname wa_mcdz-fieldnamec. ASSIGN COMPONENT wa_structure-fieldname OF STRUCTURE dyn_wa TO dyn_field. *之前取文本改成取指标 * IF wa_tx_set_1-caption . * dyn_field wa_tx_set_1-chavl. * ELSE. * dyn_field wa_tx_set_1-caption. * ENDIF. dyn_field wa_tx_set_1-chavl. pos pos 1. ENDWHILE. ENDDO. index k_num * ( line_ps - 1 ) 1. DO k_num TIMES. pos 1. WHILE pos k_num. READ TABLE tx_set_0 INTO wa_tx_set_0 INDEX pos . posc pos. CONDENSE posc. CONCATENATE V posc INTO field_name. wa_structure-fieldname field_name. ASSIGN COMPONENT wa_structure-fieldname OF STRUCTURE dyn_wa TO dyn_field. index1 index pos - 1. READ TABLE query_result INTO wa_query_result INDEX index1 . * DYN_FIELD WA_QUERY_RESULT-VALUE. dyn_field wa_query_result-formatted_value. *改成取带单位的单元格数据 pos pos 1. ENDWHILE. ENDDO. APPEND dyn_wa TO dyn_table. CLEAR dyn_wa. line_ps line_ps 1. ENDWHILE. ENDFUNCTION.2.基于函数创建程序 Z_GET_ZDEMO*---------------------------------------------------------------------* * Report Z_GET_ZDEMO *---------------------------------------------------------------------* * *---------------------------------------------------------------------* REPORT Z_GET_ZDEMO. TABLES:/BIC/AZTDEMO2. *PARAMETER:z_fsr_f TYPE /bic/azfiglc046-fiscper OBLIGATORY . *PARAMETER:z_fsr_t TYPE /bic/azfiglc046-fiscper . *SELECT-OPTIONS: z_comp FOR /bic/azfiglc046-/bic/zcomp_cod. *根据query生成的动态结构定义维度为C指标为V有多少条创建多少个 TYPES: BEGIN OF line, c1(100) TYPE c, c2(100) TYPE c, c3(100) TYPE c, c4(100) TYPE c, c5(100) TYPE c, c6(100) TYPE c, c7(100) TYPE c, c8(100) TYPE c, c9(100) TYPE c, v1(30) TYPE c, v2(30) TYPE c, v3(30) TYPE c, v4(30) TYPE c, v5(30) TYPE c, v6(30) TYPE c, END OF line. DATA: wa_line TYPE line, lt_dso TYPE TABLE OF /BIC/AZTDEMO2, ls_dso TYPE /BIC/AZTDEMO2, wa_parameter TYPE w3query, i_parameter TYPE TABLE OF w3query, dyn_table TYPE REF TO data. FIELD-SYMBOLS: dyn_table TYPE STANDARD TABLE, dyn_wa TYPE any, dyn_field TYPE any. DATA:e_lines_inserted TYPE int4, et_msg TYPE rs_t_msg, e_upd_req_tsn TYPE rspm_request_tsn, et_act_req_tsn TYPE rsdso_t_tsn. INITIALIZATION. * SELECT * SINGLE * zdate INTO DATA(lv_zdate) * FROM ztcon_bpc02 . * * z_fsr_f lv_zdate0(4) 001 . * * z_fsr_t lv_zdate0(4) 0 lv_zdate4(2). *----------------------------------------------------------------------* * 数据处理过程 * *----------------------------------------------------------------------* START-OF-SELECTION. *要先将要更新的模型内的公司库存数据 R掉 在插入新的,在where后面加入期间限制即可按月刷新ASDO.。 SELECT PLANT, COORDER, MATERIAL, /BIC/ZCALMONTH, R AS RECORDMODE, /BIC/ZCALYEAR, COMP_CODE, BASE_UOM, /BIC/ZCAMONTH2, LOC_CURRCY, /BIC/ZMENGE1, CPQUABU, /BIC/ZAHGPSL, /BIC/ZABHGSL, /BIC/ZACZ, /BIC/ZACZHFG INTO TABLE DATA(lt_r) FROM /BIC/AZTDEMO2. * SELECT * DISTINCT * /bic/zcomp_cod * INTO TABLE DATA(lt_comp) * FROM /bic/azfiglc046 * WHERE /bic/zcomp_cod IN z_comp * AND /bic/zcomp_cod * ORDER BY /bic/zcomp_cod. * IF z_comp IS INITIAL. * CLEAR lt_comp. * INSERT INITIAL LINE INTO lt_comp INDEX 1. * * ENDIF. * LOOP AT lt_comp INTO DATA(ls_comp). * * CLEAR : i_parameter. * *query input变量赋值 * wa_parameter-name VAR_NAME_1. * wa_parameter-value ZCOMP. * APPEND wa_parameter TO i_parameter. * * wa_parameter-name VAR_VALUE_EXT_1. * wa_parameter-value ls_comp-/bic/zcomp_cod . * APPEND wa_parameter TO i_parameter. * * wa_parameter-name VAR_NAME_2. * wa_parameter-value Z1FISPER04. * APPEND wa_parameter TO i_parameter. * * wa_parameter-name VAR_VALUE_LOW_EXT_2. * wa_parameter-value z_fsr_f. * APPEND wa_parameter TO i_parameter. * * wa_parameter-name VAR_VALUE_HIGH_EXT_2. * wa_parameter-value z_fsr_t. * * * APPEND wa_parameter TO i_parameter. * wa_parameter-name VAR_NAME_1.ZCVC_ZCALMONTH_019 * wa_parameter-value ZCVC_ZCALMONTH_019. * APPEND wa_parameter TO i_parameter. wa_parameter-name VAR_VALUE_EXT_1.ZCVC_ZCALMONTH_019 wa_parameter-value 202406. APPEND wa_parameter TO i_parameter. wa_parameter-name VAR_NAME_2.ZCVC_ZCALMONTH_019 wa_parameter-value ZCVC_ZCATEGORY_001. APPEND wa_parameter TO i_parameter. * wa_parameter-name VAR_VALUE_EXT_2.版本(单值出口,以输入月份所在月的版本) wa_parameter-value V01 . APPEND wa_parameter TO i_parameter. * wa_parameter-name VAR_NAME_3.数据来源(单值,可选出口) wa_parameter-value ZCVC_ZASJLY_001. APPEND wa_parameter TO i_parameter. wa_parameter-name VAR_VALUE_EXT_3.数据来源(单值,可选出口) wa_parameter-value LOAD. APPEND wa_parameter TO i_parameter. call function Z_GET_BW_QUERY_DATA EXPORTING i_infoprovider ZTDEMO“”这里是信息对象 i_query ZTEST1“这里是query” I_VIEW_ID i_t_parameter i_parameter * DESTINATION IMPORTING dy_table dyn_table. * IF dyn_table IS INITIAL. * CONTINUE. * ENDIF. ASSIGN dyn_table-* TO dyn_table. *2、将BW查询结果集写入直接更新DSO LOOP AT dyn_table ASSIGNING dyn_wa. MOVE-CORRESPONDING dyn_wa TO wa_line. * ls_dso-z_fiscper_from z_fsr_f. * ls_dso-z_fiscper_to z_fsr_t. * ls_dso-/bic/zcomp_cod wa_line-c1. * ENDCASE. * 去除单位逗号等 DATA: LV_VN TYPE CHAR10, LV_I TYPE I. FIELD-SYMBOLS:F1,F2. ASSIGN wa_line TO F1. 动态处理V1到V140,去除单位及逗号 CLEAR LV_I. DO 140 TIMES. LV_I LV_I 1. LV_VN V LV_I. ASSIGN COMPONENT LV_VN OF STRUCTURE F1 TO F2. “如果query已经无量纲公式处理此处无单位去除逗号即可” REPLACE ALL OCCURRENCES OF , IN F2 WITH . ENDDO. ls_dso-LOC_CURRCY wa_line-c1. ls_dso-/BIC/ZCALYEAR wa_line-c2. ls_dso-/BIC/ZCALMONTH wa_line-c3. ls_dso-/BIC/ZCAMONTH2 wa_line-c4. ls_dso-COMP_CODE wa_line-c5. ls_dso-BASE_UOM wa_line-c6. ls_dso-PLANT wa_line-c7. ls_dso-COORDER wa_line-c8. ls_dso-MATERIAL wa_line-c9. ls_dso-/BIC/ZMENGE1 wa_line-v1. ls_dso-CPQUABU wa_line-v2. ls_dso-/BIC/ZAHGPSL wa_line-v3. ls_dso-/BIC/ZABHGSL wa_line-v4. ls_dso-/BIC/ZACZ wa_line-v5. ls_dso-/BIC/ZACZHFG wa_line-v6. APPEND ls_dso TO lt_dso. CLEAR ls_dso. ENDLOOP. CLEAR: dyn_table. * ENDLOOP. *先将R掉的请求号 添加到模型内 CALL FUNCTION RSDSO_WRITE_API EXPORTING i_adsonm ZTDEMO * I_ALLOW_NEW_SIDS RS_C_TRUE * I_ACTIVATE_DATA RS_C_TRUE 激活 * IT_AGGREGATION LT_AGGREGATION it_data lt_r IMPORTING e_lines_inserted e_lines_inserted et_msg et_msg e_upd_req_tsn e_upd_req_tsn et_act_req_tsn et_act_req_tsn EXCEPTIONS write_failed 1 activation_failed 2 datastore_not_found 3 OTHERS 4. CASE sy-subrc. * WHEN 0. * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4 RAISING CHAIN_FAILED. WHEN 1. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 RAISING write_failed. WHEN 2. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 RAISING activation_failed. WHEN 3. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 RAISING datastore_not_found. ENDCASE. *再将更新的数据 添加到请求内 CALL FUNCTION RSDSO_WRITE_API EXPORTING i_adsonm ZTDEMO * I_ALLOW_NEW_SIDS RS_C_TRUE I_ACTIVATE_DATA RS_C_TRUE 激活 * IT_AGGREGATION LT_AGGREGATION it_data lt_dso IMPORTING e_lines_inserted e_lines_inserted et_msg et_msg e_upd_req_tsn e_upd_req_tsn et_act_req_tsn et_act_req_tsn EXCEPTIONS write_failed 1 activation_failed 2 datastore_not_found 3 OTHERS 4. CASE sy-subrc. * WHEN 0. * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4 RAISING CHAIN_FAILED. WHEN 1. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 RAISING write_failed. WHEN 2. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 RAISING activation_failed. WHEN 3. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 RAISING datastore_not_found. ENDCASE. *Selection texts *---------------------------------------------------------- * Z_COMP 公司代码(不输入公司代码将更新所有公司) * Z_FSR_F 会计期间始 * Z_FSR_T 会计期间止3.注意点注意点1query中指标必须指标放在上面维度放在下面把结果行拿掉佛则赋值会出现问题注意点2动态内表的生成顺序和query的顺序是一致的后面如果改query字段顺序就会有问题需要改相应的赋值逻辑。注意点3获取的query数据默认是char类型如果没有在query处理带单位的金额会带单位需要出处理注意点4传参格式比较特殊注意写法不同的参数有不同的NAME格式Characteristic Value VariablesParameter variables or variables for several single values are predefined as follows:ParameterDescriptionVAR_NAME_ITechnical name of the variablesVAR_VALUE_EXT_IAttribute value in external displayKey for characteristic value in external displayInterval variables are predefined as in the following:ParameterDescriptionVAR_NAME_ITechnical name of the variablesVAR_VALUE_LOW_EXT_I“From” characteristic value in external displayKey for characteristic value in external displayVAR_VALUE_HIGH_EXT_I“To” characteristic value in external displayKey for characteristic value in external displaySelection option variables are predefined as in the following:parameterDescriptionVAR_NAME_ITechnical name of the variablesVAR_OPERATOR_IAn operatorEQ Individual valueBT IntervalLT Less thanLE Less than or equal toGT Greater thanGE Greater than or equal toVAR_VALUE_LOW_EXT_I“From” characteristic value in external displayKey for characteristic value in external displayVAR_VALUE_HIGH_EXT_I“To” characteristic value in external displayKey for characteristic value in external displayThis value must only be specified with VAR_OPERATORBT.VAR_SIGN_IRow propertiesI found values are added,E found values are removed.Variables for precalculated value sets are preassigned as follows:ParameterDescriptionVAR_NAME_ITechnical name of the variablesVAR_VALUE_EXT_IName of value setVariables for Single Hierarchy Nodes or Variables for Several Hierarchy NodesParameterDescriptionVAR_NAME_ITechnical name of the variablesVAR_VALUE_EXT_INode key in external displayKey for hierarchy nodeVAR_NODE_IOBJNM_INode characteristic nameWith characteristic nodes and text nodes, you have to specify the characteristic name (0HIER_NODE).Hierarchy, Formula, and Text VariablesParameterDescriptionVAR_NAME_ITechnical name of the variablesVAR_VALUE_EXT_IHierarchy name, formula value, text