Helpfull Reports for SAP BEx Queries/Workbooks

This blog may not interest CRM consultants. But… you can help a fellow BW consultant with these simple and effective ABAP Reports. These Reports which will generate lists of all BW queries/workbooks existing in a SAP BW System, including the version in which it is stored.  If you need to migrate your BW Queries and Workbooks this will help you create slack in the planning of our valuable project time.

In preparation of the upgrade of  SAP BW 7.3 Upgrade to 7.4,  you have to read a lot of documentation plus the necessary SAP Notes. I came across a small but important SAP note which we did not take into account during the project planning phase.

1932461 – SAP BW 3.5 BEx tools / BW 3.5 frontend add-ons are incompatible with BW7.4

This SAP note tells you clearly that you have to migrate your queries and workbooks, if you want to continue to uses these queries and workbooks in de SAP BW 7.4 version. This means you will need to list all BEx Queries and existing Workbooks in your SAP BW system to see which impact it will have on our project planning.

To be able speed up the migration process and not compromise the project planning we needed to have two ABAP reports to help us to keep track a the migration activities of the query and workbook population.

The following two ABAP reports where created in the SAP BW system:

ZLIST_OF BWQUERY_DETAIL: Descriding the total population of queries with version indication
ZLIST_OF_BWWORKBOOK_DETAIL: Describing the total population of workbooks also with version indication.

For knowing the relations between the BEx Queries I used the following link.

If you start the report ZLIST_OF_BWQUERY_DETAIL described below, it will display the following selection screen:

And after selection and execution, the results will look like this:

*&———————————————————————*
*& Report  ZLIST_OF BWQUERY_DETAIL
*&
*&———————————————————————*
*& This ABAP Report helps you to list de Queries used in you system
*& and shows you the version of each one.
*&———————————————————————*

REPORT  zlist_of_bwquery_detail.

TYPE-POOLS : slis.

TABLES: rsrrepdir,       “Directory of all reports
        rszcompdir,      “Overzicht meervoudig te gebruiken componenten
        rszelttxt,       “Texts of reporting component elements
        rszeltdir,       “Directory of the reporting component elements
        rsrworkbook,     “‘Where-used list’ for reports in workbooks
        rsrwbindext,     “Titles of binary objects (Excel workbooks) in InfoCatalog
        rsrwbindex,      “Lijst van grote binaire objecten (excel-werkmap)
        rszwtemplate,    “Header Table for BW HTML Templates
        rszwobjtxt,      “Texts for Templates/Items/Views
        rszwobjxref,     “Structure of the BW Objects in a Template
        rszwview.        “Header Table for BW Views

DATA: BEGIN OF i_query OCCURS 0,
       compuid   LIKE rsrrepdir-compuid,
       infocube  LIKE rsrrepdir-infocube,
       compid    LIKE rsrrepdir-compid,
       objstat   LIKE rsrrepdir-objstat,
       readmode  LIKE rsrrepdir-readmode,
       owner     LIKE rszcompdir-owner,    “Owner
       lastuser  LIKE rszcompdir-tstpnm,   “Last changed by
       version   LIKE rszcompdir-version,  “Versie
       modtime   LIKE rszcompdir-tstpdat,
      END OF i_query.

DATA: BEGIN OF i_output OCCURS 0,
       compuid   LIKE rsrrepdir-compuid,
       infocube  LIKE rsrrepdir-infocube,
       compid    LIKE rsrrepdir-compid,
       textq     LIKE rszelttxt-TXTLG,
       objstat   LIKE rsrrepdir-objstat,
       readmode  LIKE rsrrepdir-readmode,
       owner     LIKE rszcompdir-owner,    “Owner
       lastuser  LIKE rszcompdir-tstpnm,   “Last changed by
       version(3) TYPE c,
       modtime   LIKE rszcompdir-tstpdat,
      END OF i_output.

DATA:  wa_fieldcat TYPE slis_fieldcat_alv,
       it_fieldcat TYPE slis_t_fieldcat_alv.
DATA:  w_layout    TYPE slis_layout_alv.     “For layout
DATA:  date TYPE c.

CLEAR w_layout.
w_layout-colwidth_optimize = ‘X’.

SELECTION-SCREEN SKIP.
SELECT-OPTIONS:   s_compid FOR rsrrepdir-compid.
SELECT-OPTIONS:   s_cube  FOR rsrrepdir-infocube.
SELECTION-SCREEN SKIP.

