Skip to Content

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.

TableAGR_hier

The table output field SAP_GUID will provide the workbook ID’s.

These entries can be provided to table RSRWBINDEXT to get workbook descriptions.

 Table Output 

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.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply