*&---------------------------------------------------------------------* *& Report Z_INTELLIFY_DISTINCT_VALUES *& v1.00 - 03.01.2015 *& *&---------------------------------------------------------------------* *& Author: Daniel Schindler *& WWW: intellify.de *& Mail: info@intellify.de *& *& Description: Distinct value analysis for se16 tables with *& generic master data selection *& *& If you do not pass the column parameter, you will get the *& table / InfoProvider definition and so the list of possible column values. *& *& Use at your own risk. *&---------------------------------------------------------------------* REPORT Z_INTELLIFY_DISTINCT_VALUES. ********************************************************************** * Definition of possible parameters ********************************************************************** SELECTION-SCREEN BEGIN OF BLOCK SEL1 WITH FRAME TITLE TEXT-001. PARAMETERS: PA_TABLE TYPE STRING DEFAULT '', PA_COL TYPE STRING DEFAULT ''. SELECTION-SCREEN END OF BLOCK SEL1. ********************************************************************** * Definition of needed types, variables, etc. ********************************************************************** TYPES: BEGIN OF TY_TAB, VAL TYPE STRING, CNT TYPE I, TXT TYPE STRING, END OF TY_TAB. FIELD-SYMBOLS: TYPE ANY, TYPE ANY TABLE, TYPE ANY TABLE, TYPE ANY, TYPE ANY. DATA: GV_SUBRC TYPE SY-SUBRC, GT_DD TYPE TABLE OF DD03L, GS_DD LIKE LINE OF GT_DD, GV_TABLE TYPE STRING, GV_COL TYPE STRING, GV_TEXT TYPE STRING. DATA: LT_DATA TYPE TABLE OF TY_TAB, LS_DATA TYPE TY_TAB, DREF TYPE REF TO DATA, DREF2 TYPE REF TO DATA, LV_IO TYPE RSIOBJNM, LT_MASTER TYPE TABLE OF RSDDG_S_MD, LS_MASTER LIKE LINE OF LT_MASTER, LV_OUT TYPE STRING, LV_ID TYPE I, LV_SELECT TYPE STRING, FIELDCATALOG TYPE SLIS_T_FIELDCAT_ALV WITH HEADER LINE, IT_SORTCAT TYPE SLIS_SORTINFO_ALV OCCURS 1. ********************************************************************** * Passing parameters and first checks ********************************************************************** GV_TABLE = PA_TABLE. GV_COL = PA_COL. CONDENSE GV_TABLE. CONDENSE GV_COL. ********************************************************************** * Check if table input is valid ********************************************************************** PERFORM CHECK_INPUT_TABLE. IF GV_SUBRC <> 0. PERFORM SHOW_OUTPUT USING 'TABLE_NOT_FOUND'. EXIT. ENDIF. ********************************************************************** * Check if column input is valid ********************************************************************** PERFORM CHECK_INPUT_COLUMN. IF GV_SUBRC <> 0. PERFORM SHOW_OUTPUT USING 'COLUMN_NOT_FOUND'. EXIT. ENDIF. ********************************************************************** * Get distinct data ********************************************************************** PERFORM GET_DISTINCT_VALUE. IF GV_SUBRC = 0. PERFORM SHOW_OUTPUT USING 'TABLE_WITH_TXT'. ELSEIF GV_SUBRC = 4. PERFORM SHOW_OUTPUT USING 'TABLE_WITHOUT_TXT'. ENDIF. ********************************************************************** * Output ********************************************************************** FORM FIELDCAT USING LV_TEXT TYPE STRING. REFRESH FIELDCATALOG. IF LV_TEXT = 'COLUMN_NOT_FOUND'. FIELDCATALOG-FIELDNAME = 'POSITION'. FIELDCATALOG-SELTEXT_M = 'Position'. FIELDCATALOG-COL_POS = 1. FIELDCATALOG-EMPHASIZE = 'X'. FIELDCATALOG-KEY = 'X'. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'FIELDNAME'. FIELDCATALOG-SELTEXT_M = 'Column'. FIELDCATALOG-COL_POS = 2. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. ELSE. FIELDCATALOG-FIELDNAME = 'VAL'. FIELDCATALOG-SELTEXT_M = 'Key'. FIELDCATALOG-COL_POS = 1. FIELDCATALOG-EMPHASIZE = 'X'. FIELDCATALOG-KEY = 'X'. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'TXT'. FIELDCATALOG-SELTEXT_M = 'Text'. FIELDCATALOG-COL_POS = 2. FIELDCATALOG-OUTPUTLEN = 60. IF LV_TEXT = 'TABLE_WITHOUT_TXT'. FIELDCATALOG-NO_OUT = 'X'. ENDIF. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'CNT'. FIELDCATALOG-SELTEXT_M = 'Count'. FIELDCATALOG-COL_POS = 3. FIELDCATALOG-DO_SUM = 'X'. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. ENDIF. ENDFORM. FORM CHECK_INPUT_TABLE. ********************************************************************** * Get definition if possible ********************************************************************** SELECT POSITION FIELDNAME INTTYPE LENG DECIMALS DOMNAME FROM DD03L ##TOO_MANY_ITAB_FIELDS INTO CORRESPONDING FIELDS OF TABLE GT_DD WHERE TABNAME = GV_TABLE ORDER BY POSITION ASCENDING. GV_SUBRC = SY-SUBRC. ENDFORM. FORM CHECK_INPUT_COLUMN. DATA: LV_COL TYPE STRING. ********************************************************************** * 1. Empty column parameter will show all columns IF GV_COL IS INITIAL OR GV_COL = ''. GV_SUBRC = 8. EXIT. ENDIF. ********************************************************************** * 2. Check if passed column can be found in table definition READ TABLE GT_DD WITH KEY FIELDNAME = GV_COL INTO GS_DD. IF SY-SUBRC = 0. GV_SUBRC = 0. EXIT. ENDIF. ********************************************************************** * 3. Check if passed column is a 0-InfoObject IF GV_COL(1) <> '0'. LV_COL = GV_COL+1. READ TABLE GT_DD WITH KEY FIELDNAME = LV_COL INTO GS_DD. IF SY-SUBRC = 0. GV_SUBRC = 0. EXIT. ENDIF. ENDIF. ********************************************************************** * 4. Check if passed column is a Z-InfoObject CONCATENATE '/BIC' GV_COL INTO LV_COL. READ TABLE GT_DD WITH KEY FIELDNAME = LV_COL INTO GS_DD. IF SY-SUBRC = 0. GV_SUBRC = 0. EXIT. ENDIF. GV_SUBRC = 8. ENDFORM. FORM SHOW_OUTPUT USING LV_TEXT TYPE STRING. CASE LV_TEXT. WHEN 'TABLE_NOT_FOUND'. WRITE: LV_TEXT. WHEN 'COLUMN_NOT_FOUND'. GV_TEXT = LV_TEXT. PERFORM FIELDCAT USING LV_TEXT. CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY' EXPORTING I_CALLBACK_PROGRAM = SY-REPID I_CALLBACK_TOP_OF_PAGE = 'TOP-OF-PAGE' IT_FIELDCAT = FIELDCATALOG[] TABLES T_OUTTAB = GT_DD. WHEN OTHERS. PERFORM FIELDCAT USING LV_TEXT. PERFORM BUILD_SORT. CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY' EXPORTING I_CALLBACK_PROGRAM = SY-REPID I_CALLBACK_TOP_OF_PAGE = 'TOP-OF-PAGE' IT_FIELDCAT = FIELDCATALOG[] IT_SORT = IT_SORTCAT TABLES T_OUTTAB = LT_DATA. ENDCASE. ENDFORM. FORM BUILD_SORT. DATA: WA_SORT LIKE LINE OF IT_SORTCAT. WA_SORT-SPOS = 1. WA_SORT-FIELDNAME = 'CNT'. WA_SORT-DOWN = 'X'. APPEND WA_SORT TO IT_SORTCAT. ENDFORM. *-------------------------------------------------------------------* * Form TOP-OF-PAGE * *-------------------------------------------------------------------* * ALV Report Header * *-------------------------------------------------------------------* FORM TOP-OF-PAGE. *ALV Header declarations DATA: LT_HEADER TYPE SLIS_T_LISTHEADER, LS_HEADER TYPE SLIS_LISTHEADER. * Title IF GV_TEXT = 'COLUMN_NOT_FOUND'. LS_HEADER-TYP = 'H'. LS_HEADER-INFO = 'Column not found'. APPEND LS_HEADER TO LT_HEADER. CLEAR LS_HEADER. ENDIF. * Table LS_HEADER-TYP = 'S'. LS_HEADER-KEY = 'Table: '. LS_HEADER-INFO = GV_TABLE. APPEND LS_HEADER TO LT_HEADER. CLEAR: LS_HEADER. * Column LS_HEADER-TYP = 'S'. LS_HEADER-KEY = 'Column: '. LS_HEADER-INFO = GV_COL. APPEND LS_HEADER TO LT_HEADER. CLEAR: LS_HEADER. * Version LS_HEADER-TYP = 'A'. LS_HEADER-INFO = 'Version: 1.0'. APPEND LS_HEADER TO LT_HEADER. CLEAR: LS_HEADER. * Website LS_HEADER-TYP = 'A'. LS_HEADER-INFO = 'http://www.intellify.de'. APPEND LS_HEADER TO LT_HEADER. CLEAR: LS_HEADER. CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE' EXPORTING IT_LIST_COMMENTARY = LT_HEADER. ENDFORM. *&---------------------------------------------------------------------* *& Form GET_DISTINCT_VALUE *&---------------------------------------------------------------------* FORM GET_DISTINCT_VALUE. DATA: G_END_OF_DATA TYPE RS_BOOL VALUE RS_C_FALSE, G_FIRST_CALL TYPE RS_BOOL VALUE RS_C_TRUE, G_S_SFC TYPE RSDRI_S_SFC, G_TH_SFC TYPE RSDRI_TH_SFC, G_S_SFK TYPE RSDRI_S_SFK, G_TH_SFK TYPE RSDRI_TH_SFK, LV_PROV TYPE RSINFOPROV, LDP_DYN_TABLE TYPE REF TO DATA, LDP_DYN_TABLE2 TYPE REF TO DATA, LOP_TABLEDESCR TYPE REF TO CL_ABAP_TABLEDESCR, LDP_DYNAIC_RECORD TYPE REF TO DATA, LOP_STRUCDESCR TYPE REF TO CL_ABAP_STRUCTDESCR, LS_COMPONENT TYPE ABAP_COMPONENTDESCR, LT_COMPONENT TYPE ABAP_COMPONENT_TAB, LV_LENG TYPE I, LV_DECS TYPE I, LT_DOM TYPE TABLE OF DD07V, LS_DOM LIKE LINE OF LT_DOM. * dynamically create appropriate internal table LV_LENG = GS_DD-LENG. LV_DECS = GS_DD-DECIMALS. LS_COMPONENT-NAME = 'VAL'. CASE GS_DD-INTTYPE . WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_NUM . LS_COMPONENT-TYPE = CL_ABAP_ELEMDESCR=>GET_N( LV_LENG ) . WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_HEX . LS_COMPONENT-TYPE = CL_ABAP_ELEMDESCR=>GET_X( LV_LENG ) . WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_STRING . LS_COMPONENT-TYPE = CL_ABAP_ELEMDESCR=>GET_STRING( ) . WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_XSTRING . LS_COMPONENT-TYPE = CL_ABAP_ELEMDESCR=>GET_XSTRING( ) . WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_DATE . LS_COMPONENT-TYPE = CL_ABAP_ELEMDESCR=>GET_D( ) . WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_TIME . LS_COMPONENT-TYPE = CL_ABAP_ELEMDESCR=>GET_T( ) . WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_INT . LS_COMPONENT-TYPE = CL_ABAP_ELEMDESCR=>GET_I( ) . WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_INT1 . LS_COMPONENT-TYPE ?= CL_ABAP_ELEMDESCR=>DESCRIBE_BY_NAME( 'INT1' ) . WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_INT2 . LS_COMPONENT-TYPE ?= CL_ABAP_ELEMDESCR=>DESCRIBE_BY_NAME( 'INT2' ) . WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_FLOAT . LS_COMPONENT-TYPE = CL_ABAP_ELEMDESCR=>GET_F( ) . WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_DECFLOAT16 . LS_COMPONENT-TYPE = CL_ABAP_ELEMDESCR=>GET_DECFLOAT16( ) . WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_DECFLOAT34 . LS_COMPONENT-TYPE = CL_ABAP_ELEMDESCR=>GET_DECFLOAT34( ) . WHEN CL_ABAP_TYPEDESCR=>TYPEKIND_PACKED . LS_COMPONENT-TYPE = CL_ABAP_ELEMDESCR=>GET_P( P_LENGTH = LV_LENG P_DECIMALS = LV_DECS ) . WHEN OTHERS. LS_COMPONENT-TYPE = CL_ABAP_ELEMDESCR=>GET_STRING( ). ENDCASE. APPEND LS_COMPONENT TO LT_COMPONENT. LS_COMPONENT-NAME = 'CNT'. LS_COMPONENT-TYPE = CL_ABAP_ELEMDESCR=>GET_I( ). APPEND LS_COMPONENT TO LT_COMPONENT. LS_COMPONENT-NAME = 'TXT'. LS_COMPONENT-TYPE = CL_ABAP_ELEMDESCR=>GET_STRING( ). APPEND LS_COMPONENT TO LT_COMPONENT. LOP_STRUCDESCR = CL_ABAP_STRUCTDESCR=>CREATE( LT_COMPONENT ). CREATE DATA LDP_DYNAIC_RECORD TYPE HANDLE LOP_STRUCDESCR. LOP_TABLEDESCR ?= CL_ABAP_TABLEDESCR=>CREATE( P_LINE_TYPE = LOP_STRUCDESCR ). CREATE DATA LDP_DYN_TABLE TYPE HANDLE LOP_TABLEDESCR. CREATE DATA LDP_DYN_TABLE2 TYPE HANDLE LOP_TABLEDESCR. "ASSIGN Ldp_dynaic_record->* to . ASSIGN LDP_DYN_TABLE->* TO . ASSIGN LDP_DYN_TABLE2->* TO . " it is a se16 table CONCATENATE GV_COL ' as VAL count(*) as CNT' INTO LV_SELECT. SELECT DISTINCT (LV_SELECT) FROM (GV_TABLE) INTO CORRESPONDING FIELDS OF TABLE GROUP BY (GV_COL). IF SY-SUBRC = 0 AND IS NOT INITIAL. SORT . " Check if column is an InfoObject and get Master Data IF GV_COL(4) = '/BIC'. LV_IO = GV_COL. REPLACE '/BIC/' WITH '' INTO LV_IO. CONDENSE LV_IO. ELSEIF GV_COL(1) <> '0'. CONCATENATE '0' GV_COL INTO LV_IO. ELSE. LV_IO = GV_COL. ENDIF. CALL FUNCTION 'RSDDG_X_BI_MD_GET' EXPORTING I_IOBJNM = LV_IO I_COUNT_OF_RECORDS = '1000' TABLES E_T_MD = LT_MASTER. IF LT_MASTER IS INITIAL. " Maybe the element has Domain IF GS_DD-DOMNAME IS NOT INITIAL. CALL FUNCTION 'DDIF_DOMA_GET' EXPORTING NAME = GS_DD-DOMNAME STATE = 'A' LANGU = SY-LANGU TABLES DD07V_TAB = LT_DOM EXCEPTIONS ILLEGAL_INPUT = 1 OTHERS = 2. IF SY-SUBRC <> 0. WRITE: 'Error'. EXIT. ENDIF. LOOP AT LT_DOM INTO LS_DOM. LS_MASTER-VALUE = LS_DOM-DOMVALUE_L. LS_MASTER-TEXTLG = LS_DOM-DDTEXT. APPEND LS_MASTER TO LT_MASTER. ENDLOOP. ENDIF. ENDIF. " Write Output LOOP AT ASSIGNING . ASSIGN COMPONENT 'VAL' OF STRUCTURE TO . ASSIGN COMPONENT 'CNT' OF STRUCTURE TO . LV_ID = SY-TABIX. " Get Master Data Value IF LT_MASTER IS NOT INITIAL. READ TABLE LT_MASTER WITH KEY VALUE = INTO LS_MASTER. IF SY-SUBRC = 0. IF LS_MASTER-TEXTLG IS NOT INITIAL. LV_OUT = LS_MASTER-TEXTLG. ELSEIF LS_MASTER-TEXTMD IS NOT INITIAL. LV_OUT = LS_MASTER-TEXTMD. ELSE. LV_OUT = LS_MASTER-TEXTSH. ENDIF. ENDIF. ENDIF. LS_DATA-VAL = . LS_DATA-CNT = . LS_DATA-TXT = LV_OUT. APPEND LS_DATA TO LT_DATA. CLEAR LV_OUT. ENDLOOP. ENDIF. * Show text column or hide? GV_SUBRC = 4. LOOP AT LT_DATA INTO LS_DATA. IF LS_DATA-TXT <> ''. GV_SUBRC = 0. EXIT. ENDIF. ENDLOOP. ENDFORM.