Currently Being Moderated

Most of our clients looking for business object as front end tool for reporting due to enhance feature and appearance. In such case migrating existing BEx queries into business object is a primary need. It requires setting change into BEx queries i.e. in query designer-properties-advanced, there is one check box available “Allow External Access to this query”. If we would like to access query using business object or any other non-sap tool, this option should be checked. BEx Queries acts as an info provider for BO reports eliminating need for universe.

 

flag.png

 

 

If this change is require only in few queries it can be done one-by-one. But in case we are planning to migrate, a huge number of queries into business object, then how about a small “Jinni” i.e. program to do so. It will reduce our time and effort.

 

As per our need I have develop a small program to achieve this. I am putting it here so that other can also take benefit in case of need.

 

 

ABAP Code  ----------- Start    ---------------------------------------

 

*&---------------------------------------------------------------------*

*& Report  ZFLAG

*&

*&---------------------------------------------------------------------*

 

REPORT  ZFLAG.

 

*&---------------------------------------------------------------------*

*& Report ZFLAG

*&---------------------------------------------------------------------*

*&

*& Title: BEx Queries External Access Flag update

*& Purpose:

*& This program updates the external query access flag for a single BEx query or

*& multiple queries through a wildcard * query name input. The flag can either

*& be set or removed, depending on input parameters. When the external flag for

*& a BEx query is set, the BEx query data can be read from external non-BW systems,

*& such as Business Objects.

*&

*&---------------------------------------------------------------------*

* REPORT ZFLAG.

*----------------------------------------------------------------------*

* Declarations *

*----------------------------------------------------------------------*

TYPES: BEGIN OF ty_queries,

mapname type rszeltdir-mapname, " query name

eltuid type rszeltprop-eltuid, " technical query ID

rfcsupport type rszeltprop-rfcsupport, " external flag

END OF ty_queries.

DATA: lv_queries TYPE ty_queries,

it_queries TYPE STANDARD TABLE OF ty_queries,

lv_eltuid type rszeltprop-eltuid,

lv_ext_fl type rszeltprop-rfcsupport,

lv_sim_fl type c,

lv_techname type rszeltdir-mapname.

FIELD-SYMBOLS: <l_s_queries> TYPE ty_queries.

*----------------------------------------------------------------------*

* Selection-Screen *

*----------------------------------------------------------------------*

SELECTION-SCREEN BEGIN OF BLOCK 1.

PARAMETERS: n_query TYPE RSZELTDIR-MAPNAME OBLIGATORY.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(21) TEXT-001.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN SKIP 1.

SELECTION-SCREEN END OF BLOCK 1.

SELECTION-SCREEN BEGIN OF BLOCK 2 WITH FRAME. " TITLE TEXT-002.

PARAMETERS: bex_fl AS CHECKBOX DEFAULT 'X'.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 4(44) TEXT-002 FOR FIELD bex_fl.

SELECTION-SCREEN END OF LINE.

PARAMETERS: sim_fl AS CHECKBOX DEFAULT ''.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 4(44) TEXT-003 FOR FIELD sim_fl.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF BLOCK 2.

START-OF-SELECTION.

*----------------------------------------------------------------------*

* Parameter *

*----------------------------------------------------------------------*

* Replace * with % for select statement

lv_techname = n_query.

REPLACE ALL OCCURRENCES OF '*' IN lv_techname WITH '%' IN CHARACTER MODE.

* Set BEx flag according to parameter bex_fl

lv_ext_fl = bex_fl.

lv_sim_fl = sim_fl.

IF lv_sim_fl = 'X'.

WRITE: / 'SIMULATION MODE'.

ULINE.

ENDIF.

WRITE: / 'Searching for BEx Query: ', n_query.

SKIP 1.

*----------------------------------------------------------------------*

* Find Queries based on input *

*----------------------------------------------------------------------*

* The table RSZELTDIR holds the details of all query elements.

* Query Technical Names are contained in the MAPNAME field with

* active versions given by 'A' in the OBJVERS field and DEFTP being

* 'REP' for Report.

* Retrieve the Technical Query ID, ELTUID for further operations.

SELECT a~MAPNAME b~ELTUID b~RFCSUPPORT

INTO TABLE it_queries

FROM RSZELTDIR AS a

INNER JOIN RSZELTPROP AS b

ON a~ELTUID = b~ELTUID

WHERE a~MAPNAME LIKE lv_techname " Query technical name

AND a~OBJVERS EQ 'A' " Active

AND a~DEFTP EQ 'REP' " Query and no other query element

AND b~OBJVERS EQ 'A'. " Active

* Query found

IF sy-subrc EQ 0.

*----------------------------------------------------------------------------------------------------*

* Loop on results to set external access flag or report back otherwise  *

*----------------------------------------------------------------------------------------------------*

LOOP AT it_queries ASSIGNING <l_s_queries>.

CASE <l_s_queries>-rfcsupport.

WHEN ''.

IF lv_ext_fl = 'X'.

* Set flag

IF lv_sim_fl NE 'X'. " Not a simulation

UPDATE RSZELTPROP SET RFCSUPPORT = lv_ext_fl

WHERE ELTUID EQ <l_s_queries>-ELTUID AND OBJVERS EQ 'A'.

*------------------------------------------------------------------------------------------------------------------------*

* Updating Table RSREPDIR as well since it is also having field RFCSUPPORT and

* it gets updated when we checked “Allow external access flag” at query level.

*------------------------------------------------------------------------------------------------------------------------*

UPDATE RSRREPDIR  SET RFCSUPPORT = lv_ext_fl

WHERE COMPUID  EQ<l_s_queries>-ELTUIDAND OBJVERS EQ 'A'.

ENDIF.

 

WRITE: / 'Query: ', <l_s_queries>-mapname, 'Object ID', <l_s_queries>-eltuid, ' set for external access'.

ELSE.

* External access removed

WRITE: / 'Query: ', <l_s_queries>-mapname, 'Object ID', <l_s_queries>-eltuid, ' external access already removed'.

ENDIF.

WHEN 'X'.

IF lv_ext_fl = 'X'.

* External access already active

WRITE: / 'Query: ', <l_s_queries>-mapname, 'Object ID', <l_s_queries>-eltuid, ' external access already active'.

ELSE.

IF lv_sim_fl NE 'X'. " Not a simulation

 

* Remove external access, set flag to blank  forboth tables, RSZELTPROP and RSRREPDIR

 

UPDATE RSZELTPROP SET RFCSUPPORT = lv_ext_fl

WHERE ELTUID EQ <l_s_queries>-ELTUID

AND OBJVERS EQ 'A'.

 

UPDATE RSRREPDIR  SET RFCSUPPORT = lv_ext_fl

WHERE COMPUID  EQ<l_s_queries>-ELTUIDAND OBJVERS EQ 'A'.

ENDIF.

 

WRITE: / 'Query: ', <l_s_queries>-mapname, 'Object ID', <l_s_queries>-eltuid, ' external access removed'.

ENDIF.

WHEN OTHERS.

* Not found

WRITE: / 'Query: ', <l_s_queries>-mapname, ' not found'.

ENDCASE.

ENDLOOP.

* Query not found

ELSE.

WRITE: / 'Query: ', n_query, ' not found'.

ENDIF.

* Clean-up

REFRESH it_queries.

CLEAR: lv_techname, lv_ext_fl, lv_sim_fl.

 

ABAP Code ---------- End   ---------------------------------------

 

Output:

 

Save your report and execute it.

Output screen:

Output.png

 

 

How to use:

 

1. Enter a BEx query technical name into Query Name parameter.

The wildcard * is accepted, e.g. 'Z*'; or

2. Use the match code navigation to navigate to query and double-click to select it.

 

In case you are not able to see parameter text, go to text elements->selection text and provide text description for each parameter variable.  Alternatively you can use transaction code SE38 to do so.

 

Text.png

 

Text1.png

 

 

Note: Please test it into your sand box before deploying into development/production environment.

 

References: help.sap.com

                    scn.sap.com

Comments

Actions

Filter Blog

By author:
By date:
By tag: