How to get list of workbooks in roles and queries included in workbooks.
This blog provides information about how to get list of all workbooks in roles and queries included in workbooks. In scenarios like migration of all 3.x queries to BI 7.0 queries and also migration to BusinessObjects reports, we may need information of all BW queries which are included in workbooks, the metadata repository can provide list of all queries and workbooks but it can include queries or workbooks which are created for adhoc purpose.
To get list of workbooks
In order to get the list of workbooks which are included in roles, the SAP table AGR_HIER is useful.
In below example we retrieved the list of workbooks included in BWCRM* role.
The table output field SAP_GUID will provide the workbook ID’s.
These entries can be provided to table RSRWBINDEXT to get workbook descriptions.
Once we get the list of all workbooks we can move forward to get information of the query included in it.
Below are some important tables for Workbooks.
WORKBOOKS |
|
RSRWBINDEX | List of binary large objects (Excel workbooks) |
RSRWBINDEXT | Titles of binary objects (Excel workbooks) |
RSRWBSTORE | Storage for binary large objects (Excel workbooks) |
RSRWBTEMPLATE | Assignment of Excel workbooks as personal templates |
RSRWORKBOOK | Where-used list for reports in workbooks |
Get queries included in Workbooks.
Function Module:
RRMX_WORKBOOK_QUERIES_GET is SAP standard function module which is created for retrieving the query used in workbook. Below is the sample ABAP code which will provide the list of queries included in workbook.
Program Logic:
TYPE-POOLS: rzd1.
* Query Info
TYPES: BEGIN OF wa_rrmx_s_query_info,
compuid TYPE rzd1_compuid,
compid TYPE rzd1_compid,
txtlg TYPE rzd1_s_elttxt-txtlg,
END OF wa_rrmx_s_query_info.
DATA: l_book TYPE rsrworkbook-workbookid,
lt_query_info TYPE STANDARD TABLE OF wa_rrmx_s_query_info,
wa_l_query_info LIKE LINE OF lt_query_info .
DATA: BEGIN OF wa_get_query,
compuid TYPE char25,
compid TYPE char30,
txtlg TYPE rszelttxt,
END OF wa_get_query.
*----------------------------------------------------------------------*
* SELECTION-SCREEN
*----------------------------------------------------------------------*
SELECT-OPTIONS: so_work FOR l_book NO INTERVALS.
*----------------------------------------------------------------------*
* START-OF-SELECTION.
*----------------------------------------------------------------------*
START-OF-SELECTION.
* Get the data for given selection criteria.
PERFORM f_get_data.
*&---------------------------------------------------------------------*
*& Form F_GET_DATA
*&---------------------------------------------------------------------*
* Get the data for given selection criteria.
*----------------------------------------------------------------------*
FORM f_get_data .
WRITE:/ 'Work Book ID' COLOR 1,
30 'Query ID' COLOR 1,
62 'Qyery Name' COLOR 1.
LOOP AT so_work.
CLEAR lt_query_info.
l_book = so_work-low.
CALL FUNCTION 'RRMX_WORKBOOK_QUERIES_GET'
EXPORTING
i_workbookid = l_book
i_objvers = 'A'
IMPORTING
e_t_query_info = lt_query_info
EXCEPTIONS
no_workbook_id_specified = 1
no_query_authorities = 2
OTHERS = 3.
IF sy-subrc EQ 0.
LOOP AT lt_query_info INTO wa_l_query_info.
MOVE-CORRESPONDING wa_l_query_info TO wa_get_query.
WRITE:/ l_book,
30 wa_get_query-compid,
62 wa_get_query-txtlg .
ENDLOOP.
ENDIF.
ENDLOOP.
ENDFORM. "f_get_data
After execution of above program code it will provide the selection screen for Workbook technical name. After entering workbook ID’s query information will be retrieved.
2 Comments
