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:
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.
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.
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.
Conclusion:
Using an ABAP program with the above function modules described, we can copy the Bex query across the systems.
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
very valuable information.
thanks
Salam
Valuable Information.
Thanks,
Avinash.
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.
Thank you so much for sharing, Francis!
Chapeau!
very detailed