Skip to Content

Copy BW Bex Queries across systems

Summary

This article gives clear picture about how to copy Bex query from a destination system to the source system by running a custom program.


Introduction

  Using an ABAP program we can copy the Bex query across the systems. There are scenarios like, the Bex Query is developed directly in production system and in order to have all the systems (development, test and production) in line, we might want to copy the queries across the systems without recreating in each of the systems which is time consuming. Instead, we can create a custom program to copy the queries between the systems


Scenario

Let’s take an example of a Bex query “ZTEST_QUERY1” which is available in production system ‘PRO’ to be copied to Development system ‘DEV’.


Function modules

  • RSZ_X_COMPONENT_GET
  • RSZ_X_COMPONENT_SET

Custom program Explained

Let’s create a custom program ZCOPY_QUERY with a selection screen allowing the user to provide the Query name in the destination system, destination system name and name to be copied to the source system.



Selection Screen:


S1.jpg






RSZ_X_COMPONENT_GET:


Function module RSZ_X_COMPONENT_GET is used to get the Query elements of the query in the production system PRO.



The above function module reads the query definition from the remote system.

Modify internal table IT_ELTDIR with the new query name to be copied to. 

RSZ_X_COMPONENT_SET:


Function module RSZ_X_COMPONENT_SET is used to create a query in the source system.


Once the program is executed with these two function modules described above the query will be copied to the source system as expected.


S2.jpg


Tables being used:


RSRREPDIR     Directory of all reports
RSZCOMPIC     Assignment reusable component <Infocube>
RSZCOMPDIR   Directory of reporting components
RSZELTXREF    Directory of query element references
RSZGLOBV      Global Variables in Reporting
RSZELTDIR     Directory of the reporting component elements
RSZCALC          Definition of a formula element
RSZELTPROP Element properties (settings)
RSZELTPRIO Priorities with element collisions
RSZSELECT Selection properties of an element
RSZRANGE

Selection specification for an element


Caution:

Copying procedure also copies all reusable component used in this query. Such as, Calculated Key Figures, Restricted Key Figures, Structures, Variables and Selection Objects. Someone would only identify these duplicated components only when the query is opened using Bex 7.X or higher, this could cause many inconsistencies when running reports.

Use report ANALYZE_RSZ_TABLES (notes 792779 and 853826) for detection of both situations (Table RSZCOMPDIR). The program allows renaming of duplicated queries and query components.

S3.jpg

S4.jpg

S5.jpg


Recommendation:


Recommended workaround is to use ‘Save As…’  functionality. This can be achieved by passing I_COPY_MODE = “L” in the function module RSZ_X_COMPONENT_SET_NEW.

S6.jpg

Conclusion:


Using an ABAP program with the above function modules described, we can copy the Bex query across the systems.