PARAMETERS:       p_grid  AS CHECKBOX DEFAULT ‘X’.

SELECT a~compuid a~infocube a~compid a~objstat a~readmode b~owner b~tstpnm b~version b~tstpdat
        FROM rsrrepdir AS a INNER JOIN rszcompdir AS b ON a~compuid = b~compuid
        INTO TABLE i_query
        WHERE   a~objvers = ‘A’
        AND   a~comptype = ‘REP’
        AND   b~objvers = ‘A’
        AND a~compid IN s_compid.

*&——- Loop over Query data and check the version *

LOOP AT i_query.

  IF i_query-version > 100.
    i_output-version = ’70’.
  ELSE.
    i_output-version = ’35’.
  ENDIF.

  select single *
      from rszelttxt
      WHERE  ELTUID = i_query-compuid
      AND objvers = ‘A’
      AND LANGU = ‘NL’.

  i_output-textq =  rszelttxt-TXTLG.

  MOVE:
      i_query-compuid  TO i_output-compuid,
      i_query-infocube TO i_output-infocube,
      i_query-compid   TO i_output-compid,
      i_query-objstat  TO i_output-objstat,
      i_query-readmode TO i_output-readmode,
      i_query-owner    TO i_output-owner,
      i_query-lastuser TO i_output-lastuser,
      i_query-modtime  TO i_output-modtime.

  IF sy-subrc <> 0.
* Implement suitable error handling here
  ENDIF.

  APPEND i_output.

ENDLOOP.

SORT i_query BY compid.

DELETE i_output WHERE infocube NOT IN s_cube.

wa_fieldcat-fieldname = ‘INFOCUBE’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘INFOPROVIDER’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘COMPUID’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘QUERY GUID’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘COMPID’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘QUERY NAME’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘TEXTQ’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘QUERY TITLE’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘VERSION’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘QUERY VERSION’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘OBJSTAT’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘OBJECT STATUS’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘READMODE’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘READMODE’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘OWNER’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘OWNER’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘LASTUSER’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘LAST CHANGED BY’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘MODTIME’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘MODTIME’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

IF p_grid = ‘X’.
  CALL FUNCTION ‘REUSE_ALV_GRID_DISPLAY’
    EXPORTING
      is_layout   = w_layout
      it_fieldcat = it_fieldcat[]
    TABLES
      t_outtab    = i_output.
ELSE.
  CALL FUNCTION ‘REUSE_ALV_LIST_DISPLAY’
    EXPORTING
      is_layout   = w_layout
      it_fieldcat = it_fieldcat[]
    TABLES
      t_outtab    = i_output.
ENDIF.

IF sy-subrc <> 0.
  MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.


If you start the report ZLIST_OF_BWWORKBOOK_DETAIL described below, it will display the following selection screen:

And after selection and execution, the results will look like this:

*&———————————————————————*
*& Report  ZLIST_OF_BWWORKBOOK_DETAIL
*&
*&———————————————————————*
*& This ABAP Report helps you to list de Workbooks used in you system
*& and shows you the version of each one.
*&———————————————————————*

REPORT  ZLIST_OF_BWWORKBOOK_DETAIL.

TABLES: rsrrepdir,       “Directory of all reports
        rszcompdir,      “Overzicht meervoudig te gebruiken componenten
        rszelttxt,       “Texts of reporting component elements
        rszeltdir,       “Directory of the reporting component elements
        rsrworkbook,     “‘Where-used list’ for reports in workbooks
        rsrwbindext,     “Titles of binary objects (Excel workbooks) in InfoCatalog
        rsrwbindex,      “Lijst van grote binaire objecten (excel-werkmap)
        rszwtemplate,    “Header Table for BW HTML Templates
        rszwobjtxt,      “Texts for Templates/Items/Views
        rszwobjxref,     “Structure of the BW Objects in a Template
        rszwview.        “Header Table for BW Views

DATA: BEGIN OF i_query OCCURS 0,
       WORKBOOKID   LIKE  rsrworkbook-WORKBOOKID,
       genuniid     LIKE  rsrworkbook-genuniid,
       owner        LIKE  RSRWBINDEX-OWNER,
       version      LIKE  RSRWBINDEX-LEAST_BEX_VERSIO,
       runuser      LIKE  RSRWBINDEX-TSTPNM,
       Lastupdate   LIKE  RSRWBINDEX-TIMESTMP,
      END OF i_query.

DATA: BEGIN OF i_output OCCURS 0,
       WORKBOOKID   LIKE  rsrworkbook-WORKBOOKID,
       title        LIKE  rsrwbindext-title,
       version      LIKE  RSRWBINDEX-LEAST_BEX_VERSIO,
       infocube     LIKE  rsrrepdir-INFOCUBE,
       compid       LIKE  rsrrepdir-COMPID,
       objvers      LIKE  rsrrepdir-objvers,
       owner        LIKE  RSRWBINDEX-OWNER,
       runuser      LIKE  RSRWBINDEX-TSTPNM,
       lastupdate   LIKE  RSRWBINDEX-TIMESTMP,
      END OF i_output.

DATA:  wa_fieldcat TYPE slis_fieldcat_alv,
       it_fieldcat TYPE slis_t_fieldcat_alv.

* For layout
DATA: w_layout       TYPE SLIS_LAYOUT_ALV.
CLEAR w_layout.
w_layout-colwidth_optimize = ‘X’.

* Selection part
SELECTION-SCREEN SKIP.
SELECT-OPTIONS:   s_wbook FOR rsrworkbook-workbookid.
SELECT-OPTIONS:   s_cube  FOR rsrrepdir-infocube.
SELECTION-SCREEN SKIP.
PARAMETERS:       p_grid  AS CHECKBOX DEFAULT ‘X’.

* Main Query for Quering Query data…
SELECT a~workbookid a~genuniid b~OWNER b~LEAST_BEX_VERSIO b~TSTPNM b~TIMESTMP
        FROM rsrworkbook AS a INNER JOIN rsrwbindex AS b ON a~workbookid = b~workbookid
        INTO TABLE i_query
        WHERE a~workbookid IN s_wbook
        AND a~objvers = ‘A’
        AND b~objvers = ‘A’.

SORT i_query BY workbookid genuniid.

DELETE ADJACENT DUPLICATES FROM i_queryCOMPARING workbookid genuniid.

LOOP AT i_query.
  MOVE:
      i_query-workbookid    TO i_output-workbookid,
      i_query-owner         TO i_output-owner,
      i_query-runuser       TO i_output-runuser,
      i_query-lastupdate    TO i_output-lastupdate.

      select single *
      from rsrwbindext
      WHERE workbookid = i_query-workbookid
      AND objvers = ‘A’      AND LANGU = ‘NL’.

      select single *
      from rsrrepdir
      WHERE GENUNIID = i_query-GENUNIID
      AND objvers = ‘A’.

      IF i_query-version = ’70’.
         MOVE : i_query-version TO i_output-version.
      ELSE.
         i_output-version = ’35’.
      ENDIF.

 MOVE:      rsrwbindext-title           TO i_output-title,
      rsrrepdir-compid            TO i_output-compid,
      rsrrepdir-infocube          TO i_output-infocube,
      rsrrepdir-objvers           TO i_output-objvers.

  APPEND i_output.
ENDLOOP.

SORT i_output BY workbookid compid.

DELETE ADJACENT DUPLICATES FROM i_output
COMPARING workbookid compid.

DELETE i_output WHERE infocube NOT IN s_cube.

wa_fieldcat-fieldname = ‘WORKBOOKID’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘WORKBOOKID’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘TITLE’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘TITLE’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘VERSION’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘VERSION’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘INFOCUBE’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘INFOCUBE’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘COMPID’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘COMPID’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘OBJVERS’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘OBJVERS’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘OWNER’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘OWNER’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘RUNUSER’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘USER UPDATE’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

wa_fieldcat-fieldname = ‘LASTUPDATE’.
wa_fieldcat-ref_tabname = ‘I_OUTPUT’.
wa_fieldcat-tabname = ‘TAB_IOUT’.
wa_fieldcat-seltext_m = ‘LAST UPDATE’.
APPEND wa_fieldcat TO it_fieldcat.
CLEAR wa_fieldcat.

IF p_grid = ‘X’.
CALL FUNCTION ‘REUSE_ALV_GRID_DISPLAY’
  EXPORTING
      is_layout                      = w_layout
      it_fieldcat                    = it_fieldcat[]
    TABLES  t_outtab                 = i_output.
ELSE.
CALL FUNCTION ‘REUSE_ALV_LIST_DISPLAY’
  EXPORTING
      is_layout                      = w_layout
      it_fieldcat                    = it_fieldcat[]
    TABLES  t_outtab                 = i_output.
ENDIF.

IF sy-subrc <> 0.
  MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.