To find Queries by InfoObjects

[Former Member]

Hi SDN Community

Here is a reversal of a post made by another developer

This has the reverse logic... thanks for the tip.

&----

---

*& Report ZBW_FIND_CHARACTERISTICS_QUERY

*&

&----

---

************************************************************************

• SAP Consulting BW Tools:

************************************************************************

*& Find Characteristics in BEx Queries using ABAP

*&

*& Developer: WBHADS 07/06/2011

&----

---

REPORT ZBW_FIND_CHARACTERISTICS_QUERY.

PARAMETER: CHARACT LIKE rszrange-IOBJNM.

DATA: s_characteristic LIKE rszrange-IOBJNM.

s_characteristic = CHARACT.

DATA: lv_count TYPE i.

DATA: lv_count1 TYPE i.

DATA: lv_count2 TYPE i.

• Data Declaration

DATA: D_MSG_TEXT(50).

DATA: lc_DIRECTORY(30) TYPE c.

DATA: P_FILE(128).

DATA: FILENAME(128).

DATA: lc_query(60) TYPE c.

DATA: lc_date(30) TYPE c.

DATA: lc_time(30) TYPE c.

DATA: lc_start_time(60) TYPE c.

DATA: lc_syst(30) TYPE c.

DATA: lc_exception(120) TYPE c.

• RSZELTDIR Directory of the reporting component elements

• RSZELTTXT Texts of reporting component elements

• RSZELTXREF Directory of query element references

• RSRREPDIR Directory of all reports (Query GENUNIID)

• RSZCOMPDIR Directory of reporting components

• RSZRANGE Selection specification for an element

• RSZSELECT Selection properties of an element

• RSZELTDIR Directory of the reporting component elements

• RSZCOMPIC Assignment reuseable component <-> InfoCube

• RSZELTPRIO Priorities with element collisions

• RSZELTPROP Element properties (settings)

• RSZELTATTR Attribute selection per dimension element

• RSZCALC Definition of a formula element

• RSZCEL Query Designer: Directory of Cells

• RSZGLOBV Global Variables in Reporting

data: gi_data like rszrange occurs 0 with header line.

• Determine the queries

tables:

RSZELTTXT,

rszeltdir,

rszeltxref,

rszrange.

data: begin of lt_line occurs 0,

ELTUID like RSZELTDIR-ELTUID, "char 25

MAPNAME like RSZELTDIR-MAPNAME, "char 30

end of lt_line.

data: begin of lt_line1 occurs 0,

SELTUID like rszeltxref-SELTUID, "char 25

TELTUID like rszeltxref-TELTUID, "char 25

LAYTP like rszeltxref-LAYTP, "char 30

end of lt_line1.

data: begin of lt_line2 occurs 0,

ELTUID like rszrange-ELTUID, "char 25

IOBJNM like rszrange-IOBJNM, "char 25

end of lt_line2.

data: lt_tab like standard table of lt_line initial size 0.

data: lt_tab1 like standard table of lt_line initial size 0.

data: lt_tab2 like standard table of lt_line initial size 0.

data: lt_line20(128) TYPE c. " LIKE RSZELTXREF-SELTUID.

data: lt_line30(128) TYPE c. " LIKE RSZELTXREF-SELTUID.

clear lt_line.

lv_count = 0.

lv_count2 = 0.

• Loop through all the queries

select * from RSZELTDIR

into corresponding fields of lt_line

where DEFTP = 'REP'

and OBJVERS = 'A'.

• Filter by the Z Developed Queries

IF lt_line-MAPNAME CP 'Z*'.

clear lt_line20.

• Get the Query Long Name

select single * from RSZELTTXT

into lt_line20

where eltuid = lt_line-eltuid "rszeltdir-eltuid

and objvers = 'A'

and langu = 'EN'.

• Find all the query elements within the query

select * from rszeltxref

into corresponding fields of lt_line1

where seltuid = lt_line-eltuid "rszeltdir-eltuid

and objvers = 'A'.

IF lt_line1-laytp = 'FIX' OR lt_line1-laytp = 'COL' OR lt_line1-laytp = 'ROW'.

• Determine if the Query Element is matching to the characteristic

select * from rszrange "appending table gi_data

into corresponding fields of lt_line2

where eltuid = lt_line1-teltuid "rszeltxref-teltuid

and objvers = 'A'

and enum = 1.

• Populate tables

IF lt_line2-IOBJNM = s_characteristic.

