Summary
The document illustrates the detailed steps to list the bex queries based on the process chain.
Table of Contents
Introduction
A process chain is an automated sequence of events that help in loading data to info providers which further help in reporting. There are various reports built on info providers which facilitates viewing of this data possible as per business requirement.
Business Scenario:
Many a times in support project we come across a scenario where process chains fail or get delayed above the threshold time and BI reports are not refreshed on time. In such scenarios, we are required to inform business about the delay in data refresh and reports affected so as to avoid the business lose their precious time.
The time spent in finding the bex reports from the process chain involves quite long steps and is often tedious and may further involve more work if the design of process chain is complex.
The below document tries to ease the process with help of small program.
Tables Used
Below are the various tables that would be often used in the program.
| Table Name | Desciption |
|---|---|
| RSPCCHAIN | Process chain details |
| RSBKDTP | BW: Data Transfer Process Header Data |
| RSRREPDIR | Directory of all reports (Query GENUNIID) |
| RSDCUBEMULTI | Info Cubes involved in a Multi Cube |
| RSBCTCB_QRY_T_V | View containing text for bex queries. |
Manual Steps to find Bex Queries:
Manual steps to find bex queries from Process Chain would involve various as steps listed below.
Step 1. Find all the info providers (targets) which are being loaded through process chain using tables RSPCCHAIN and RSBKDTP.
Step 2. The next step would involve finding the multiproviders for the info providers found in step 1 using table RSDCUBEMULTI.
Step 3. Bex queries could be built on any type of the info providers and hence we would be using result of step 1 and step 2 in table RSRREPDIR to find list of all bex queries,
Step 4. The final step would involve the use of view RSBCTCB_QRY_T_V to find the text for the bex queries.
Automating Steps to find Bex Queries.
As seen, the above steps are quite tedious and involves lot of efforts, the below program takes care of this and provides a one stop solution to handle same.
The final layout would look something like below:
I have provided a radio button to display info providers and Bex Queries in different layouts.
Main Program
We would start with creating the program as below:
REPORT ZPROCES_CHAIN_BEX_INFOPROVIDER.
INCLUDE zpcbi_top. “Would contain all the declarations.
INCLUDE zpcbi_forms. “Would contain all the performs
AT SELECTION-SCREEN ON BLOCK b3.
PERFORM validate_chain. "Validation for process chain"
START-OF-SELECTION.
PERFORM fetch_data.
“We will use a variable lv_flag to check what selection user has selected and process accordingly.
IF lv_flag IS NOT INITIAL AND r_bexqr = 'X'.
PERFORM build_fieldcatlog.
PERFORM display_alv.
ELSEIF lv_flag IS NOT INITIAL AND r_infop = 'X'.
PERFORM build_fieldcatlog_p.
PERFORM display_alv_p.
ELSE.
MESSAGE i000(zbw_msg).
LEAVE LIST-PROCESSING.
ENDIF.
Declarations:
We would require various internal tables for calculations, listed as below, this should be part of TOP include
TYPES: BEGIN OF i_tab,
chain_id TYPE rspcchain-chain_id,
dtp_var TYPE rspcchain-variante,
END OF i_tab,
BEGIN OF i_dtp,
dtp TYPE rsbkdtp-dtp,
trgt TYPE rsbkdtp-tgt,
END OF i_dtp,
BEGIN OF i_multi,
multiprovider TYPE rsdcubemulti-infocube,
partcube TYPE rsdcubemulti-partcube,
END OF i_multi,
BEGIN OF ty_temp,
dtp TYPE rsbkdtp-dtp,
cubemulti TYPE rsdcubemulti-partcube,
END OF ty_temp,
BEGIN OF ty_temp1,
dtp TYPE rsbkdtp-dtp,
infoobj TYPE rsdiobj-iobjnm,
END OF ty_temp1,
BEGIN OF ty_technm,
techname TYPE rsrrepdir-compid,
infocube TYPE rsrrepdir-infocube,
END OF ty_technm,
BEGIN OF ty_final,
techname TYPE rsbctcb_qry_t_v-compid,
descr TYPE rsbctcb_qry_t_v-txtlg,
END OF ty_final,
BEGIN OF ty_final1,
chain_id TYPE rspcchain-chain_id,
techname TYPE rsbctcb_qry_t_v-compid,
descr TYPE rsbctcb_qry_t_v-txtlg,
END OF ty_final1,
BEGIN OF ty_final2,
chain_id TYPE rspcchain-chain_id,
infoobj TYPE rsdiobj-iobjnm,
dso TYPE rsdcubemulti-partcube,
cube TYPE rsdcubemulti-partcube,
multiprovider TYPE rsdcubemulti-infocube,
counter TYPE i,
END OF ty_final2,
BEGIN OF i_infoobj_tbl,
infoobj TYPE rsdiobj-iobjnm,
chain_id TYPE rspcchain-chain_id,
counter TYPE i,
END OF i_infoobj_tbl,
BEGIN OF i_dso_tbl,
dso TYPE rsdcubemulti-partcube,
chain_id TYPE rspcchain-chain_id,
counter TYPE i,
END OF i_dso_tbl,
BEGIN OF i_cube_tbl,
cube TYPE rsdcubemulti-partcube,
chain_id TYPE rspcchain-chain_id,
counter TYPE i,
END OF i_cube_tbl,
BEGIN OF i_multip_tbl,
multiprovider TYPE rsdcubemulti-infocube,
chain_id TYPE rspcchain-chain_id,
counter TYPE i,
END OF i_multip_tbl.
DATA:
it_table TYPE STANDARD TABLE OF i_tab,
it_dtp_tbl TYPE STANDARD TABLE OF i_dtp,
it_multi_tbl TYPE STANDARD TABLE OF i_multi,
it_tmp_tbl TYPE STANDARD TABLE OF ty_temp,
it_tmp_tbl1 TYPE STANDARD TABLE OF ty_temp1,
it_technm_tbl TYPE STANDARD TABLE OF ty_technm,
it_technm_tbl1 type STANDARD TABLE OF ty_technm,
it_final TYPE STANDARD TABLE OF ty_final,
it_final1 TYPE STANDARD TABLE OF ty_final1,
it_final2 TYPE STANDARD TABLE OF ty_final2,
it_infoobj TYPE STANDARD TABLE OF i_infoobj_tbl,
it_dso TYPE STANDARD TABLE OF i_dso_tbl,
it_cube TYPE STANDARD TABLE OF i_cube_tbl,
it_multip TYPE STANDARD TABLE OF i_multip_tbl.
DATA: fieldcatalog TYPE STANDARD TABLE OF slis_fieldcat_alv,
wa_fldcat TYPE slis_fieldcat_alv,
wa_result TYPE ty_final1,
wa_multip TYPE i_multip_tbl,
wa_result2 TYPE ty_final2,
wa_technm_tbl type ty_technm,
gd_repid LIKE sy-repid,
g_save TYPE c VALUE 'X',
g_variant TYPE disvariant,
gx_variant TYPE disvariant,
g_exit TYPE c,
lv_flag TYPE i,
lv_infoobj TYPE i,
lv_dso TYPE i,
lv_cube TYPE i,
lv_multip TYPE i,
lv_max TYPE i,
lv_inc TYPE i.
FIELD-SYMBOLS:
<fld_sym> TYPE ty_final,
<fld_sym1> TYPE i_tab,
<fld_sym2> TYPE ty_final2,
<tech_fld_sym> type ty_technm,
<dtp_fldsym> TYPE i_dtp,
<fld_sym_multi> TYPE i_multi,
<infoobj_fld_sym> TYPE i_infoobj_tbl,
<dso_fld_sym> TYPE i_dso_tbl,
<cube_fld_sym> TYPE i_cube_tbl,
<multip_fld_sym> TYPE i_multip_tbl.
**SELECTION SCREEN DETAILS
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-002.
PARAMETERS: r_bexqr RADIOBUTTON GROUP rad1 DEFAULT 'X', "Bex Query
r_infop RADIOBUTTON GROUP rad1. "Infoprovider
SELECTION-SCREEN END OF BLOCK b2.
SELECTION-SCREEN BEGIN OF BLOCK b3 WITH FRAME TITLE text-003.
PARAMETERS: pc_name TYPE rspcchain-chain_id.
SELECTION-SCREEN END OF BLOCK b3.
SELECTION-SCREEN END OF BLOCK b1.
The Logic:
The logic would be written as below
FORM fetch_data .
SELECT chain_id
variante FROM rspcchain
INTO TABLE it_table
WHERE chain_id = pc_name AND
objvers = 'A' AND
type = 'DTP_LOAD'.
SELECT dtp tgt
FROM rsbkdtp
INTO TABLE it_dtp_tbl
FOR ALL ENTRIES IN it_table
WHERE dtp = it_table-dtp_var AND
objvers = 'A'.
it_tmp_tbl[] = it_dtp_tbl[].
it_tmp_tbl1[] = it_dtp_tbl[].
SELECT infocube partcube FROM rsdcubemulti
INTO TABLE it_multi_tbl
FOR ALL ENTRIES IN it_tmp_tbl
WHERE partcube = it_tmp_tbl-cubemulti AND
objvers = 'A'.
" select queries on dso, infoobject or cube
if it_tmp_tbl is not INITIAL.
SELECT compid infocube FROM rsrrepdir
INTO TABLE it_technm_tbl
FOR ALL ENTRIES IN it_tmp_tbl
WHERE infocube = it_tmp_tbl-cubemulti AND "it_multi_tbl-multiprovider AND
objvers = 'A'.
endif.
" select queries based on multiproviders
if it_multi_tbl is not INITIAL.
SELECT compid infocube FROM rsrrepdir
INTO TABLE it_technm_tbl1
FOR ALL ENTRIES IN it_multi_tbl
WHERE infocube = it_multi_tbl-multiprovider AND
objvers = 'A'.
endif.
loop at it_technm_tbl1 ASSIGNING <tech_fld_sym>.
wa_technm_tbl = <tech_fld_sym>.
append wa_technm_tbl to it_technm_tbl.
endloop.
SELECT compid txtlg FROM rsbctcb_qry_t_v
INTO TABLE it_final
FOR ALL ENTRIES IN it_technm_tbl
WHERE compid = it_technm_tbl-techname AND
objvers = 'A'.
IF it_final[] IS NOT INITIAL.
LOOP AT it_final ASSIGNING <fld_sym>.
wa_result-chain_id = pc_name.
wa_result-techname = <fld_sym>-techname.
wa_result-descr = <fld_sym>-descr.
APPEND wa_result TO it_final1.
ENDLOOP.
ENDIF.
SELECT iobjnm FROM rsdiobj INTO TABLE it_infoobj
FOR ALL ENTRIES IN it_tmp_tbl1
WHERE iobjnm = it_tmp_tbl1-infoobj AND objvers = 'A'.
DELETE ADJACENT DUPLICATES FROM it_infoobj.
CLEAR lv_inc.
LOOP AT it_infoobj ASSIGNING <infoobj_fld_sym>.
<infoobj_fld_sym>-chain_id = pc_name.
lv_inc = lv_inc + 1.
<infoobj_fld_sym>-counter = lv_inc.
ENDLOOP.
SELECT odsobject FROM rsdodso INTO TABLE it_dso
FOR ALL ENTRIES IN it_tmp_tbl
WHERE odsobject = it_tmp_tbl-cubemulti AND objvers = 'A'.
DELETE ADJACENT DUPLICATES FROM it_dso.
CLEAR lv_inc.
LOOP AT it_dso ASSIGNING <dso_fld_sym>.
<dso_fld_sym>-chain_id = pc_name.
lv_inc = lv_inc + 1.
<dso_fld_sym>-counter = lv_inc.
ENDLOOP.
SELECT infocube FROM rsdcube INTO TABLE it_cube
FOR ALL ENTRIES IN it_tmp_tbl
WHERE infocube = it_tmp_tbl-cubemulti AND objvers = 'A'.
DELETE ADJACENT DUPLICATES FROM it_cube.
CLEAR lv_inc.
LOOP AT it_cube ASSIGNING <cube_fld_sym>.
<cube_fld_sym>-chain_id = pc_name.
lv_inc = lv_inc + 1.
<cube_fld_sym>-counter = lv_inc.
ENDLOOP.
LOOP AT it_multi_tbl ASSIGNING <fld_sym_multi>.
wa_multip-chain_id = pc_name.
wa_multip-multiprovider = <fld_sym_multi>-multiprovider.
APPEND wa_multip TO it_multip.
CLEAR wa_multip.
ENDLOOP.
SORT it_multip.
DELETE ADJACENT DUPLICATES FROM it_multip COMPARING multiprovider.
CLEAR lv_inc.
LOOP AT it_multip ASSIGNING <multip_fld_sym>.
lv_inc = lv_inc + 1.
<multip_fld_sym>-counter = lv_inc.
ENDLOOP.
SORT: it_infoobj, it_dso, it_cube, it_multip.
DESCRIBE TABLE it_infoobj LINES lv_infoobj.
DESCRIBE TABLE it_dso LINES lv_dso.
DESCRIBE TABLE it_cube LINES lv_cube.
DESCRIBE TABLE it_multip LINES lv_multip.
lv_max = lv_dso.
IF lv_cube > lv_max.
lv_max = lv_cube.
ENDIF.
IF lv_multip > lv_max.
lv_max = lv_multip.
ENDIF.
IF lv_infoobj > lv_max.
lv_max = lv_infoobj.
ENDIF.
CLEAR: lv_dso, lv_inc.
WHILE lv_dso < lv_max.
wa_result2-chain_id = pc_name.
lv_inc = lv_inc + 1.
wa_result2-counter = lv_inc.
APPEND wa_result2 TO it_final2.
lv_dso = lv_dso + 1.
CLEAR wa_result2.
ENDWHILE.
CLEAR wa_result2.
LOOP AT it_final2 ASSIGNING <fld_sym2>.
CLEAR wa_result2.
READ TABLE it_infoobj ASSIGNING <infoobj_fld_sym> WITH KEY chain_id = <fld_sym2>-chain_id counter = <fld_sym2>-counter.
IF sy-subrc IS INITIAL AND <infoobj_fld_sym> IS ASSIGNED.
<fld_sym2>-infoobj = <infoobj_fld_sym>-infoobj.
ENDIF.
ENDLOOP.
LOOP AT it_final2 ASSIGNING <fld_sym2>.
CLEAR wa_result2.
READ TABLE it_dso ASSIGNING <dso_fld_sym> WITH KEY chain_id = <fld_sym2>-chain_id counter = <fld_sym2>-counter.
IF sy-subrc IS INITIAL AND <dso_fld_sym> IS ASSIGNED.
<fld_sym2>-dso = <dso_fld_sym>-dso.
ENDIF.
ENDLOOP.
LOOP AT it_final2 ASSIGNING <fld_sym2>.
READ TABLE it_cube ASSIGNING <cube_fld_sym> WITH KEY chain_id = <fld_sym2>-chain_id counter = <fld_sym2>-counter.
IF sy-subrc IS INITIAL AND <cube_fld_sym> IS ASSIGNED.
<fld_sym2>-cube = <cube_fld_sym>-cube.
ENDIF.
ENDLOOP.
LOOP AT it_final2 ASSIGNING <fld_sym2>.
READ TABLE it_multip ASSIGNING <multip_fld_sym> WITH KEY chain_id = <fld_sym2>-chain_id counter = <fld_sym2>-counter.
IF sy-subrc IS INITIAL AND <multip_fld_sym> IS ASSIGNED.
<fld_sym2>-multiprovider = <multip_fld_sym>-multiprovider.
ENDIF.
ENDLOOP.
ENDFORM. " FETCH_DATA
*&---------------------------------------------------------------------*
*& Form BUILD_FIELDCATLOG
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM build_fieldcatlog .
wa_fldcat-fieldname = 'CHAIN_ID'
wa_fldcat-tabname = 'IT_FINAL1'.
wa_fldcat-seltext_m = 'CHAIN NAME'.
wa_fldcat-outputlen = 50.
wa_fldcat-col_pos = 0.
APPEND wa_fldcat TO fieldcatalog..
CLEAR wa_fldcat.
wa_fldcat-fieldname = 'TECHNAME'.
wa_fldcat-tabname = 'IT_FINAL1'.
wa_fldcat-seltext_m = 'QUERY TECHNICAL NAME'.
wa_fldcat-outputlen = 50.
wa_fldcat-col_pos = 1.
APPEND wa_fldcat TO fieldcatalog.
CLEAR wa_fldcat.
wa_fldcat-fieldname = 'DESCR'.
wa_fldcat-tabname = 'IT_FINAL1'.
wa_fldcat-seltext_m = 'QUERY DESCRIPTION'.
wa_fldcat-outputlen = 50.
wa_fldcat-col_pos = 2.
APPEND wa_fldcat TO fieldcatalog.
ENDFORM. " BUILD_FIELDCATLOG
*-------------------------------------------------------------------*
* Form TOP-OF-PAGE *
*-------------------------------------------------------------------*
* ALV Report Header *
*-------------------------------------------------------------------*
FORM top-of-page.
*ALV Header declarations
DATA: t_header TYPE slis_t_listheader,
wa_header TYPE slis_listheader,
t_line LIKE wa_header-info,
ld_lines TYPE i,
ld_linesc(10) TYPE c.
* Title
wa_header-typ = 'H'.
IF r_bexqr = 'X'.
wa_header-info = 'Your list of BEX Reports'.
ELSE.
CONCATENATE 'Info-provider Information for chain: ' pc_name INTO wa_header-info.
ENDIF.
APPEND wa_header TO t_header.
CLEAR wa_header.
* Date
wa_header-typ = 'S'.
wa_header-key = 'Date: '.
CONCATENATE sy-datum+6(2) '.'
sy-datum+4(2) '.'
sy-datum(4) INTO wa_header-info. "todays date
APPEND wa_header TO t_header.
CLEAR: wa_header.
CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'
EXPORTING
it_list_commentary = t_header.
ENDFORM. "top-of-page
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Form DISPLAY_ALV
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM display_alv .
gd_repid = sy-repid.
CALL FUNCTION'REUSE_ALV_GRID_DISPLAY'
EXPORTING
i_callback_program = gd_repid
i_callback_top_of_page = 'TOP-OF-PAGE' "see FORM
* i_callback_user_command = 'USER_COMMAND'
it_fieldcat = fieldcatalog[]
* i_save = 'X'
* is_variant = g_variant
TABLES
t_outtab = it_final1[]
EXCEPTIONS
program_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDFORM. " DISPLAY_ALV
*&---------------------------------------------------------------------*
*& Form VALIDATE_CHAIN
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM validate_chain .
CLEAR lv_flag.
IF pc_name IS NOT INITIAL.
SELECT chain_id
variante FROM rspcchain
INTO TABLE it_table
WHERE chain_id = pc_name AND
objvers = 'A' AND
type = 'DTP_LOAD'.
IF sy-subrc = 0.
lv_flag = 1.
SORT it_table BY chain_id.
ELSE.
MESSAGE e001(zbw_msg)." WITH 'Enter a valid Process Chain Name'(039) DISPLAY LIKE 'E'.
ENDIF.
ENDIF.
ENDFORM. " VALIDATE_CHAIN
*&---------------------------------------------------------------------*
*& Form BUILD_FIELDCATLOG_P
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM build_fieldcatlog_p .
wa_fldcat-fieldname = 'CHAIN_ID'.
wa_fldcat-tabname = 'IT_FINAL2'.
wa_fldcat-seltext_m = 'CHAIN NAME'.
wa_fldcat-outputlen = 30.
wa_fldcat-col_pos = 0.
APPEND wa_fldcat TO fieldcatalog.
CLEAR wa_fldcat.
wa_fldcat-fieldname = 'INFOOBJ'.
wa_fldcat-tabname = 'IT_FINAL2'.
wa_fldcat-seltext_m = 'INFOOBJECT'.
wa_fldcat-outputlen = 30.
wa_fldcat-col_pos = 1.
APPEND wa_fldcat TO fieldcatalog.
CLEAR wa_fldcat.
wa_fldcat-fieldname = 'DSO'.
wa_fldcat-tabname = 'IT_FINAL2'.
wa_fldcat-seltext_m = 'DSO'.
wa_fldcat-outputlen = 30.
wa_fldcat-col_pos = 1.
APPEND wa_fldcat TO fieldcatalog.
CLEAR wa_fldcat.
wa_fldcat-fieldname = 'CUBE'.
wa_fldcat-tabname = 'IT_FINAL2'.
wa_fldcat-seltext_m = 'CUBE'.
wa_fldcat-outputlen = 50.
wa_fldcat-col_pos = 1.
APPEND wa_fldcat TO fieldcatalog.
CLEAR wa_fldcat.
wa_fldcat-fieldname = 'MULTIPROVIDER'.
wa_fldcat-tabname = 'IT_FINAL2'.
wa_fldcat-seltext_m = 'MultiProvider'.
wa_fldcat-outputlen = 34.
wa_fldcat-col_pos = 1.
APPEND wa_fldcat TO fieldcatalog.
ENDFORM. " BUILD_FIELDCATLOG_P
*&---------------------------------------------------------------------*
*& Form DISPLAY_ALV_P
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM display_alv_p .
gd_repid = sy-repid.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
i_callback_program = gd_repid
i_callback_top_of_page = 'TOP-OF-PAGE' "see FORM
* i_callback_user_command = 'USER_COMMAND'
it_fieldcat = fieldcatalog[]
* i_save = 'X'
* is_variant = g_variant
TABLES
t_outtab = it_final2[]
EXCEPTIONS
program_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDFORM. " DISPLAY_ALV_P
Final Output
The final output would look like below
The below output is result when Bex Query Information radio button is selected.
And if info provider is selected, the below output is displayed.
The above program could be associated with a custom tocde to further help ease of use, many SCN documents are available which could be referred for same.
Conclusion
This program allows user to get the list of all bex queries and info provider’s corresponding to a particular process chain. It leverage’s the information provided by SAP standard tables and help in effective use of time during critical phases of a project life cycle.
References
http://wiki.scn.sap.com/wiki/display/BI/Important+Tables+in+SAP+BW+7.x
- SAP Managed Tags:
3 Comments
