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.
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:
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.
Note: Please test it into your sand box before deploying into development/production environment.
References: help.sap.com
scn.sap.com
- SAP Managed Tags:
3 Comments