append lt_line-MAPNAME to lt_tab.

lv_count = lv_count + 1.

ENDIF.

endselect.

ENDIF.

endselect.

ENDIF.

endselect.

sort lt_tab by MAPNAME.

delete adjacent duplicates from lt_tab comparing MAPNAME.

• DIRECTORY represents path where file is stored .

lc_DIRECTORY = '/interfaces/EDW/data/CSM/'.

CONCATENATE 'EDW Queries by Characteristic -' SY-DATUM

'.txt' into P_FILE SEPARATED BY SPACE.

CONCATENATE lc_DIRECTORY P_FILE into FILENAME.

• Opening the Log File

OPEN DATASET FILENAME FOR OUTPUT IN TEXT MODE

ENCODING DEFAULT.

IF SY-SUBRC NE 0.

WRITE: 'File cannot be opened. Reason:'. ", D_MSG_TEXT.

EXIT.

ENDIF.

CONCATENATE 'System Date -' SY-DATUM 'YYYYMMDD' into lc_date

SEPARATED BY SPACE.

CONCATENATE 'EDW System -' SY-SYSID into lc_syst

SEPARATED BY SPACE.

TRANSFER lc_date TO FILENAME.

TRANSFER lc_start_time TO FILENAME.

TRANSFER lc_syst TO FILENAME.

• Queries for the parameters

• loop at gi_data into lt_line.

loop at lt_tab into lt_line.

TRANSFER lt_line TO FILENAME.

endloop. "Query Loop

• Closing the Log File

CLOSE DATASET FILENAME.

[Former Member]

We have successfully created an ABAP program to do this.

Thank you.

Simon

&----

---

*& Report ZBW_FIND_CHARACTERISTICS_QUERY

*&

&----

---

************************************************************************

• SAP Consulting BW Tools:

************************************************************************

*& Find Characteristics in BEx Queries using ABAP

*&

*& Developer: WBHADS 07/06/2011

&----

---

REPORT ZBW_FIND_CHARACTERISTICS_QUERY.

PARAMETER: CHARACT LIKE rszrange-IOBJNM.

PARAMETER: VARIABN LIKE RSZGLOBV-VNAM.

DATA: query_name TYPE RSZCOMPID.

DATA: s_varname TYPE RSZGLOBV-VNAM,

s_varnameu TYPE RSZGLOBV-VARUNIID.

DATA: s_characteristic LIKE rszrange-IOBJNM.

DATA: lv_count TYPE i.

DATA: lv_count1 TYPE i.

DATA: lv_count2 TYPE i.

• Data Declaration

DATA: D_MSG_TEXT(50).

DATA: lc_DIRECTORY(30) TYPE c.

DATA: P_FILE(128).

DATA: FILENAME(128).

DATA: lc_query(60) TYPE c.

DATA: lc_date(30) TYPE c.

DATA: lc_time(30) TYPE c.

DATA: lc_start_time(60) TYPE c.

DATA: lc_syst(30) TYPE c.

DATA: lc_exception(120) TYPE c.

• RSZELTDIR Directory of the reporting component elements

• RSZELTTXT Texts of reporting component elements

• RSZELTXREF Directory of query element references

• RSRREPDIR Directory of all reports (Query GENUNIID)

• RSZCOMPDIR Directory of reporting components

• RSZRANGE Selection specification for an element

• RSZSELECT Selection properties of an element

• RSZELTDIR Directory of the reporting component elements

• RSZCOMPIC Assignment reuseable component <-> InfoCube

• RSZELTPRIO Priorities with element collisions

• RSZELTPROP Element properties (settings)

• RSZELTATTR Attribute selection per dimension element

• RSZCALC Definition of a formula element

• RSZCEL Query Designer: Directory of Cells

• RSZGLOBV Global Variables in Reporting

data: gi_data like rszrange occurs 0 with header line.

• Determine the queries

tables:

RSZELTTXT,

rszeltdir,

rszeltxref,

rszrange.

data: begin of lt_line occurs 0,

ELTUID like RSZELTDIR-ELTUID, "char 25

MAPNAME like RSZELTDIR-MAPNAME, "char 30

TXTLG(60) TYPE c,

end of lt_line.

data: begin of lt_line1 occurs 0,

SELTUID like rszeltxref-SELTUID, "char 25

TELTUID like rszeltxref-TELTUID, "char 25

LAYTP like rszeltxref-LAYTP,

end of lt_line1.