6 Comments
You must be Logged on to comment or reply to a post.
  • Hi Rajesh,

    Interest approach. Wouldn’t it be simpler though to just transport the query from one system to another?

    Regards,

    Suhas

    • Hi Suhas,

      transportation is an alterantive. However, mostly only a few queries origin in a development system, often it is business that develops such queries directly in the productive environment. Also, not all systems might share a common transporting environment.

      Greetings

      Markus

  • Thanks Rajesh, Very useful tip…

    Those who are looking for code… find the ABAP code below

    May be it would be handy for someone

    *&———————————————————————*
    *& Report ZCOPY_QUERY
    *&———————————————————————*
    *&
    *&———————————————————————*

    REPORT ZCOPY_QUERY.

    DATA i_genuniid            TYPE sysuuid_25.
    DATA i_compid              TYPE rszcompid.
    DATA i_compuid             TYPE sysuuid_25.
    DATA i_objvers             TYPE rsobjvers.
    DATA i_with_templates      TYPE rs_bool.
    DATA i_by_infocube         TYPE rsinfocube.
    DATA i_template_type       TYPE rszdeftp.
    DATA i_variable_types      TYPE rszvtypes.
    DATA i_variable_infoobject TYPE rsiobjnm.
    DATA i_language            TYPE sy-langu.
    DATA i_use_buffer          TYPE rs_bool.
    DATA i_qd_version          TYPE char5.
    DATA i_designtime_call     TYPE rs_bool.
    DATA e_compuid             TYPE sysuuid_25.
    DATA e_subrc               TYPE sysubrc.
    DATA e_backup_exists       TYPE rs_bool.
    DATA c_t_eltdir            TYPE STANDARD TABLE OF rsz_x_eltdir.
    DATA c_t_elttxt_ui         TYPE STANDARD TABLE OF rsz_x_elttxt_ui.
    DATA c_t_eltprop           TYPE STANDARD TABLE OF rsz_x_eltprop.
    DATA c_t_eltprio           TYPE STANDARD TABLE OF rsz_x_eltprio.
    DATA c_t_eltprio_2         TYPE STANDARD TABLE OF rsz_x_eltprio_2.
    DATA c_t_eltattr           TYPE STANDARD TABLE OF rsz_x_eltattr.
    DATA c_t_eltxref           TYPE STANDARD TABLE OF rsz_x_eltxref.
    DATA c_t_compdir           TYPE STANDARD TABLE OF rsz_x_compdir.
    DATA c_t_compic            TYPE STANDARD TABLE OF rsz_x_compic.
    DATA c_t_select            TYPE STANDARD TABLE OF rsz_x_select.
    DATA c_t_range             TYPE STANDARD TABLE OF rsz_x_range.
    DATA c_t_calc              TYPE STANDARD TABLE OF rsz_x_calc.
    DATA c_t_cell              TYPE STANDARD TABLE OF rsz_x_cel.
    DATA c_t_var               TYPE STANDARD TABLE OF rsz_x_var.
    DATA c_t_uid_server        TYPE STANDARD TABLE OF rsz_x_uid_server.
    DATA c_t_elttxt            TYPE STANDARD TABLE OF rszelttxt.
    DATA e_t_hier_info         TYPE STANDARD TABLE OF rsz_x_hier_info.
    DATA i_copy_mode    TYPE rs_bool.
    DATA i_role         TYPE agr_name.
    DATA i_node         TYPE num10.
    DATA e_genuniid     TYPE sysuuid_25.

    FIELD-SYMBOLS <fs_eltdir>  TYPE rsz_x_eltdir.
    FIELD-SYMBOLS <fs_compdir> TYPE rsz_x_compdir.

    CONSTANTS: c_rep(3) TYPE c VALUE ‘REP’.

    PARAMETERS qry_frm TYPE rszcompid.
    PARAMETERS sys_frm TYPE rfcdes-rfcdest. “will use SM59 system settings
    PARAMETERS qry_tgt TYPE sysuuid_25.

    MESSAGE ‘Reading query from source system’ TYPE ‘S’ .

    CALL FUNCTION ‘RSZ_X_COMPONENT_GET’
    DESTINATION sys_frm
    EXPORTING
    i_compid          = qry_frm
    i_objvers         = ‘A’
    i_with_templates  = ‘ ‘
    i_template_type   = ‘NIL’
    i_designtime_call = rs_c_true
    IMPORTING
    e_compuid         = e_compuid
    e_subrc           = e_subrc
    e_backup_exists   = e_backup_exists
    TABLES
    c_t_eltdir        = c_t_eltdir
    c_t_elttxt_ui     = c_t_elttxt_ui
    c_t_eltprop       = c_t_eltprop
    c_t_eltprio       = c_t_eltprio
    c_t_eltprio_2     = c_t_eltprio_2
    c_t_eltattr       = c_t_eltattr
    c_t_eltxref       = c_t_eltxref
    c_t_compdir       = c_t_compdir
    c_t_compic        = c_t_compic
    c_t_select        = c_t_select
    c_t_range         = c_t_range
    c_t_calc          = c_t_calc
    c_t_cell          = c_t_cell
    c_t_var           = c_t_var
    c_t_uid_server    = c_t_uid_server
    c_t_elttxt        = c_t_elttxt
    e_t_hier_info     = e_t_hier_info.

    IF e_subrc EQ 0.
    MESSAGE ‘Query read was successfully. Preparing to create target query’ TYPE ‘S’ .
    ELSE.
    MESSAGE ‘Unable to read query, Stopping the process’ TYPE ‘S’ .
    RETURN.
    ENDIF.

    * Update the target query name to the fields
    READ TABLE c_t_eltdir ASSIGNING <fs_eltdir>
    WITH KEY deftp    = c_rep
    subdeftp = c_rep.
    IF sy-subrc = 0.
    READ TABLE c_t_compdir ASSIGNING <fs_compdir>
    WITH KEY compuid = <fs_eltdir>-eltuid.
    IF sy-subrc = 0.
    <fs_eltdir>-mapname = <fs_compdir>-compid = qry_tgt.
    ENDIF.
    ENDIF.

    CALL FUNCTION ‘RSZ_X_COMPONENT_SET’
    EXPORTING
    i_compuid   = e_compuid
    i_objvers   = ‘A’
    i_copy_mode = ‘X’
    i_language  = sy-langu
    IMPORTING
    e_genuniid  = e_genuniid
    e_subrc     = e_subrc
    TABLES
    c_t_eltdir  = c_t_eltdir
    c_t_eltprop = c_t_eltprop
    c_t_eltprio = c_t_eltprio
    c_t_eltattr = c_t_eltattr
    c_t_eltxref = c_t_eltxref
    c_t_compdir = c_t_compdir
    c_t_compic  = c_t_compic
    c_t_select  = c_t_select
    c_t_range   = c_t_range
    c_t_calc    = c_t_calc
    c_t_cell    = c_t_cell
    c_t_var     = c_t_var
    c_t_elttxt  = c_t_elttxt.

    IF sy-subrc EQ 0.
    MESSAGE  ‘… Query created successfully.’ TYPE ‘S’ .
    ELSE.
    MESSAGE  ‘… Issue while creating target query. Process failed’TYPE ‘S’ .
    ENDIF.