data: begin of lt_line2 occurs 0,

ELTUID like rszrange-ELTUID, "char 25

IOBJNM like rszrange-IOBJNM,

LOW like rszrange-LOW,

LOWFLAG like rszrange-LOWFLAG,

end of lt_line2.

data: begin of lt_line3 occurs 0,

VARUNIID like RSZGLOBV-VARUNIID, "char 25

VNAM like RSZGLOBV-VNAM,

IOBJNM like RSZGLOBV-IOBJNM,

end of lt_line3.

data: lt_tab like standard table of lt_line initial size 0.

data: lt_tab1 like standard table of lt_line1 initial size 0.

data: lt_tab2 like standard table of lt_line2 initial size 0.

data: lt_tab3 like standard table of lt_line3 initial size 0.

data: lt_line20(128) TYPE c. " LIKE RSZELTXREF-SELTUID.

data: lt_line30(128) TYPE c. " LIKE RSZELTXREF-SELTUID.

type-pools:

rs, "BW global

rrms, "message server

rsz, "Qry definition global

rzx0, "Qry RFC interface

rzd1. "Qry definition database

DATA: l_target_compid TYPE rszcompdir-compid,

l_t_uid_server TYPE rzx0_t_uid_server,

l_s_uid_server TYPE rzx0_s_uid_server_x,

l_s_msg TYPE rs_s_msg,

l_s_compdir TYPE rzx0_s_compdir,

l_s_eltdir TYPE rzx0_s_eltdir,

l_s_elttxt TYPE rzx0_s_elttxt,

l_t_eltdir TYPE rzx0_t_eltdir,

l_t_eltprop TYPE rzx0_t_eltprop,

l_t_eltprio TYPE rzx0_t_eltprio,

l_t_eltattr TYPE rzx0_t_eltattr,

l_t_eltxref TYPE rzx0_t_eltxref,

l_t_compdir TYPE rzx0_t_compdir,

l_t_compic TYPE rzx0_t_compic,

l_t_select TYPE rzx0_t_select,

l_t_range TYPE rzx0_t_range,

l_t_calc TYPE rzx0_t_calc,

l_t_elttxt TYPE rzx0_t_elttxt,

l_t_cell TYPE rzx0_t_cel.

data: c_subrc LIKE sy-subrc.

data:

l_GENUNIID LIKE RSRREPDIR-GENUNIID,

l_COMPID LIKE RSZCOMPDIR-COMPID,

l_COMPUID LIKE RSZCOMPDIR-COMPUID,

I_OBJVERS LIKE RSZCOMPDIR-OBJVERS,

I_WITH_TEMPLATES LIKE RSGENERAL-BOOL,

I_BY_INFOCUBE LIKE RSZCOMPIC-INFOCUBE,

I_TEMPLATE_TYPE LIKE RSZELTDIR-DEFTP,

I_VARIABLE_TYPES LIKE RSZHELP-VARTYPES,

I_VARIABLE_INFOOBJECT LIKE RSZGLOBV-IOBJNM,

I_LANGUAGE LIKE SY-LANGU,

I_USE_BUFFER TYPE RS_BOOL.

FIELD-SYMBOLS:

<l_s_eltdir> TYPE rzx0_s_eltdir,

<l_s_compdir> TYPE rzx0_s_compdir,

<l_s_compic> TYPE rzx0_s_compic,

<l_s_eltxref> TYPE rzx0_s_eltxref.

DATA:

l_t_eltxref_seltuid LIKE rszeltxref-seltuid,

l_t_eltxref_teltuid LIKE rszeltxref-teltuid,

l_t_eltxref_laytp LIKE rszeltxref-laytp.

s_characteristic = CHARACT.

• Find instances of the Forecast/Budget Variable in queries

s_varname = VARIABN. "'ZE_BFC01'.

SELECT SINGLE VARUNIID from RSZGLOBV

INTO s_varnameu where VNAM = s_varname.

clear lt_line.

lv_count = 0.

lv_count2 = 0.

• Loop through all the queries

select * from RSZELTDIR

into corresponding fields of lt_line

where DEFTP = 'REP'

and OBJVERS = 'A'.

l_COMPUID = lt_line-ELTUID.

l_GENUNIID = lt_line-MAPNAME.

• Filter by the Z Developed Queries

IF l_GENUNIID CP 'Z*'.

clear lt_line20.

• Get the Query Long Name

select single * from RSZELTTXT

into lt_line20

where eltuid = lt_line-eltuid "rszeltdir-eltuid

and objvers = 'A'

and langu = 'EN'.

• Find all the query elements within the query using function module

• Get the tables of source query and its components

CALL FUNCTION 'RSZ_X_COMPONENT_GET'

EXPORTING

• i_genuniid =

• I_COMPID =

I_COMPUID = l_COMPUID

IMPORTING

e_subrc = c_subrc

TABLES

• c_t_eltdir = l_t_eltdir

• c_t_eltprop = l_t_eltprop

• c_t_eltprio = l_t_eltprio

• c_t_eltattr = l_t_eltattr

c_t_eltxref = l_t_eltxref.

• c_t_compdir = l_t_compdir

• c_t_compic = l_t_compic

• c_t_select = l_t_select

• c_t_range = l_t_range

• c_t_calc = l_t_calc

• c_t_elttxt = l_t_elttxt

• c_t_cell = l_t_cell.

LOOP AT l_t_eltxref ASSIGNING <l_s_eltxref>.

l_t_eltxref_seltuid = <l_s_eltxref>-seltuid.

l_t_eltxref_teltuid = <l_s_eltxref>-teltuid.

l_t_eltxref_laytp = <l_s_eltxref>-laytp.

IF l_t_eltxref_laytp = 'FIX' OR l_t_eltxref_laytp = 'COL' OR l_t_eltxref_laytp = 'ROW' OR l_t_eltxref_laytp = 'AGG'.

• Determine if the Query Element is matching to the characteristic

select * from rszrange

into corresponding fields of lt_line2

where eltuid = l_t_eltxref_teltuid "rszeltxref-teltuid

and objvers = 'A'.

• Determine the variable name or master data value

IF lt_line2-LOWFLAG = 1.

• Populate tables if match for Characterisic

IF lt_line2-IOBJNM = s_characteristic.

move lt_line-ELTUID to lt_line-ELTUID.

move lt_line-MAPNAME to lt_line-MAPNAME.

move lt_line2-LOW to lt_line-TXTLG.

append lt_line to lt_tab.

lv_count = lv_count + 1.

clear lt_line2-LOW.

ENDIF.

ELSE.

select * from RSZGLOBV

into corresponding fields of lt_line3

where VARUNIID = lt_line2-LOW

and objvers = 'A'.

• Populate tables if match for Characterisic

IF lt_line2-IOBJNM = s_characteristic.

move lt_line-ELTUID to lt_line-ELTUID.

move lt_line-MAPNAME to lt_line-MAPNAME.

move lt_line3-VNAM to lt_line-TXTLG.

append lt_line to lt_tab.

lv_count = lv_count + 1.

clear lt_line3-VNAM.

ENDIF.

endselect.

ENDIF.

endselect.

ENDIF.

clear l_t_eltxref_seltuid.

clear l_t_eltxref_teltuid.

clear l_t_eltxref_laytp.

ENDLOOP.

ENDIF.

clear lt_line-ELTUID.

clear lt_line-MAPNAME.

clear lt_line-TXTLG.

endselect.

sort lt_tab by MAPNAME.

delete adjacent duplicates from lt_tab comparing ELTUID MAPNAME TXTLG.

• DIRECTORY represents path where file is stored .

lc_DIRECTORY = '/interfaces/EDW/data/CSM/'.

CONCATENATE 'EDW Queries by Characteristic -' SY-DATUM

'.txt' into P_FILE SEPARATED BY SPACE.

CONCATENATE lc_DIRECTORY P_FILE into FILENAME.

• Opening the Log File

OPEN DATASET FILENAME FOR OUTPUT IN TEXT MODE

ENCODING DEFAULT.

IF SY-SUBRC NE 0.

WRITE: 'File cannot be opened. Reason:'. ", D_MSG_TEXT.

EXIT.

ENDIF.

CONCATENATE 'System Date -' SY-DATUM 'YYYYMMDD' into lc_date

SEPARATED BY SPACE.

CONCATENATE 'EDW System -' SY-SYSID into lc_syst

SEPARATED BY SPACE.

TRANSFER lc_date TO FILENAME.

TRANSFER lc_start_time TO FILENAME.

TRANSFER lc_syst TO FILENAME.

• Queries for the parameters

loop at lt_tab into lt_line.

TRANSFER lt_line TO FILENAME.

endloop. "Query Loop

• Closing the Log File

CLOSE DATASET FILENAME.